php - LAST_INSERT_ID() not returning for all tags -

so have 3 tables follows.

topics      topic_tags    tags topic_id    tag_id        tag_id topic_data  topic_id      tags 

right can insert topic_data topics, , tags being inserted so...

tag_id    tags 1         2         3         4         test 

but when i'm trying insert tag_ids topic_tags table, it's inserting last 1 this

topic_id  tag_id 0         4 

and it's not inserting topic_id when topic inserted.

this form posts data.

<form method="post" action="add_topic.php"> <table> <tr> <td align="left"><b>enter topic keywords.     <ul id="topic" name="tags[]"></ul>  </td> </tr> <tr> <td colspan="3"><textarea name="topic_data" cols="50" rows="3" id="topic_data" placeholder="what topic talking about?"></textarea></td> </tr> <tr> <td colspan="3" align="right">invisipost: <input type="hidden" name="invisipost" value="0"><input type="checkbox" name="invisipost" value="1"> <input type="submit" name="submit" value="talk" /> <input type="reset" name="submit2" value="reset" /></td> </tr> </table> </form> 

here code:

$tags = isset($_post['tags']) ? $_post['tags'] : null;  if (is_array($tags)) { foreach ($tags $t) {     // checking duplicate      $sql_d = "select * tags tags='$t'";        $res=mysql_query($sql_d);       $res = mysql_num_rows($res);     if($res<1)     {     // escape $t before inserting in db     $sql = "insert tags (tags) values('$t')";     mysql_query($sql);     }  } } else { echo 'invalid tag'; } $sql_s = "select * tags tag_id='$tags'"; $tag_id = isset($_get['tag_id']) ? $_get['tag_id'] : null;  if (is_array($tag_id)) { foreach ($tag_id $tid) {      // escape $t before inserting in db     $sql = "insert topic_tags (tag_id) values('$tid')";     mysql_query($sql);     }  }  $sql="insert topic_tags (tag_id)values(last_insert_id())"; $result=mysql_query($sql);   $topic_data= htmlentities($_post['topic_data']); $posted_by = $_session['user_id']; $posted = "date_add(now(),interval 2 hour)"; $invisipost = isset($_post['invisipost']) ? $_post['invisipost'] : 0 ;  if (($topic_data==""))  echo "<h2>opps...</h2><p>you did not fill out required fields.</p>";  else  $sql="insert topics(topic_data, posted_by, posted, invisipost)values('$topic_data', '$posted_by', $posted, $invisipost)"; $result=mysql_query($sql);  if($result){  $sql="insert topic_tags (topic_id)values(last_insert_id()) topic_tags.tag_id='". $_get['tags'] ."'"; $result=mysql_query($sql); 

there no more support mysql_* functions, officially deprecated, no longer maintained , removed in future. should update code pdo or mysqli ensure functionality of project in future.

since you're using mysql_*, should use mysql_real_escape_string prevent injection.

---

note: example covers table information provided @ top of question, have add other columns yourself.

using mysqli , prepared statements can prevent injection using prepared statements.

the bind_param takes care of type of data you're inserting example i stands integer, s string each ? have on query add bind_param it's respective type. read more bind_param here.

my test form:

<form method="post" action="add_topic.php"> <table> <tr> <td align="left"><b>enter topic keywords.<br /> <input id="topic" name="tags"> </td> </tr> <tr> <td colspan="3"><textarea name="topic_data" cols="50" rows="3" id="topic_data" placeholder="what topic talking about?"></textarea></td> </tr> <tr> <td colspan="3" align="right">invisipost: <input type="hidden" name="invisipost" value="0"><input type="checkbox" name="invisipost" value="1"> <input type="submit" name="submit" value="talk" /> <input type="reset" name="submit2" value="reset" /></td> </tr> </table> </form> 

database.php:

<?php // fill data $db_host = 'localhost'; $db_user = 'stackoverflow'; $db_pass = 'stackoverflow'; $db_name = 'stackoverflow';  $con = mysqli_connect($db_host,$db_user,$db_pass,$db_name); if($con->connect_error)     die('connect error (' . mysqli_connect_errno() . ') '. mysqli_connect_error()); 

add_topic.php:

<?php include_once "database.php"; $tags = $_post['tags']; $topic_data = $_post['topic_data']; $ids = array();  if (!isset($topic_data)) {     die("<h2>opps...</h2><p>you did not fill out topic data field.</p>"); }  if (!isset($tags)) {     die("<h2>opps...</h2><p>you did not fill out tags field.</p>"); }  foreach (explode(' ', $tags) $tag) {     if ($stmt = $con->prepare("select tag_id tags tags=?"))     {         $stmt->bind_param("s", $tag);         $stmt->execute();         $stmt->bind_result($id);         $stmt->fetch();         $stmt->close();     }      if ($id == 0)     {         if ($stmt = $con->prepare("insert tags (tags) values(?)"))         {             $stmt->bind_param("s", $tag);             $stmt->execute();             $stmt->bind_result($id);             $stmt->fetch();             $ids[] = $stmt->insert_id;             $stmt->close();         }     }     else         $ids[] = $id; }  if ($stmt = $con->prepare("insert topics(topic_data) values(?)")) {     $stmt->bind_param("s", $topic_data);     if ($stmt->execute())     {         $topic_id = $stmt->insert_id;         $stmt->close();         foreach ($ids $id)         {             if ($stmt = $con->prepare("insert topic_tags (topic_id, tag_id) values(?, ?)"))             {                 $stmt->bind_param("ii", $topic_id, $id);                 $stmt->execute();                 $stmt->close();             }         }          }     else         $stmt->close(); }  echo "<h2>topic inserted</h2><p>the topic , tags have been inserted.</p>"; $con->close(); 

as can see on code, when checking tags, retrieving ids of tags exist , storing them array $ids later reuse table topic_tags.

after insert topic retrieve topic id , insert tags id topic_tags table along topic id.

on test form using simple input tags if you're using array can change from:

if (!isset($tags)) 

to:

if (!isset($tags) || !is_array($tags)) 

and change:

foreach (explode(' ', $tags) $tag) 

to:

foreach ($tags $tag)