CODEWITHSUNDEEP
phpmysqldatabase
Drmzindec
Drmzindec

Reputation: 814

How to update two MySQL tables with PHP?

I Have 2 tables in my db that each have a column named Catergory.

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 the *category_name* in the categories table i would like to change it in the Products table as well, but i am not sure on how to do this.

I couldnt find and answer because i dont know how to phrase it correctly.

Any help would be appreciated.

What my update.php looks like now: 

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

what is should look like:???

{code here}

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

Upvotes: 4

Views: 10736

Answers (8)

flochristos
flochristos

Reputation: 75

Simple use the code below, hope this works for you.

          $sql = "UPDATE productSET category_name='$catname' WHERE id='$coode';";
          $sql .= "UPDATE categoriesSET category_name='".$catname."' WHERE category_name='$category_name';";
           if (mysqli_multi_query($db, $sql)) {
              do {
                  /* store first result set */
                  if ($result = mysqli_store_result($db)) {
                      while ($row = mysqli_fetch_row($result)) {
                        echo "Success";
                      }
                      mysqli_free_result($result);
                  }
                  /* print divider */
                  if (mysqli_more_results($db)) {
                      printf("");
                  }
              } while (mysqli_next_result($db));
          }

Upvotes: -1

Jyrwyn
Jyrwyn

Reputation: 72

Try this hope it helps

$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

Upvotes: 0

ORK EANG
ORK EANG

Reputation: 1

Yes, I used to encounter with this problem as well. Here the clue! Please try this,

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

Upvotes: -1

Prabhuram
Prabhuram

Reputation: 1268

When ever you have a relation like products and categories which you mentioned it is always better to have the structure as follows :

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

So whenever you need to show the category name for a product, you can do it by using joins. So this change will enable you to solve your issue.

Upvotes: 1

Daniël Voogsgerd
Daniël Voogsgerd

Reputation: 600

You could just run two queries 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'");
?>

If you want to do it in one string you can do this with multiple queries, a joined query or a stored procedure.

Multiple queries in one string:

<?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';)
    ");
?>

Joined query:

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

If none of the above work I suggest changing your tables so there isn't a matching name but a matching ID.

You'll get the following:

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

Upvotes: 4

Nick
Nick

Reputation: 6346

I don't know why you've got category_name in both tables, it seems pointless as far as I can see.

Ideally your table structure should be 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 name of the category in the categories table without needing to change anything in the products table.

All you then need to do is join the 2 tables in your SELECT query, e.g.:

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

Upvotes: 0

Frank
Frank

Reputation: 664

I suggest that you link your products table to your categories table by use of the category table's 'id' field. This way you will be linked to the same category and can change the category name without fear of breaking anything else, as you will still be linking to the same category id.

Consider the following:

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

The way you are currently doing it will seemingly be very hard to maintain, as you may need to add another column in future that uses a category in some way, and having to keep changing the 'category_name' column for all of those tables will probably end up very messy. By linking to the id, you can make any change you like in the category table without worrying that you will lose the link to any other tables(which is what will happen with the current tables).

Edit: You can get any information about categories by use of joins such as INNER JOINS. This will be a better approach, I think. Also, consider adding a relationship between the 'category_id' column in the products table with the 'id' column in the category table. This will ensure that a category will not just get deleted while there are still products linked to it.

Upvotes: 1

Konerak
Konerak

Reputation: 39773

UPDATE Categories t1 
JOIN Product t2 ON (t1.category_name  = t2.category_name ) 
SET t1.category_name = ?
    t1.category_name = ?
WHERE t1.id = ?

But you are feeling yourself that your data model is ugly. It might be better to de-normalize...

Have the product table refer to the category table:

Product Table - 
id | product | category_ID

Categories Table -
id | category_name

Upvotes: 1

Related Questions