Geek asks and answers

How to update two MySQL tables using PHP?

I have 2 tables in my db, each of which has a Catergory column.

Categories Table -
id | category_name
1  : basic
2  : midlevel
3  : highlevel
4  : profesional

Product Table - 
id | product | category_name
1  : adsl 1G : basic
2  : adls 2G : midlevel
3  : adsl 3G : highlevel
4  : adsl 4G : profesional

When I update * category_name * in a table categories, I would also like to change it in the table Products, but I'm not sure how to do this.

I could not find and answer, because I do not know how to formulate this correctly.

Any help would be appreciated.

What my update.php looks like:

     db_update("UPDATE category SET 
    `category_name` = '".$_POST['category_name']."'
     WHERE category_id = '".$id."'");

what should look like: ???

{code here}

Note: "FIXED - I forgot to send my old value to save.php so that $ id never knows what to update."

+3
source share
7 answers

You can simply run two queries by changing both names:

<?php
mysql_query("UPDATE categories SET category_name='" . $catname . "' WHERE category_name='basic'");
mysql_query("UPDATE product SET category_name='" . $catname . "' WHERE category_name='basic'");
?>

, , .

:

<?php
    mysql_query("
        mySqlQuery(UPDATE categories SET category_name='" . $catname . "' WHERE category_name='basic';)
        mySqlQuery(UPDATE product SET category_name='" . $catname . "' WHERE category_name='basic';)
    ");
?>

:

<?php
mysql_query("
    UPDATE `product`,`categories`
    SET `product`.`category_name` = '" . $catname . "',
        `categories`.`category_name` = '" . $catname . "'
    WHERE `items`.`id` = '" . $catname . "'
");
?>

, , , .

:

Categories Table -
id | category_name
1  : basic
2  : midlevel
3  : highlevel
4  : profesional

Product Table - 
id | category_ID | product
1  : 1           : adsl 1G
2  : 2           : adls 2G
3  : 3           : adsl 3G
4  : 3           : adsl 4G
+4
UPDATE Categories t1 
JOIN Product t2 ON (t1.category_name  = t2.category_name ) 
SET t1.category_name = ?
    t1.category_name = ?
WHERE t1.id = ?

, . , -...

:

Product Table - 
id | product | category_ID

Categories Table -
id | category_name
+1

"id" . , , - , .

:

Categories Table - id | category_name 1 : basic 2 : midlevel 3 : highlevel 4 : profesional

Product Table - id | product | category_id l : adsl 1G : basic 2 : adls 2G : midlevel 3 : adsl 3G : highlevel 4 : adsl 4G : profesional

, , , -, , , - , "category_name" , , . , , , ( ).

: , INNER JOINS. , . "category_id" "id" . , , .

+1

- , , , :

Categories Table -
id | category_name
1  : basic
2  : midlevel
3  : highlevel
4  : profesional

Product Table - 
id | product | category_id
1  : adsl 1G : 1
2  : adls 2G : 2
3  : adsl 3G : 3
4  : adsl 4G : 4

, , , . , .

+1

,

$query = "UPDATE tblname SET colname = '$var';";
$query .="UPDATE tblname1 SET colname1 = '$var1';";
/* execute multi query */
if (mysqli_multi_query($conn, $query)) {
    do {
        /* store first result set */
        if ($result = mysqli_store_result($conn)) {
            while ($row = mysqli_fetch_row($result)) {
             printf("%s\n", $row[0]);
            }
            mysqli_free_result($result);
        }
        /* print divider */
        if (mysqli_more_results($conn)) {
            printf("");
        }
    } while (mysqli_next_result($conn));
}

http://php.net/manual/en/mysqli.multi-query.php

+1
source

I do not know why you have category_namein both tables, this seems pointless as far as I can see.

Ideally, your table structure should look something like this:

Categories Table -
id | category_name
1  : basic
2  : midlevel
3  : highlevel
4  : profesional

Product Table - 
id | product | category_id
1  : adsl 1G : 1
2  : adls 2G : 2
3  : adsl 3G : 3
4  : adsl 4G : 4

This allows you to change the category name in the category table without having to change anything in the product table.

All you have to do is join 2 tables in a SELECT query, for example:

SELECT * FROM `categories`,`products` WHERE `categories`.`id`=`products`.`category_id`
0
source

Yes, I also came across this problem. Here is the key! Try it,

$sql = "UPDATE categorise c, product p SET category_name='$cate' ";
$sql = $sql. "WHERE c.id=p.id AND c.id like "."\"".$cate."\" ";
0
source

More articles:


All Articles