Help with INSERT and SELECT

Anon

Im trying to insert a new order into an orders table and then take the newly created orderid and use it in a SELECT statement. The result of the SELECT statement is always 3. I want the orderid from the new order just INSERTED. Can anyone help? These is the SQL code im using:-

//Create a new Order record
$newOrder = "INSERT INTO ORDERS SET CUSTID=$cust_id, STATUS=0 ";
$saveOrder = mysql_query($newOrder);

//Get newly created ORDERID from ORDERS table
//PROBLEM AT THIS STAGE
$getOrdid = "SELECT ORDERID FROM ORDERS WHERE CUSTID=$cust_id AND STATUS=0 ";
$confOrdid = mysql_query($getOrdid);
echo($confOrdid);

Ive echoed the $confOrdid to see what it returns, it always 3 for some reason though..!

Anon

well, tahts an invalid query, $newOrder should be

$newOrder = "INSERT INTO ORDERS (CUSTID, STATUS) VALUES ($cust_id, 0)";

Anon

http://www.php.net/manual/en/ref.mysql.php

You need to call the function mysql_getarray (for instance) in order to get the value.

If you wish to retrieve a newly inserted auto_increment value you should use the last_insert_id function instead.

Your attempted method is not reliable in a multiuser environment.

Anon

It is valid in Mysql but nowhere else. Another of their pointless additions.

Anon

OK, changed SQL statement but still having problems. It is now showing " 2 " for every orderid instead of " 3 ". For some reason it isnt selecting the orderid I want. Any other ideas?

Anon

Im just trying to SELECT the ORDERID from an ORDERS table, but everytime it just returns 2, no matter how many records are in the orders table. Im confused?

Anon

What does your code look like now?

Anon

Its on 2 separate PHP pages now as follows:-

//Create a new Order record
$newOrder = \"INSERT INTO ORDERS (CUSTID, STATUS) VALUES ($cust_id, 0) \";
$saveOrder = mysql_query($newOrder);

The above is run when the user confirms a shopping basket. This works ok. They then fill out some shipping details and then confirms these by clicking a form button. This then runs this page:-

//Get newly created ORDERID from ORDERS table
//PROBLEM AT THIS STAGE
$getOrdid = \"SELECT LAST_INSERT_ID() FROM ORDERS WHERE CUSTID=$cust_id AND STATUS=0 \";
$confOrdid = mysql_query($getOrdid);
echo($getOrdid);
echo($confOrdid);

Iv\'e tried both last_insert_id() and simply ORDERID (as stated in the ORDERS table) but when $confOrdid is echoed it always displays 2 for the orderid for every order created. Ive listed the table structure below if this helps at all?

\"CREATE TABLE ORDERS (
ORDERID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
CUSTID INT NOT NULL,
PROCESSED TIMESTAMP,
STATUS INT,
SHIPPED DATETIME
)\";

Thanks.

larry1301

You seem to be missing the mysql_fetch_row() after you do your last mysql_query(). Here is a function of mine that does somthing similiar to what you want. It adds a Record to the CUSTOMERS table and returns the CustomerID of the Customer added.

function AddCustomerToDB ( )
{
$t = $GLOBALS;
// Open the DB
$link = ConnectToDatabase ( );

$query = "insert into CUSTOMERS VALUES ( 
            NULL, 
            '{$t[ 'EMailAddress' ]}', 
            '{$t[ 'BillingFirstName' ]}', 
            ....   // more columns here left out
            '{$t[ 'ShippingZip' ]}' 
         )";
$result = mysql_query ( $query, $link );
if ( !$result )
    return false;

$query = "select CustomerID from CUSTOMERS where EMailAddress = '{$t[ 'EMailAddress' ]}'"; 
$result = mysql_query ( $query, $link );

if ( !$result )
    return false;
else
{   
    // record exists, get it 
    $QueryData = mysql_fetch_row ( $result );
    $CustomerID = $QueryData [ 0 ];
}
return $CustomerID;

}

Anon

🙂