I am getting 0 as result for mysqli_insert_id()

impressionexpress
impressionexpress used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Commented:
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 FavorFractional CTO
Distinguished Expert 2018

Commented:
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.
Distinguished Expert 2017

Commented:
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.
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Most Valuable Expert 2018
Distinguished Expert 2018
Commented:
Simple answer is to not call close on the DB connection. PHP will automatically close it when the script finishes.

I would also advise that you setup your connection as a singleton. Currently, you making a new connection each time you call a method. Edit the get_connection() method to re-use an existing connection if it exists.

class MyClass {
    private $connection;

    public function get_connection() {
        if ($this->connection == null) {
            $this->connection = mysqli_connect(...);
        }

        return $this->connection;
    }
}

Open in new window

Now you can call get_connection() several times and you'll only ever have one connection.

I would also advise setting up different methods for your CRUD operations. As it stands, your query() method could return a boolean or a result set. There's also no way of handling prepared statements, so you're open to SQL Injection. You'd be better off having distinct methods, such as Update(array $data, int $id), Delete(int $id), Create(array $data), GetById(int $id), GetAll() etc. Then in your Create method, you could return the auto_id without having to call a different method.
David FavorFractional CTO
Distinguished Expert 2018

Commented:
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.

Author

Commented:
Thank you

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial