CODEWITHSUNDEEP
phpmysqlsql
hours_wasted
hours_wasted

Reputation: 43

mysqli_insert_id still returning a value even after select queries

I have a function that is not acting as I expect it to. Its purpose is to return an array of rows for select queries, or the insert id for Insert queries. For some reason if there is an insert query followed by a select query, the part of the function where it tests whether or not the query was an insert or not fails because inesrt_id still returns the id of the inserted row. I could program around this but I'd really like to understand why it's happening.

My understanding was that if the most recent query is a select, then there should be no insert id. I'm new to mysqli, so maybe the query doesn't really 'end' when I think it does so the new select counts as part of the same query? I can get it to work fine if I recreate the connection on each query but that's not practical. Here's the code for the query function. 

public function __construct(){
    parent::__construct();
    //        @mysql_select_db ('public') OR die('Could not select the database: ' . mysql_error() );
    $this->connection = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_TABLE, DB_PORT)OR die ('Could not connect to MySQL: ' . mysql_error());
    $this->db = $this;
}

public function query_return($querystring){

    $response = array();
    $this->result_object = $this->connection->query($querystring);
    if($this->result_object != FALSE) {
        $last_id = $this->connection->insert_id;
        if (!empty($last_id)) {
            $response = $last_id;
        }
        else {
            while ($row = mysqli_fetch_assoc($this->result_object)) {
                array_push($response, $row);
            }
        }
    }
    else{
        $response = PEAR::raiseError('There was a problem with the query: '. mysql_error());
    }
    return $response;


}

EDIT: Ok so thanks to the guys below I added an is_object test to the function. I couldn't directly replace the true/false test as I actually use this function for deletions too and I want to know when it fails. I wrapped the insert_id test with the is_object test and things seem to be working now. I guess that an insert_id will be returned for a deletion query if there was a previous insert on the connection but I don't think that will cause problems as those functions only fail if they receive FALSE from the query_return.

I still don't really fathom why mysqli_insert_id would return a value after a select query when the documentation leads me to believe that it shouldn't but at least my code is working. This query_return function is tweaked from 'mysql_' (no 'i') procedural versions of these methods and it worked with those versions so there's something I'm still not understanding. Anyways here's the tweaked function.

private function query_return($querystring){

    $response = array();
    $this->result_object = $this->connection->query($querystring);
    if($this->result_object != FALSE) {
        if (!is_object($this->result_object)) {
            $last_id = $this->connection->insert_id;
            if (!empty($last_id)) {
                $response = $last_id;
            }
        }
        else {
            while ($row = mysqli_fetch_assoc($this->result_object)) {
                array_push($response, $row);
            }
        }
    }
    else{
        $response = PEAR::raiseError('There was a problem with the query: '. mysql_error());
    }
    return $response;
}

Upvotes: 4

Views: 502

Answers (3)

exussum
exussum

Reputation: 18558

Looking though the source of PHP its a wrapper to https://dev.mysql.com/doc/refman/5.0/en/mysql-stmt-insert-id.html

This documentation says the followng

LAST_INSERT_ID() (with no argument) returns a BIGINT (64-bit) value representing the first automatically generated value that was set for an AUTO_INCREMENT column by the most recently executed INSERT statement to affect such a column

https://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

I would suggest changing your if statement to 

 if (!is_object($this->result_object)) {

this will check if there are any results to retrieve

The PHP documentation should probably be updated to reflect this though, As it stands its obvious behaviour

Upvotes: 3

meda
meda

Reputation: 45500

That's how it works, mysqli_insert_id Still returns a value because it's the same connection. You will need to close and open a new one for it to be set to 0.

Upvotes: 1

MRazz
MRazz

Reputation: 71

I believe the variable is retaining its value from the last call. Try adding a unset($last_id); like below. Let me know if it doesn't work that way. Cheers.

public function __construct(){
    parent::__construct();
    //        @mysql_select_db ('public') OR die('Could not select the database: ' . mysql_error() );
    $this->connection = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_TABLE, DB_PORT)OR die ('Could not connect to MySQL: ' . mysql_error());
    $this->db = $this;
}

public function query_return($querystring){

    $response = array();
    $this->result_object = $this->connection->query($querystring);
    if($this->result_object != FALSE) {
        $last_id = $this->connection->insert_id;
        if (!empty($last_id)) {
            $response = $last_id;
        }
        else {
            while ($row = mysqli_fetch_assoc($this->result_object)) {
                array_push($response, $row);
            }
        }
        unset($last_id);    //<--------here
    }
    else{
        $response = PEAR::raiseError('There was a problem with the query: '. mysql_error());
    }
    return $response;


}

Upvotes: 0

Related Questions