[MySQL, PHP] Multiple table insert

programming
1

My DB design is as follows:

users (user_id, username, pwd, first_name, family_name, email, admin_priv)
blog (article_id, user_id, title, article, updated, created, image, caption)
blog_tags (id, article_id, tag_id)
sites (site_id, title, description, url, created, image)
sites_tags (id, site_id, tag_id)
tags (tag_id, tag)

I’m working on an insert form that adds sites and associates user inputed tags with them. When the form is submitted, the “sites” table fills out fine. The problem is with the “sites_tags” and “tags” tables. The “tags” table always only tags the first element in a user inputed comma delimited string, which is parsed using the explode(); function. The “sites_tags” table isn’t taking anything at all. What’s more troubling is, this is almost exactly the same code as my blog insert page, which works exactly how it should. Just a few field names and variable names are different.

My guesses:

  1. It’s something in the foreach loop.
  2. It might have something to do with the mysql_insert_id(); function.

Here’s the code:

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form2")) {
  $insertSQL = sprintf("INSERT INTO sites (title, description, url, image, created) VALUES (%s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['title'], "text"),
                       GetSQLValueString($_POST['description'], "text"),
                       GetSQLValueString($_POST['url'], "text"),
                       GetSQLValueString($_POST['image'], "text"),
                       $_POST['created']);

  mysql_select_db($database_zoxide_admin, $zoxide_admin);
  $Result1 = mysql_query($insertSQL, $zoxide_admin) or die(mysql_error());
  $sitestags_siteID = mysql_insert_id();

  $insertGoTo = "sites_list.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}

//inserts tags into tags table
if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form2")) {
//parse the string of tags (tag_string) into the array $tags
$tags = explode(",",$_POST['tag_string'], 20);
if (!(isset($tags[7]))) {
//assign each element in the tags array to the temp variable $tag
foreach($tags as $tag) {
$tag = trim($tag);

//run the checking query here
  $tagcheck_query = "SELECT tags.tag_id, tags.tag FROM tags WHERE tag='" . $tag . "'";
  mysql_select_db($database_zoxide_admin, $zoxide_admin);
  $tagcheck = mysql_query($tagcheck_query, $zoxide_admin) or die(mysql_error());
  $tagcheckFoundTag = mysql_num_rows($tagcheck);
//end the query, begin the check
  if($tagcheckFoundTag > 0){
      while( $row_tagcheck = mysql_fetch_array($tagcheck)) {
          $sitestags_tagID = $row_tagcheck['tag_id'];
      }
  }
  else {
  $insertSQL_tags = sprintf("INSERT INTO tags (tag) VALUES (%s)",
                       GetSQLValueString($tag, "text"));

  mysql_select_db($database_zoxide_admin, $zoxide_admin);
  $Result1 = mysql_query($insertSQL_tags, $zoxide_admin) or die(mysql_error());
  $sitestags_tagID = mysql_insert_id();
  }//end else
  
  //insert sitestags_siteID and  sitestags_tagID into sites_tags table
  if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form2")) {
  $insertSQL_ID = sprintf("INSERT INTO sites_tags (site_id, tag_id) VALUES ('$sitestags_siteID', '$sitestags_tagID')");

  mysql_select_db($database_zoxide_admin, $zoxide_admin);
  $Result1 = mysql_query($insertSQL_ID, $zoxide_admin) or die(mysql_error());
  
}//end if nested $_POST["MM_insert"] statement
}//end foreach loop
}//end if (isset($tags[7])) statement
}//end if $_POST["MM_insert"] statement