[SOLVED] Noob - Problem with insert

[colinsp]

I am trying to retrieve a record from a table with a select query and if that record doesn't exist then add it to the table. After it has been added I then want to run the select query again to get the new name_id and add it to an existing variable.

 

This is my existing code

 

$sql = "SELECT name_id FROM names WHERE surname='$surname'";
	$result = mysql_query($sql);
	$row = mysql_fetch_array($result);
	$name_id = $row[0];
	//$temp = mysql_num_rows($result);
	//echo "rows:- $temp<br>";
	if ( mysql_num_rows($result) == 0) 
	{
		$newname = "Null,".$surname;
		$sql2 = "INSERT INTO names  VALUES '$newname'"; //add record to names 
		$result = mysql_query($sql2); 
		//Requery database
		$sql = "SELECT name_id FROM names WHERE surname='$newname'";
		$result = mysql_query($sql);
		$row = mysql_fetch_array($result);
		$name_id = $row[0];
		}
	$newcontent[$i] = $parts[0].",".$name_id.",".$parts[1].",".$parts[2].",".$parts[3].",".$parts[4];
	echo "New content:- $newcontent[$i]<br>";

	}

 

The select query is working fine, the number of rows returned is 0 if the record doesn't exist. It seems to be the if statement with the update that does not work. Do I have to requery the database or can I get the new name_id after the insert? Or is there a better way to do this?

 

I expect that I am going about this incorrectly. I have been trying to get it working for a couple of days and have given up and am asking here.

 

TIA for any thoughts or advice.

 

--

Colin

[phant0m]

This should work:

<?php
      $sql = "SELECT name_id FROM names WHERE surname='$surname'";
      $result = mysql_query($sql);
      $row = mysql_fetch_array($result);
      $name_id = $row[0];
      //$temp = mysql_num_rows($result);
      //echo "rows:- $temp<br>";
      if ( mysql_num_rows($result) == 0)
      {
         $sql = "INSERT INTO names (surname) VALUES('$surname')"; //add record to names
         mysql_query($sql);  
         $name_id = mysql_insert_id();
      }
      $newcontent[$i] = $parts[0].",".$name_id.",".$parts[1].",".$parts[2].",".$parts[3].",".$parts[4];
      echo "New content:- $newcontent[$i]<br>";

      }//what's this doing here? I assume that belong to some other unposted code

?>

 

mysql_insert_id() fetches the last automatically inserted id from the database for you.

This only works if an attribute in the database is set to auto_increment!

[colinsp]

Brilliant. 

 

Thank you very much it works perfectly.

 

Yes the trailing } is from some code that I didn't post.

 

--

Colin

[phant0m]

you're welcome

 

but make sure to read something about mysql injections - because your code looks like it's prone to that

[colinsp]

Thanks, I do know about injections, this is just development code to get the functionality working with data supplied by me. Once I have all the wrinkles out of it I will recode the appropriate parts.