Hi,

I am using PDO in MySQL to insert a ticket. I want to return the ticket number which is the primary key (auto increment) but it's always returning zero.

here is the code I am using:

$mysql_query = $mysql_connection->prepare("CALL sp_add_new_ticket(:param_ticket_name, :param_ticket_email, :param_ticket_subject, :param_ticket_message)");
// $mysql_connection->beginTransaction();
$mysql_query->bindParam(':param_ticket_name', $ticket_name, PDO::PARAM_STR);
$mysql_query->bindParam(':param_ticket_email', $ticket_email, PDO::PARAM_STR);
$mysql_query->bindParam(':param_ticket_subject', $ticket_subject, PDO::PARAM_STR);
$mysql_query->bindParam(':param_ticket_message', $ticket_message, PDO::PARAM_STR);

$mysql_query->execute();

$ticket_number = $mysql_connection->lastInsertId();

Please help...

Jassim

    According to the user notes on the [man]pdo.lastinsertid[/man] page, the value returned by that method for MySQL is only good before the transaction that performed the insertion is committed. If you're committing after every statement (i.e., you're not explicitly beginning a transaction), then you have no chance to get the ID.

    Other information is also available on that page.

    If you aren't wrapping your database interactions in transactions you'd be risking problems if lastInsertId gave the ID of the last-inserted row, because there'd be no guarantee that it was your row that was inserted last.

      Weedpacket;11033901 wrote:

      According to the user notes on the [man]pdo.lastinsertid[/man] page, the value returned by that method for MySQL is only good before the transaction that performed the insertion is committed. If you're committing after every statement (i.e., you're not explicitly beginning a transaction), then you have no chance to get the ID.

      I did but still the same problem!!

      here is my code after modification:

      try
{
    include 'connect2db.php';
    $mysql_query = $mysql_connection->prepare("CALL sp_add_new_ticket(:param_ticket_name, :param_ticket_email, :param_ticket_subject, :param_ticket_message)");
    $mysql_query->bindParam(':param_ticket_name', $ticket_name, PDO::PARAM_STR);
    $mysql_query->bindParam(':param_ticket_email', $ticket_email, PDO::PARAM_STR);
    $mysql_query->bindParam(':param_ticket_subject', $ticket_subject, PDO::PARAM_STR);
    $mysql_query->bindParam(':param_ticket_message', $ticket_message, PDO::PARAM_STR);
    try
    {
        $mysql_connection->beginTransaction(); 
        $mysql_query->execute();
        $ticket_number = $mysql_connection->lastInsertId();
        $mysql_connection->commit(); 

    // print $mysql_connection->lastInsertId(); 
}
catch(PDOExecption $e)
{ 
    $mysql_connection->rollback(); 
    print "Error!: " . $e->getMessage() . "</br>"; 
} 
}
catch ( PDOExecption $e )
{ 
    print "Error!: " . $e->getMessage() . "</br>"; 
}
        Weedpacket;11033901 wrote:

        If you're committing after every statement (i.e., you're not explicitly beginning a transaction), then you have no chance to get the ID.

        No, this only applies to explicit calls to PDO::beginTransaction / PDO::commit.

        # using autocommit
$s = $db->prepare();
$s->execute();

sleep(10);
# Go check your database using another connection. The information is allready there because of autocommit.

# will show some number (assuming successful query)
echo $db->lastInsertId();

        However, using explicit calls to beginTransaction and commit, you will have to check lastInsertId before commit.

        $s = $db->prepare();
$db->beginTransaction();
$s->execute();

# will show some number (assuming successful query)
echo 'Before commit: ' . $db->lastInsertId() . PHP_EOL;

sleep(10);
# Check your database using another connection. The above execute will not show up, since it's not yet commited.

# 
$db->commit();

# Will show 0, since it's called after explicit call to commit.
echo 'After commit: ' . $db->lastInsertId() . PHP_EOL;
        Weedpacket;11033901 wrote:

        If you aren't wrapping your database interactions in transactions you'd be risking problems if lastInsertId gave the ID of the last-inserted row, because there'd be no guarantee that it was your row that was inserted last.

        This is not how it works. lastInsertId is on a per connection basis.

        Run the following script in rapid succession in two separate browser tabs, one with ?name=one, the other with ?name=two.

        $s = $db->prepare("INSERT INTO user(name) VALUES(:name)");

$name = isset($_GET['name']) ? $_GET['name'] : 'default';
$s->bindValue('name', $name, 's');
$s->bindValue('points', 30, 'i');

$s->execute();
sleep(10);
# As soon as you request the page, go check the database using a third connction. The record has been added
# Run the second tab and check the db using a third connection. The record has been added.

# This line still will not show the same insert id for the two separate connections
# because lastInsertId is kept on a per connection basis.
echo 'Insert id: ' . $db->lastInsertId();
        jrahma;11033903 wrote: 
            $mysql_query = $mysql_connection->prepare("CALL sp_add_new_ticket(:param_ticket_name, :param_ticket_email, :param_ticket_subject, :param_ticket_message)");

        The questions is what sp_add_new_ticket does. Does it even use auto increment?

        For example, this does not use auto increment

        CREATE TABLE user(
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(30)
);

DELIMITER #
CREATE PROCEDURE sp_insert_user(IN name CHAR(30), OUT max INT UNSIGNED)
	BEGIN
	START TRANSACTION;
		SELECT MAX(id) + 1 INTO max FROM user;
		INSERT INTO user(id, name) VALUES(max, name);
	COMMIT;
	END
#
DELIMITER ;

SET @id = 0;
SELECT @id;
CALL sp_insert_user('sp_test1', @id);
SELECT @id;
SELECT LAST_INSERT_ID();
SELECT *
FROM user;

        And the output would be

        0 - from SELECT @id
1 - from SELECT @id
0 - from SELECT LAST_INSERT_ID();
1 sp_test1 - from SELECT * FROM user

        If it does use auto increment, have you also verified that your insert query is successful? And how do you verify it?

          Write a Reply...