php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #32195 mysqli_insert_id with mysqli_multi_query returns wrong value
Submitted: 2005-03-05 00:26 UTC Modified: 2005-03-07 16:37 UTC
From: toma at smartsemantics dot com Assigned:
Status: Not a bug Package: MySQLi related
PHP Version: 5.0.3 OS: Linux RedHat Enterprise 3
Private report: No CVE-ID: None
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: toma at smartsemantics dot com
New email:
PHP Version: OS:

 

 [2005-03-05 00:26 UTC] toma at smartsemantics dot com
Description:
------------
I am executing a loop of 4 statements: 2 INSERTS returning insert IDs and 2 SELECTS.  The last 2 SELECTS both show insert IDs equal to the value of the last successful INSERT.  I believe the appropriate behavior should be that mysqli_insert_id return a value of zero or null for the SELECTs.

Reproduce code:
---------------
<?php
$server='localhost';
$user='parkdomains';
$pass='$p@rkpl@ce$';
$database='ParkDomains';

###CONNECT TO DATABASE
$dbconn=mysqli_init();
mysqli_options($dbconn,MYSQLI_OPT_CONNECT_TIMEOUT,8);
//mysqli_real_connect($dbconn,$server,$user,$pass,$database,null,null,MYSQLI_CLIENT_FOUND_ROWS);
mysqli_real_connect($dbconn,$server,$user,$pass,$database,null,null,MYSQLI_CLIENT_FOUND_ROWS);
if(mysqli_connect_errno()){
	printf("Can't connect to the MySQL Server (".$server."): %s\n", mysqli_connect_error()); 
}
@mysqli_select_db($dbconn,$database);
if(mysqli_error($dbconn)) $ReportError(sprintf("Can't select the MYSQL database (".$database."): %s\n", mysqli_error($dbconn)),1);


###OUR SQL STATEMENTS###
$sql_statements_statements=array();
$sql_statements[]="INSERT INTO test () VALUES('','test')";
$sql_statements[]="INSERT INTO test () VALUES('','test')";
$sql_statements[]="SELECT * FROM test LIMIT 3";
$sql_statements[]="SELECT * FROM test LIMIT 2";

###COMBINE THEM INTO ONE###
$query=implode(';',$sql_statements);
echo $query."<BR>\n";

$ret=array();
reset($sql_statements);
if(mysqli_multi_query($dbconn,$query)){
	do{
		$obj=new stdClass();
		list(,$obj->sql)=each($sql_statements);
		$res=mysqli_store_result($dbconn);
		$obj->affected_rows=mysqli_affected_rows($dbconn);
		$obj->last_insert_id=mysqli_insert_id($dbconn);
		if($res){
			if(mysqli_num_rows($res)>0){
				while ($row=mysqli_fetch_row($res)){
					$obj->result[]=$row;
				};
				mysqli_free_result($res);
			}
		}
		$ret[]=$obj;
	}while(mysqli_next_result($dbconn));
}
echo "<pre>";
print_r($ret);
?>

Expected result:
----------------
INSERT INTO test () VALUES('','test');INSERT INTO test () VALUES('','test');SELECT * FROM test LIMIT 3;SELECT * FROM test LIMIT 2

Array
(
    [0] => stdClass Object
        (
            [sql] => INSERT INTO test () VALUES('','test')
            [affected_rows] => 1
            [last_insert_id] => 46
        )

    [1] => stdClass Object
        (
            [sql] => INSERT INTO test () VALUES('','test')
            [affected_rows] => 1
            [last_insert_id] => 47
        )

    [2] => stdClass Object
        (
            [sql] => SELECT * FROM test LIMIT 3
            [affected_rows] => 3
            [last_insert_id] => 47
            [result] => Array
                (
                    [0] => Array
                        (
                            [0] => 30
                            [1] => 0
                        )

                    [1] => Array
                        (
                            [0] => 31
                            [1] => 0
                        )

                    [2] => Array
                        (
                            [0] => 32
                            [1] => 0
                        )

                )

        )

    [3] => stdClass Object
        (
            [sql] => SELECT * FROM test LIMIT 2
            [affected_rows] => 2
            [last_insert_id] => 47
            [result] => Array
                (
                    [0] => Array
                        (
                            [0] => 30
                            [1] => 0
                        )

                    [1] => Array
                        (
                            [0] => 31
                            [1] => 0
                        )

                )

        )

)



Actual result:
--------------
INSERT INTO test () VALUES('','test');INSERT INTO test () VALUES('','test');SELECT * FROM test LIMIT 3;SELECT * FROM test LIMIT 2

Array
(
    [0] => stdClass Object
        (
            [sql] => INSERT INTO test () VALUES('','test')
            [affected_rows] => 1
            [last_insert_id] => 46
        )

    [1] => stdClass Object
        (
            [sql] => INSERT INTO test () VALUES('','test')
            [affected_rows] => 1
            [last_insert_id] => 47
        )

    [2] => stdClass Object
        (
            [sql] => SELECT * FROM test LIMIT 3
            [affected_rows] => 3
            [last_insert_id] => 0
            [result] => Array
                (
                    [0] => Array
                        (
                            [0] => 30
                            [1] => 0
                        )

                    [1] => Array
                        (
                            [0] => 31
                            [1] => 0
                        )

                    [2] => Array
                        (
                            [0] => 32
                            [1] => 0
                        )

                )

        )

    [3] => stdClass Object
        (
            [sql] => SELECT * FROM test LIMIT 2
            [affected_rows] => 2
            [last_insert_id] => 0
            [result] => Array
                (
                    [0] => Array
                        (
                            [0] => 30
                            [1] => 0
                        )

                    [1] => Array
                        (
                            [0] => 31
                            [1] => 0
                        )

                )

        )

)



Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2005-03-05 00:34 UTC] toma at smartsemantics dot com
I reversed my expected and actual results... what I am looking to get is insert ids with a value of zero.  Please reverse them.
 [2005-03-06 16:14 UTC] sniper@php.net
Please try using this CVS snapshot:

  http://snaps.php.net/php5-latest.tar.gz
 
For Windows:
 
  http://snaps.php.net/win32/php5-win32-latest.zip


 [2005-03-07 10:21 UTC] georg@php.net
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.php.net/manual/ and the instructions on how to report
a bug at http://bugs.php.net/how-to-report.php

See http://bugs.mysql.com/?id=8988 
 [2005-03-07 16:37 UTC] toma at smartsemantics dot com
I did read the documentation and this is what I see...

"The value of the AUTO_INCREMENT field that was updated by the previous query. Returns zero if there was no previous query on the connection or if the query did not update an AUTO_INCREMENT value."

I have a function that executes multi-queries and handles them, returning them (with results) in class form.  Using the standard you have now, I must execute mysqli_insert_id to see if I get a non-zero value, then do a preg_match on the SQL query to see if it is a valid INSERT query.  Doesn't it just make more sense to see if there is a non-zero value and assume it is valid, rather than getting values and having to double-check them b/c they might not even be inserts?

I must also point out that I had to make a creative loop in my function to match up individual SQL commands with each result set.  Having to do so makes that above task that much more cumbersome.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Sun Nov 03 07:01:28 2024 UTC