mysqli_insert_id() is returning 0 but inserting the data

Question

The Code is:

class anything_i
{

/*Every variable is defined and hidden fro privacy.*/

public function connect(){
        return mysqli_connect(self::HOST,self::USERNAME,self::PASSWORD,self::DATBASE);
}



public function insertData($postData){
        $sqli = 'INSERT INTO `payments`(`item_name`,`price`,`email`,`date`,`time`,`detail`,`volume`,`ip`,`payment_status`) VALUES ("'.$productData['name'].'","'.$productData['price'].'","'.$postData['email'].'","'.$date.'","'.$time.'","'.$postData['list'].'","'.$productData['volume'].'","'.$ip.'","On Hold")';


       if(mysqli_query($this->connect(),$sqli)){
           print_r(mysqli_insert_id($this->connect()));
      }else{
                print_r(mysqli_error($this->connect()));
      }
   }
}

It is inserting the data and working fine but does not return anything other than a 0 (zero) not getting any error.

Let me clear that my table contains auto_increment and my connection is fine because it is entering data just fine. Please don't disregard the question because you found similar once answered. I also found similar questions but most of them have connection problems, some of them the no AUTO_INCREMENT column and there may be some which included another query in between. So, please read it before mentioning another answer.

Here is the proof of AUTO_INCREMENT:

What does the `connect()` function do? This is a bit of a guess, but it sounds like it might create a **new** connection, and `mysqli_insert_id` needs to run against the same one that executed the query. — iainn, Jun 28 '20 at 10:24
Duplicate of this quetion [Why is mysqli_insert_id() always returning 0?](https://stackoverflow.com/a/15332270/12232340) — , Jun 28 '20 at 10:31
Not your question but you should parameterize your query and use prepared statements. This is not secure as is. — user3783243, Jun 28 '20 at 10:33
@Dlk I run the function mysqli_insert_id () with the same connection as you can see. This answer doesn't help me much. — Prashant Rajpoot, Jun 28 '20 at 10:34
`$this->connect()` creates a *new connection* of which you're trying to find the last insert id. You must only call `$this->connect` once and reuse that connection in all your database calls. — deceze, Jun 28 '20 at 10:35
@PrashantRajpoot use col_id in I`NSERT` query & let me know? — KUMAR, Jun 28 '20 at 10:35
Thanks, man. I forgot that I was calling the function again and again. @deceze — Prashant Rajpoot, Jun 28 '20 at 10:38
Link I gave is showing you how to use connection as deceze said `if(mysqli_query($this->connect,$sqli)){` — , Jun 28 '20 at 10:39
Should I delete the question as it was actually a small mistake — Prashant Rajpoot, Jun 28 '20 at 10:42
Does this answer your question? [return 0 mysqli\_affected\_rows and mysqli\_insert\_id](https://stackoverflow.com/questions/43914660/return-0-mysqli-affected-rows-and-mysqli-insert-id) — Dharman, Jun 28 '20 at 11:40

Álvaro González · Answer 1 · 2020-06-28T10:43:51.750

6

I suspect this code has been migrated from the legacy mysql extension where mysql_connect():

Opens or reuses a connection to a MySQL server.

This is no longer the case with mysqli_connect():

Opens a connection to the MySQL Server.

You really need to store the connection in a variable and reuse that same connection. Currently you're starting many different connections within the same script run.

edited Jun 28 '20 at 10:43

answered Jun 28 '20 at 10:40

Álvaro González

142,137
41
261
360

Yes, That is the right answer because I forget to store the connection. New player :p – Prashant Rajpoot Jun 28 '20 at 10:41

score 1 · Answer 2 · answered Jun 28 '20 at 12:22

When you call mysqli_connect() it will create a new connection each time. It does not reuse the previous connection. When a new connection is created all the properties of the old one are lost.

You are calling mysqli_connect() every time you call $this->connect() so you will never get the errors or the auto-generated ID.

mysqli connection should be global to your application. It does not make sense to keep recreating the same connection because it will lead to terrible performance issues and it will cause you problems like this one. If you are using dependency injection it should be easy to create the mysqli at the start of your application and then pass it as an argument to your class' constructor.

Connecting to the database using mysqli is always the same 3 lines of code. Only the details you pass as arguments are different.

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli('localhost', 'user', 'pass', 'db_name');
$mysqli->set_charset('utf8mb4'); // always set the charset

You can then pass this to your constructor whenever you create an object and store it in a private property.

Your fixed code would like this:

<?php

class anything_i {
    private mysqli $db = null;

    public function connect(mysqli $db) {
        $this->db = $db;
    }

    public function insertData($postData) {
        /*
        ...
        */
        $stmt = $this->db->prepare('INSERT INTO `payments`(`item_name`,`price`,`email`,`date`,`time`,`detail`,`volume`,`ip`,`payment_status`) VALUES (?,?,?,?,?,?,?,"On Hold")');
        $stmt->bind_param('ssssssss', $productData['name'], $productData['price'], $postData['email'], $date, $time, $postData['list'], $productData['volume'], $ip);
        $stmt->execute();
    }
}

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli('localhost', 'inet', '5432', 'test');
$mysqli->set_charset('utf8mb4'); // always set the charset

$obj = new anything_i($mysqli);

mysqli_insert_id() is returning 0 but inserting the data

2 Answers2