[Resolved] PHP/MySQL coding problem

[shawnmk]

When I submit a form (form.php), which adds data to my db (insert statement is in submit.php), how do I immediately obtain data from the row that was created?

In other words, if I insert a cd review into a db, but it is assigned an ID upon insertion (i.e. not entered by user), what statement do I need to use to obtain that ID and display it on the same page in which the info is inserted (basically to link to the added review via review.php?id=$ID) - how do I obtain that $ID?

One idea I had was to obtain it by selecting the row with the highest ID, however that won't work if a row is merely updated instead of created.

Any help would be greatly appreciated. Thanks!

[LordShryku]

See mysql_insert_id

[shawnmk]

I can't get it to return anything other than 0...

Here's my code...am I missing something? I am first just trying the example code that was given to see if I am obtaining the ID. (so far I'm not) The goal then, once I can obtain it will be to insert it: addphotos.php?id=$ID

Thanks for your help.

$query = "INSERT INTO mutual (ID ,Year ,Make ,Model ,BodyStyle ,Notes ,Price ,`Description ) VALUES ('' ,'$Year' ,'$Make' ,'$Model' ,'$BodyStyle' ,'$Notes' ,'$Price' ,'$Description' )";
printf ("Last inserted record has ID %d\n", mysql_insert_ID());
$result = mysql_query($query);

if ($result)
{echo 'Car successfully added. Now <a href="addphotos.php?id=$ID">Add Photos</a>.';}
else
{echo 'Car not added. There was an error.';}

[LordShryku]

Call it after mysql_query

$query = "INSERT INTO `mutual` (`Year` ,`Make` ,`Model` ,`BodyStyle` ,`Notes` ,`Price` ,`Description ) 
          VALUES ('$Year' ,'$Make' ,'$Model' ,'$BodyStyle' ,'$Notes' ,'$Price' ,'$Description' )";

$result = mysql_query($query);
if ($result) {
   $id = mysql_insert_id();
   echo 'Car successfully added. Now <a href="addphotos.php?id=$id">Add Photos</a>.';
} 
else {
   echo 'Car not added. There was an error.';
}

[shawnmk]

I'm getting a parse error in the line:

if($result){

Here's the code I have:

$query = "INSERT INTO mutual (ID ,Year ,Make ,Model ,BodyStyle ,Notes ,Price ,`Description) VALUES ('' ,'$Year' ,'$Make' ,'$Model' ,'$BodyStyle' ,'$Notes' ,'$Price' ,'$Description')";

$result=mysql_query($query);
if($result){
$id = mysql_insert_id();

echo 'Car successfully added. Now <a href="addphotos.php?id=$ID">Add Photos</a>.';}
else
{echo 'Car not added. There was an error.';}

?>

Thanks again for your help.

[shawnmk]

ok, I fixed the parse error - it was from when I pasted in the code, it inserted "_" instead of spaces.

I'm still not, however, getting the $id. The link is still showing up as:

addphotos.php?id=$ID

[LordShryku]

Well, it's the same code you had, only removing
printf ("Last inserted record has ID %d\n", mysql_insert_ID()); and putting it below the result....

[shawnmk]

I'm assuming you're referring to the parse error? I noticed that I posted that I fixed it at almost the same time as you posted.

I'm still not obtaining the $id for some reason.

Is there a possibility it's something set up wrong in my table?
Field: ID, smallint, (6), not null, auto_increment

Thanks

[LordShryku]

Well, the alternative is to execute another SQL query to get the last id from the table....

$query = "INSERT INTO `mutual` (`Year` ,`Make` ,`Model` ,`BodyStyle` ,`Notes` ,`Price` ,`Description ) 
          VALUES ('$Year' ,'$Make' ,'$Model' ,'$BodyStyle' ,'$Notes' ,'$Price' ,'$Description' )"; 

$result = mysql_query($query); 
if ($result) { 
   $sql = mysql_query("SELECT max(ID) FROM mutual");
   $row = mysql_fetch_row($sql);
   $id = $row[0];
   echo 'Car successfully added. Now <a href="addphotos.php?id=$id">Add Photos</a>.'; 
}  

else { 
   echo 'Car not added. There was an error.'; 
}

[shawnmk]

I'm still not getting anything. Any other ideas?

My code is exactly what you had, but just in case I'm missing something, here it is:

$query = "INSERT INTO mutual (Year ,Make ,Model ,BodyStyle ,Notes ,Price ,Description )
VALUES ('$Year' ,'$Make' ,'$Model' ,'$BodyStyle' ,'$Notes' ,'$Price' ,'$Description' )";

$result = mysql_query($query);
if ($result) {
$sql = mysql_query("SELECT max(ID) FROM mutual");
$row = mysql_fetch_row($sql);
$id = $row[0];

echo 'Car successfully added. Now <a href="addphotos.php?id=$id">Add Photos</a>.';}
else
{echo 'Car not added. There was an error.';}

[crowezr]

You should go back to using the mysql_insert_id as it will be a lot more efficient. I'd say that the problem you were having before was that when you tried to get $id, you used this line:

$id = mysql_insert_id(); 

Then when you are printing your hyperlink, you use this line:

addphotos.php?id=$ID

In the second instance, you are using $ID in all-caps, when it should be all lower-case.

Another possible issue is that when you are using your echo statement, you are using a single quotation '. If you are using a single quote, you must escape out the variable, like this:

addphotos.php?id=',$ID,'

If you don't want to escape out, you must use regular quotes ". Hope this helps.

[shawnmk]

crowezr, you're a genius. I figured it must be something simple that I was missing. I had already noticed the lowercase/uppercase thing a while ago and fixed that, but I hadn't escaped out the variable.

Thank you crowezr and LordShryku.