Hello,
This is a google maps mashup. I want the user to be able to add a tag to the marker he is creating. I’m using PHP to write to 3 different MySQL tables : markers, tags, and a 3rd table, markers_tags. Of course, the 3rd table is just a 2-column table that saves the ids of the rows in the first two tables. I base this idea on Brian Haveri’s excellent tutorial http://www.kirupa.com/developer/php/relational_db_design4.htm
Just substitute my tags table for his categories table.
So I have the following code, and it works, IF I don’t care about having duplicated tags :
// Insert new row into markers table
$query = sprintf(“INSERT INTO markers " .
" (id, codepostale, nom, marche_lundi, marche_mardi, marche_mercredi, marche_jeudi, marche_vendredi, marche_samedi, marche_dimanche, lat, lng, remarques, liensource ) " .
" VALUES (NULL, ‘%s’,’%s’, ‘%s’, ‘%s’, ‘%s’, ‘%s’, ‘%s’, ‘%s’, ‘%s’, ‘%s’, ‘%s’, ‘%s’, ‘%s’);”,
mysql_real_escape_string($codepostale),
mysql_real_escape_string($nom),
mysql_real_escape_string($marche_lundi),
mysql_real_escape_string($marche_mardi),
mysql_real_escape_string($marche_mercredi),
mysql_real_escape_string($marche_jeudi),
mysql_real_escape_string($marche_vendredi),
mysql_real_escape_string($marche_samedi),
mysql_real_escape_string($marche_dimanche),
mysql_real_escape_string($lat),
mysql_real_escape_string($lng),
mysql_real_escape_string($remarques),
mysql_real_escape_string($liensource));
$resultmarkertable = mysql_query($query);
if (!$resultmarkertable) {
die('Invalid query: ’ . mysql_error());
}
$last_insert_marker_id = mysql_insert_id();
//above stores the id of the insert
// Insert new row into tags table
//I think id below is not necessary since it’s auto increment?
$query2 = sprintf(“INSERT INTO tags " .
" (tag_id, tag ) " .
" VALUES (NULL, ‘%s’);”,
mysql_real_escape_string($tag));
$resulttagtable = mysql_query($query2);
if (!$resulttagtable) {
die(‘Invalid query: ’ . mysql_error());
}
$last_insert_tag_id = mysql_insert_id();
//above stores the id of the insert
//now, below, lets insert into markers_tags table
$query3 = sprintf(“INSERT INTO markers_tags " .
" (marker_id, tag_id )” .
" VALUES (’$last_insert_marker_id’, ‘$last_insert_tag_id’);",
mysql_real_escape_string($tag));
$resultmarkers_tags = mysql_query($query3);
if (!$resultmarkers_tags) {
die('Invalid query: ’ . mysql_error());
}
?>
However, I think an IF, ELSE statement in place of my simple insert tag query. I need to run a select on the tag table to see if the tag to be inserted already exists. If it does, we get the id and save THAT to the variable “$last_insert_tag_id” and if that SELECT statement comes up with 0 values, I need to run the regular old insert statement above. Something like this
//check to see if tag already exists
$tagcheck = mysql_query(“SELECT ID FROM tags WHERE tag = ‘$tag’”)
IF, ELSE, etc.
But I can’t figure it out. Any ideas?
