[Resolved] LAST_INSERT_ID() problem

[Havin_it2]

Hi,

I'd have put this in 'DATABASES' but I don't think I'm eligible yet...

I have a table called customer with two columns:
cartID (VARCHAR(100) NOT_NULL primary key)
orderNum (INT(5) AUTO_INCREMENT)

I tried to write a script to (a) insert a row in tha table and (b) return the value of orderNum - hence, using LAST_INSERT_ID:

$query1 = "INSERT INTO customer (cartID, orderNum) VALUES ('foo', NULL)";
$query2 = "SELECT LAST_INSERT_ID()";
mysql_select_db($database_connectMyShop, $connectMyShop);
mysql_query ($query1, $connectMyShop) or die (mysql_error());
$result = mysql_query($query2, $connectMyShop) or die (mysql_error());

echo $result;

This runs with no errors, but the echo returns:

Resource id #2

I tried just executing the queries with a form-based query-sending script (equivalent to the command line) and the value returned for SELECT LAST_INSERT_ID() was '0' no matter how many times I repeated the INSERT query. I read that MyISAM tables' lack of transaction support might be the problem, but I get the same results when 'customer' is an InnoDB table.

What the H£LL is going on? Any theories appreciated.

[LordShryku]

$result is just the mysql_query function. It's displaying the REsource used to perform the query. You need to fetch the data using mysql_fetch_row, mysql_fetch_array, mysql_fetch_object, etc...

There's also a php function to do this called mysql_insert_id()

[Havin_it2]

Thanx, didn't know about Mysql_insert_id(), it fixed the problem in the basic test script, BUT...

The full implementation is now 'being difficult'.

The full script:

require_once('../Connections/connectMyShop.php');
$fooorderNum = $SESSION['orderNum'];
$fooName = $POST['Name'];
$fooprice = $POST['price'];
$fooqty = $POST['qty'];
$query_cust_exists = "INSERT INTO orderitem (orderNum, Name, price, qty) VALUES ($fooorderNum, $fooName, $fooprice, $fooqty)";

if (!isset($SESSION['orderNum']))
{
mysql_select_db($database_connectMyShop, $connectMyShop);
$query_init_cust = "INSERT INTO customer (orderNum) VALUES (NULL)";
mysql_query ($query_init_cust, $connectMyShop) or die(mysql_error());
$SESSION['orderNum'] = mysql_insert_id();
mysql_query ($query_cust_exists, $connectMyShop) or die(mysql_error());
}
else
{
mysql_select_db($database_connectMyShop, $connectMyShop) or die(mysql_error());
mysql_query ($query_cust_exists, $connectMyShop) or die(mysql_error());
}

The output is:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ' Grave Robbers, 7.99, 1)' at line 2

I used the 'foo' variables because MySQL didn't seem to like using $_SESSION etc. in the definition of $query_cust_exists, but maybe it's just the same problem differently reported. The problem seems to be with $fooorderNum, because the POST variables made it far enough to be reported in the output so I assume they're okay. Something fundamental I am missing about session variables?

[LordShryku]

You're missing the quotes around your variables in the SQL. Anything not an integer should have quotes around it, and even integers don't hurt to have them.

$query_cust_exists = "INSERT INTO orderitem 
                        (orderNum, 
                         Name, 
                         price, 
                         qty
                        ) 
                      VALUES 
                        ('".$fooorderNum."', 
                         '".$fooName."', 
                         '".$fooprice."', 
                         '".$fooqty."'
                        )"; 

[Havin_it2]

Oh man... <blush> <cringe>

Thanks for devoting your time to this no doubt obvious-to-anyone-who-isn't-a-complete-tard problem. Will spend more time with my textbook before I bring something like this in here again. Promise!

Err... can I delete this thread?

Thx again m8.

[LordShryku]

Don't be shy to bring anything here. We've seen em all 😃