Fetching info from a freshly created MYSQL Insert statement

[joberman]

I've about give up with the whole mysql_insert_id function. Always returns a "0". I do have an auto-incrementing field in my database called "ordernumbe r", which when I query it from another page, always gives the right answer of "45" or "60" or whatever. User fills out simple form on page 1. Form info is POSTed to response Page 2. If all goes well, and user does as instructed (field validation), user gets a nice pretty page to print out which contains all the stuff they just filled out (still on Page 2 now). However, I need user to also receive the 2 fields that were automatically created on their behalf, which were the "ordernumbe r" field and the "date" (yyyy-mm-dd format) field. There must be a way. Any help would be greatly appreciated. Is there a unique ID that creates the lock prior to the INSERT that I can reference immediately after the INSERT to then go back and fetch the remaining 2 new fields? Thanks,
JMO

[pbmods]

Heya, JMO. Welcome to TSDN!

Is there only a specific query that exhibits this behavior, or can you recreate it with any query that INSERTs data into that table?

[joberman]

Here's the relevant code.

 
mysql_select_db("db199830720", $con);
 
$sql="INSERT INTO lensorder (firstName, lastName, ccname, cctype, ccnumber, cccode, ccmonth, ccyear, ccaddress1, ccaddress2, cccity, ccstate, cczip, ccphone, email, leftlens, rightlens, nopickup, shipaddress1, shipaddress2, shipcity, shipstate, shipzip, shipphone, date)
VALUES
('$_POST[firstname]', '$_POST[lastname]', '$_POST[ccname]', '$_POST[cctype]', '$_POST[ccnumber]', '$_POST[cccode]', '$_POST[ccmonth]', '$_POST[ccyear]', '$_POST[ccaddress1]', '$_POST[ccaddress2]', '$_POST[cccity]', '$_POST[ccstate]', '$_POST[cczip]', '$_POST[ccphone]', '$_POST[email]', '$_POST[leftlens]', '$_POST[rightlens]', '$_POST[nopickup]', '$_POST[shipaddress1]', '$_POST[shipaddress2]', '$_POST[shipcity]', '$_POST[shipstate]', '$_POST[shipzip]', '$_POST[shipphone]', '$today')";
 
 

Shortly after this insert into the db, there is a simple table drawn with the results above. However, the "ordernumbe r" field in the database is auto-incremented with the successful addition of every complete record. Everything works like a champ, except I just can't figure out how to display the "ordernumbe r" field on this page.

(Sorry for the long code above - didn't know that it wasn't going to wrap)

[pbmods]

Heya, JMO.

Code wraps better (and is easier to decipher in general) when you use whitespace ~_^

What is the output of this statement:

SHOW CREATE TABLE `lensorder`\G
 

[joberman]

Sorry it took so long (crap satellite connection). Also had to remember how to run direct statements remotely (firewalled). Here's the output.

CREATE TABLE `lensorder` (
 `firstname` varchar(20) NOT NULL default '',
 `lastname` varchar(25) NOT NULL default '',
 `ccname` varchar(30) NOT NULL default '',
 `cctype` varchar(20) NOT NULL default '',
 `ccnumber` varchar(25) NOT NULL default '',
 `cccode` varchar(6) NOT NULL default '',
 `ccmonth` varchar(12) NOT NULL default '',
 `ccyear` varchar(5) NOT NULL default '',
 `ccaddress1` varchar(25) NOT NULL default '',
 `ccaddress2` varchar(50) NOT NULL default '',
 `cccity` varchar(30) NOT NULL default '',
 `ccstate` char(2) NOT NULL default '',
 `cczip` varchar(10) NOT NULL default '',
 `ccphone` varchar(14) NOT NULL default '',
 `email` varchar(50) NOT NULL default '',
 `leftlens` int(1) NOT NULL default '0',
 `rightlens` int(1) NOT NULL default '0',
 `nopickup` varchar(5) NOT NULL default '',
 `shipaddress1` varchar(50) NOT NULL default '',
 `shipaddress2` varchar(50) NOT NULL default '',
 `shipcity` varchar(30) NOT NULL default '',
 `shipstate` char(2) NOT NULL default '',
 `shipzip` varchar(10) NOT NULL default '',
 `shipphone` varchar(14) NOT NULL default '',
 `leftqty` int(3) NOT NULL default '0',
 `rightqty` int(3) NOT NULL default '0',
 `ordernumber` int(8) NOT NULL auto_increment,
 `date` date NOT NULL default '0000-00-00',
 `freeshipping` tinyint(1) NOT NULL default '0',
 `completed` tinyint(1) NOT NULL default '0',
 PRIMARY KEY  (`ordernumber`)
) TYPE=MyISAM

The "/g" didn't take in the command.

[pbmods]

Heya, JMO.

The '\G' is used in the MySQL client app to output results in vertical format instead of horizontal. It makes it a lot easier to copy and paste long pieces of result data.

Next time you're insanely curious, type \? at the MySQL prompt.

At any rate, your structure and query look OK to me.

Let me just make sure that I understand your situation. You're saying that if you were to add this line right after you execute the above query, the result is '0', correct?

mysql_query( $sql, $con );
echo mysql_insert_id($con);
 

[joberman]

Yup. "0" every time. Weird. By my understandaing, that code should return the most recent "autoincremente d" value. I remember reading something about the field being "null" or "not null" (default) might having something to do with the "0" return. Any further clues would be stellar. It's not that big of a deal if I can't get it to work, but I'm some what of a perfectionist so I'm hell-bent on eventually figuring it out.

[pbmods]

Heya, JMO.

A couple of ideas. First thing to check is to make sure that your SQL query is valid.

Try adding this:

if( ! mysql_query( $sql, $con ) )
{
    echo $sql, '<br /><br />', mysql_error($con);
}
 
echo mysql_insert_id($con);
 

I believe you mentioned earlier that the INSERTs are executing successfully, but what the heck.

Perhaps try explicitly setting a NULL value for `ordernumber`.. .?

$sql = "INSERT INTO `lensorders` ( ... `ordernumber` ... ) VALUES ( ... NULL ... )"; 
 

[joberman]

Hey! I got it to work! (Ok, actually you got it to work). I was leaving out the actual "mysql_queryxxx x" statement immediately prior to the insert_id statement. However, the last time I put the query statement in (earlier today), SQL threw up and spewed errors all over the place. Must have had my con and sql references mixed up. Thanks for the tip and the verification that I wasn't completely out of whack.

[pbmods]

Heya, JMO.

Glad to hear you got it working! Good luck with your project, and if you ever need anything, post back anytime :)