Providing link to a dB entry following a post

jonnyboy

I have set up a form which posts to a MySQL dB.

If a row has posted, the script returns a message to confirm.

In that message I would like to provide a link to the record they have just posted.

How would I go about this. The key piece of data for me to get the record back out is the 'id' but if is auto generated by MySQL, how do I get it to use in another query?

Any help appreciated

Thanks

Jon

weekender

there is a function [man]mysql_insert_id/man which returns the id of the last row inserted (if there is an auto incremented field in the db).

so when you do your insert after the form...

$sql = "INSERT INTO table VALUES(id, name, email)";
$result = mysql_query($sql);
if(mysql_num_rows($result)<=0){
   echo "INSERT Failed";
} else {
   $lastid = mysql_insert_id();
   $sql = "SELECT * FROM table WHERE ID = $lastid";
   $result = mysql_query($sql);
   //blah blah blah
}

hope that helps

adam

jonnyboy

Thanks for your help. This is as far as I got.

<PHP>
if (!mysql_select_db("helpdesk", $connection))
showerror();

$insertQuery = ("INSERT INTO call_logger VALUES (NULL, '$dept', '$log_creator', '$log_title', '$log_detail', '$log_sev', '$log_detail_ref', '$log_status', '$log_ip', NOW(),NULL,NULL,NULL)");

if ((@ ($insertQuery,
$connection))
&& @ mysql_affected_rows() == 1)

echo "The log number is ", mysql_insert_id() .
'<a href="test_page.php?log_id=lastid">Test</a>';

 else

showerror();
}//if else empty()
?>

</PHP>

So I can get the value, but I don't know how to create the value seperately and include it in the link.

Thanks

Jon

leatherback

$lastid = mysql_insert_id() 

echo "The log number is $lastid <a href=\"test_page.php?log_id=$lastid\">Test</a>";

weekender

yup, that's good. and when the user clicks on the link and gets to test_page.php?log_id=lastid

$lastid = $_GET['log_id'];

$sql = "SELECT * FROM table WHERE id = '$lastid'";
$result = mysql_query($sql);
$row = mysql_fetch_assoc($result);
echo "ID: " . $row['id'] . "<BR>";
echo "NAME: " . $row['name'] . "<BR>";
echo "EMAIL: " . $row['email'] . "<BR>";

you can also do this in the previous page, and then you won't need the $_GET line

adam

jonnyboy

Sorry to be a little slow, but I have inserted code and I get a parse error. I am sure I am missing something simple somewhere!?

<?php
 }
  else
 {
if (!($connection = @ mysql_connect($hostname,
                                    $username,
								    $password)))
die("Could not connect to database");

if (!mysql_select_db("helpdesk", $connection))
showerror();

$insertQuery = ("INSERT INTO call_logger VALUES (NULL, '$dept', '$log_creator', '$log_title', '$log_detail', '$log_sev', '$log_detail_ref', '$log_status', '$log_ip', NOW(),NULL,NULL,NULL)");

if ((@mysql_query ($insertQuery,
                   $connection))
		&& @ mysql_affected_rows() == 1)

$lastid = mysql_insert_id()  

echo "The log number is $lastid <a href=\"test_page.php?log_id=$lastid\">Test</a>";

 else
showerror();
}//if else empty()
?>		   

</body>
</html>

Thanks

Jon

leatherback

Hi,

You miss th {} after the if statement.

(I am assuming you have more code above what you have posted..?)

J,.

ps: Next time it would help if you also post the error line..