Getting data from MySQL

Getting and using data from MySQL table and creating PHP variables with it

[andrewheiss]

I'm creating a registration form that links to a third party payment system. After a user has registered, their information is inserted in a MySQL database and then redirected off site to pay. The payment service then redirects them back to my site with confirmation variables in the POST headers. I'd like to be able to add data to the registrant's MySQL row depending on if the payment was processed.

Currently, I have this code that works great for getting the data in the db:

<?php
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
$sql = "INSERT INTO $tbl_name(companyname, address1, address2, ... ) VALUES('$companyname', '$address1', '$address2', ... )";
$result = mysql_query($sql);
?>

In the DB I have an auto generated ID column as well. I need to somehow have PHP discover what ID is given to the newly inserted row so I can submit the ID number to the payment site in a cookie so that record/row can be reference and updated after the payment.

However, I can't get it to work!

I've tried adding this right after the INSERT command, but it gives me an error: "Unable to jump to row 0 on MySQL result index 4"

$getid = "SELECT id FROM $tbl_name WHERE `companyname` = '$name' AND `sub_tot` = '$sub_tot'";
$result1 = mysql_query($getid);
$id= mysql_result($result1,0,"id");

I've also tried getting an array, but that won't work either. No results are given.

$result1 = mysql_query($getid);
while ($row = mysql_fetch_assoc($result1)) {
$id= $row['id'];
}

What's the best way to get this to work? Is there an easy way to get the automatically generated id immediately after inserting a row?

[jatar_k]

Welcome to WebmasterWorld andrewheiss

[php.net...]

[andrewheiss]

Is that the built-in, MySQL default id, or does that let me choose which column I want to use as the id? For example, in another project I'm working on, I have a column of auto-generated invoice numbers. Can I specify the column in the function?

[jatar_k]

it will give you the auto increment column id from the last insert

if you need other data from that row you can use that id to query mysql and get the other data

[andrewheiss]

This is perfect! Thanks!