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.
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();
}
}
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
Do not close the connection it might be part of your php function class definition.
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.
"@" 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.
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.
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.
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.
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.
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;
}
}
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.
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial