[RESOLVED] Update 2 tables at same time??

David_P

I have two tables - Categories and Items. The Categories table has 2 fields - id and cat_name (category name).
The Items table has several fields one of which is also cat_name (category name). The category name here should match the category name in the Category table.

Basically, how it works is I populate the Categories table with category names.
When someone enters an item into the Items table they have a drop down menu that is populated with the category names from the Category table that they choose to set the item under.

The problem is that the category name in the Category field changes but not the category name in the Items table.

I tried doing this but it didn't work:

<?php
include('dbconnect.php'); // has connect code inside
if($_POST['Submit']){

$id=$_POST['id'];
$cat_name= addslashes($_POST['cat_name']);

mysql_query("update $categories set cat_name='$cat_name' where id='$id'");

mysql_query("update $items set cat_name='$cat_name' where cat_name='$cat_name'");
exit;
}
?>

How can I do this or is it even possible to edit two tables at the same time?

bradgrafelman

I think it would be best for you to alter your items table a bit... instead of having duplicate data (e.g. cat_name in both places, and whatever other columns you've duplicated), you would simply have a cat_id field which holds the ID of the row in the categories (in InnoDB tables(?) this could even be a foreign key).

That way, all you do is update the categories table, and alter your SELECT queries a bit (e.g. joining items with categories where items.cat_id matches categories.id) when pulling information from the items table.

David_P

My table is myISAM. I think I can change it though. I'm using phpmyadmin 2. something or another and php version4.3.11 and mysql 4.1.20.

Yes I could put the id of the cat_name in the Categories table into the cat_name field in the Items table. If phpmyadmin uses Foreign Key I haven't found it. It has Unique Key, but I don't think it's the same as Foreign Key. I don't know, maybe it is.

OK, I changed "cat_name" in the Items table to "cat_name_id" and "varchars" type to "int". Now, when I need to edit the category name in the Categories table I don't have to worry about the Items table since I'm using the category id.

Thanks, got it.

bradgrafelman

I'm no DB guru, but basically you'd only have the first UPDATE query you perform now.

When SELECT'ing data, you want to pull data from the other table. So, let's pretend we have been given a certain item ID and we want to display information about that item as well as what category it's in (I don't know your DB design/application, so I'm just making up a scenario here). Your query would look something like this:

SELECT item_description, item_name, cat_name
FROM items i
JOIN categories c ON i.cat_id = c.id
WHERE i.id = $id

EDIT:

David P wrote:
Should I change "cat_name" in the Items table to "cat_name_id" and "varchars" type to "int"?

I used cat_id in my example query above, but you can use whatever column name makes sense to you. As for the type, I would match whatever type of column the 'id' column in the categories table is (minus the AUTO_INCREMENT value, obviously).

EDIT2: DB guru's - I moved this thread into the DB forum in case any of you see something wrong with my suggestion... feel free to poke fun at my lack of SQL knowledge! :p

David_P

I edited my last post the same time you were writing yours. Got it, thanks