How to get last id inserted to mysql using OOP PHP?

Question

I have the following PHP class, im using this to connect to db and make a new instance:

class db{

    public $db_connection;

    public function __construct(){

        $this->db_connection = new mysqli("127.0.0.1","user","passwd","table");
        $this->db_connection->set_charset("utf8");

        if($this->db_connection->connect_errno) {
            echo "Failed to connect to database: " . $db_connection->connect_error;
        }
    }

    public function __destruct(){
        return $this->db_connection->close();
    }
}

The code im using to add stuff to the database:

$db = new db();

$success = $db->db_connection->query(
    "INSERT INTO users(
        name
    )
    VALUES(
        '".$_POST["firstname"].'
    )"
);

Get the ID of the inserted element aboue:

if($success){
    $user_id = $db->db_connection->insert_id;
    echo $user_id; // outputs 0
}

I am getting the value 0 even tho i have several entries in the database, is there another way around this? The id also have AUTO INCREMENT on the table

Note: Code is simplefied, and not the whole structure, but enough to explain the real issiue.

There is an issue with quotation marks in your code, does that also exist in your real code? Because this does not look like it will work. — Erik, Apr 14 '16 at 14:32
just as a note: using $POST like that directly in your query is a one-way trip to SQL injection hacking. — Tuncay Göncüoğlu, Apr 14 '16 at 14:32
code is tested and works, and YES, this is not the whole code put a simplefied example, just to point out the issiue itself. — sdfgg45, Apr 14 '16 at 14:34
are we to assume you have an auto-incrementing id field on the users table? — Jeff Puckett, Apr 14 '16 at 14:34
Your quotes are off. You're also using a raw `$_POST`, which is vulnerable to [SQL Injection](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Lastly, you should always echo out the MySQL errors — Machavity, Apr 14 '16 at 14:34
If you are getting 0 then basically the INSERT has failed and you are not checking correctly that it has failed or succeeded — RiggsFolly, Apr 14 '16 at 14:40
Possible duplicate of [mysqli last insert id](http://stackoverflow.com/questions/19738283/mysqli-last-insert-id) — Nasreddine, Apr 14 '16 at 14:40
Enteries are added with success. like mentioned no errors in the code itself — sdfgg45, Apr 14 '16 at 14:41
however the user_id is autoincrement try to insert on it a null value as this : "INSERT INTO users VALUES(NULL, ' " . $_POST[firstname]. ' " )" — PacMan, Apr 14 '16 at 14:45

score 1 · Accepted Answer · answered Apr 14 '16 at 15:27

1

Why are you separating the INSERT from polling the insert id? In both cases a failure means you haven't added a row. If you do anything else with the database link in between you'll run into complications which may be what is happening here.

if ($db->db_connection->query($insert)) {
   $user_id=$db->db_connection->insert_id;
} else {
   // add some error handling/logging
}

answered Apr 14 '16 at 15:27

symcbean

47,736
6
59
94

Dont see the difference, $insert is the sql? – sdfgg45 Apr 14 '16 at 17:15

How to get last id inserted to mysql using OOP PHP?

1 Answers1