SQL LAST_INSERT_ID() returns nothing

PHPMaker General Discussion (PHPMaker)
1

Neither $sql can get the last id with custom sql insert. Any tips to solve $last_insert_id?

// NOT WORKING

// Row Inserted event
function Row_Inserted($rsold, &$rsnew)
{
    //Log("Row Inserted");

    $sql = "
            INSERT INTO customer_orders ( field1, field2 )
            VALUES
                ( '".$rsnew["some_field"]."', 'default_customer' );
            SELECT LAST_INSERT_ID() FROM customer_orders;";

    $last_insert_id = ExecuteScalar($sql);

    $this->setSuccessMessage("Record Inserted. The ID of the new record is " . $last_insert_id);
}




// NOT WORKING

// Row Inserted event
function Row_Inserted($rsold, &$rsnew)
{
    //Log("Row Inserted");

    $sql_a = "
            INSERT INTO customer_orders ( field1, field2 )
            VALUES
                ( '".$rsnew["some_field"]."', 'default_customer' )";

	$sql_b = "
			SELECT LAST_INSERT_ID() FROM customer_orders";

	ExecuteStatement($sql_a);
    $last_insert_id = ExecuteScalar($sql_b);

    $this->setSuccessMessage("Record Inserted. The ID of the new record is " . $last_insert_id);
}
2

If you are using ExecuteScalar to get that last id and you’re using MySQL database, then you better change the SQL to:SELECT ID FROM customer_orders ORDER BY ID DESC LIMIT 0,1(assume the id field is ID).

3

working, tks!Just curious, if restricted to using SELECT LAST_INSERT_ID(), what is the proper way to execute this SQL query if ExecuteScalar is not used?

4

You may Google “select last_insert_id() returns 0”.

5

You should use two queries. They will work, if your first SQL is correct. You may debug by echoing $sql_a first.

6

it is working.Execute(“INSERT…”);$id = Conn()->lastInsertId();