php to relate tables

tagging, metadata, PHP

[crackpipe]

I'm learning tagging (for metadata) slowly and currently have a simple version of the "Toxi" schema of three tables: 1) "Items" - a files name, description and autoincrementing id, 2) "TagID" - a table of tags and an id for each tag, and 3) "Item2Tag", a table which joins each. They were all indexed to each other. Using a simple form, I insert rows into "Items" with a name and a description. I use the POST data from the form like this:

$descrip=$_POST['item_desc'];
$filename=$_POST['item_name'];

mysql_query("INSERT INTO Items (item_name, item_desc) VALUES ('$filename','$descrip')") or die(mysql_error());

Ok, so once I add a row to 'Items', MySQL auto-increments an ID for that row with an "itemID".

At the same time the user entered an item name and description in the form, they also entered the tags they wish to associate with that item. Each of these one word tags has a tagID from the TagID table. No problems to this point.

The problem is implementing a relationship between the tagID and the itemID in the Item2Tag table. I apparently need a second PHP action that takes the autoincremented 'itemID' value and the 'tagID' from the TagID table and puts them in the Item2Tag table. This apparently means I have to first let MySQL autoincrement the ItemID insertion, then retrieve that itemID, then insert it, in combination with selected tagID's, into the Item2Tag table. This seems over my current knowledge level. I saw a partial solution [webmasterworld.com ], but my problem appears to be different enough to post here. Has anyone seen this done efficiently? I hope this all made sense. Thanks.

[whoisgregg]

You can use the mysql_insert_id() [php.net] function to get the autoincremented ID of the item record you just inserted into the table.

Store that variable, then do your inserts:

mysql_query("INSERT INTO Items (item_name, item_desc) VALUES ('$filename','$descrip')") or die(mysql_error()); 
$item_id = mysql_insert_id();
$item2tag_sql = array();
foreach($userChosenTags as $tag_id){ // $userChosenTags should be an array of the tag ids the user chose
$tag_id = intval($tag_id); // sanitize user input
$item2tag_sql[] = "('$tag_id', '$item_id')";
}
$insert_tags_sql = "INSERT INTO Item2Tag (tag_id, item_id) VALUES ".implode(',', $item2tag_sql).";";
echo $insert_tags_sql;
// mysql_query($insert_tags_sql); // uncomment after you've tested the output above
}

Double check that code though, I just wrote it and probably made all sorts of errors. But it should give you the general idea. :)

[leadegroot]

and - please, please remember that our live code must have $filename and $descrip MUST be wrapped in mysql_real_escape_string !
Otherwise you are open to the worst of injection possibilities :(

[crackpipe]

You guys are officially amazing. Thanks.

I will post back with a final working version eventually, and I appreciate the reminder about mysql_real_escape_string wrapping.