Look up last auto-incremented number

chriscitrix

I need a way to look up the last auto-incremented number (or better yet the auto-increment number that comes next if possible), and then to create a "blank" insert into the database automatically to secure that number. Any ideas? Would mysql_insert_id() work, and if so, how to implement?

Thanks in advance!

laserlight

mysql_insert_id() might work here, but why do you want to do this in the first place?

chriscitrix

Basically we're creating a form that allows an agent in our call center to input information that is sent to a group of managers. In order for that agent to properly note his/her accounts with the request number, we need that provided to them at the on-set of the form opening (and thus creating the blank row of information saving this number for them, and then doing an update with the information they populate).

laserlight wrote:
mysql_insert_id() might work here, but why do you want to do this in the first place?

laserlight

Ah yes, it does look like [man]mysql_insert_id/man is what you want. Basically, you just call the function right after doing the insert, and from there you can display it to the user.

chriscitrix

Thanks for your input :-) Greatly appreciated!

chriscitrix

Here's the code I have to do what I previously explained ... however none of the data is actually posted into the database. I'm taking a wild stab and guessing that I'm calling the variable display_id wrong in the SQL query ... anyone got any idea?

<? 
$display_id = $_REQUEST['display_id']; 
$date = date('Ymd'); 
$agent = $_REQUEST['agent']; 
$aemail = $_REQUEST['aemail']; 
$prod = $_REQUEST['prod']; 
$acctemail = $_REQUEST['acctemail']; 
$custname = $_REQUEST['custname']; 
$custemail = $_REQUEST['custemail']; 
$custphone = $_REQUEST['custphone']; 
$custreq = $_REQUEST['custreq']; 
$resexp = $_REQUEST['resexp']; 
$authcc = $_REQUEST['authcc']; 
$mgr = $_REQUEST['mgr']; 
$completed = $_REQUEST['completed']; 
$comdate = $_REQUEST['comdate']; 
$resolution = $_REQUEST['resolution']; 

$dbserver = "localhost"; 
$dbuser = "cccustom"; 
$dbpass = "xxxxx"; 
$db = "cccustom"; 
$table = "legacy"; 
$link = mysql_connect($dbserver,$dbuser,$dbpass); 
if (!$link) { 
die("Couldn't Connect"); 
} 
mysql_select_db($db) or die("Unable to Open " . mysql_error()); 
$sql = 'UPDATE `legacy` SET `date` = \'$date\', `agent` = \'$agent\', `aemail` = \'$aemail\', `prod` = \'$prod\', `acctemail` = \'$acctemail\', `custname` = \'$custname\', `custemail` = \'$custemail\', `custphone` = \'$custphone\', `custreq` = \'$custreq\', `resexp` = \'$resexp\', `authcc` = \'$authcc\', `mgr` = \'$mgr\', `completed` = \'$completed\', `comdate` = \'$comdate\', `resolution` = \'$resolution\' WHERE `legacy`.`id` = \'$display_id\' LIMIT 1;';
mysql_query($sql,$link) or die("Unable to Open " . mysql_error());
?>

ClarkF1

Your query is in single quotes so it won't pass the variables.

Change

$sql = 'UPDATE `legacy` SET `date` = \'$date\', `agent` = \'$agent\', `aemail` = \'$aemail\', `prod` = \'$prod\', `acctemail` = \'$acctemail\', `custname` = \'$custname\', `custemail` = \'$custemail\', `custphone` = \'$custphone\', `custreq` = \'$custreq\', `resexp` = \'$resexp\', `authcc` = \'$authcc\', `mgr` = \'$mgr\', `completed` = \'$completed\', `comdate` = \'$comdate\', `resolution` = \'$resolution\' WHERE `legacy`.`id` = \'$display_id\' LIMIT 1;';

$sql = "UPDATE `legacy` SET `date` = '$date', `agent` = '$agent', `aemail` = '$aemail', `prod` = '$prod', `acctemail` = '$acctemail', `custname` = '$custname', `custemail` = '$custemail', `custphone` = '$custphone', `custreq` = '$custreq', `resexp` = '$resexp', `authcc` = '$authcc', `mgr` = '$mgr', `completed` = '$completed', `comdate` = '$comdate', `resolution` = '$resolution' WHERE `legacy`.`id` = '$display_id' LIMIT 1";

It get's rid of all the nasty looking backslashes too.

You should also pass all the $_REQUEST variables through mysql_real_escape_string() to prevent SQL injection

e.g.

$display_id = mysql_real_escape_string($_REQUEST['display_id']);

chriscitrix

Thanks, ClarkF1!!! Just what I needed! You guys (and gals) are awesome, I'm glad I stumbled on this site.

chriscitrix

I received the following error for each variable :

Warning: mysql_real_escape_string() [ function.mysql-real-escape-string]: Access denied for user: 'ODBC@localhost' (Using password: NO) in C:\Websites\custcare\www\mailsql.php on line 2

ClarkF1

You need to move the database connection stuff to the top of the script before you get the data from $_REQUEST. I didn't realise that it needed an open database connection to work.