Avatar of impressionexpress
impressionexpress
 asked on

I am getting 0 as result for mysqli_insert_id()

I have a PHP class that I created for my database connection and queries. Im having an issue with the mysqli_insert_id, I know the reason for this is because after my query function, I close the connection therefore when I call the last_insert_id function it returns 0, can someone guide me on how i can fix this issue.

    public function get_connection(){
        try{
            $this->connection = @mysqli_connect( $this->databaselink, $this->username, $this->password );
            if( $this->connection === false ){           
                throw new Exception( mysql_error() );
            }           
            if( !mysqli_select_db($this->connection, $this->database) ){
                echo "no db selected error";
                throw new Exception( mysql_error() );
            }
        }
        catch(Exception $e){
            echo $e->getMessage();
            //$this->log->logger( "MYSQL", $e, $query );
        }
    }
 
    public function close_connection(){
        mysqli_close( $this->connection );
        $this->connection = NULL;
    }
   
    public function query( $query ){
        $this->get_connection();
       
        try {
            if( empty($query) ){
                
				throw new Exception($query . " query empty");
            }
           
            $this->result = @mysqli_query($this->connection, $query);
            if( !$this->result ){
                throw new Exception( mysqli_error($this->connection) );
            } else {
                return $this->result;
            }
        } catch(Exception $e){
            echo $e->getMessage();
            //$this->log->logger( "MYSQL", $e, $query );
        }
		echo mysqli_info($this->connection); 
     //   $this->close_connection();
    }
	
	public function last_insert_id(){
	//	$this->get_connection();
		return mysqli_insert_id($this->connection);
	//	$this->close_connection();
	}
}

Open in new window

DatabasesPHP* mysql connectionsSQLMySQL Server

Avatar of undefined
Last Comment
impressionexpress

8/22/2022 - Mon
arnold

Without seeing the code, test whether the query you ran was successful and then get the ID.

Do not close the connection it might be part of your php function class definition.
David Favor

Get rid of the "@" (shutup) operator everywhere.

"@" is the bane of PHP, as it hides errors.

To guess, will require seeing all your code, as what you've provided looks to have many problems.

Tip: Best to echo your full INSERT syntax, then call mysqli_insert_id() directly after your INSERT, as this is the only way to return the correct last insert ID value. If any other SQL statement executes, there's a good chance a side effect of the SQL statement will clear (reset to 0) the last insert ID which is likely why you're getting back a 0 value on your call.
arnold

Your query shoukd be passed a connection handle, and only call get connection when the check on whether an existing connection is missing. Same for the get_id
IMHO, connection opening, closing shoukd be part of the main php versus reusable functions.
In your consideration, setup your query opens, closes on each query versus reusing the same connection for the entire session of processing of data.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
Chris Stanyon

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
David Favor

I'm with Chris. If you generate connection closes, then you're always going to end up with complex problems which will be near impossible to debug.

Either have a single exit point for all your code with a close before your exit.

Or, as Chris suggests, just let the connection close automagically on exit.
impressionexpress

ASKER
Thank you