skip to Main Content

Struggling to understand how to get the ID of the newly inserted row.

This is my procedural mysqli connection and SQL INSERT statement:

class DB {
    
    var $conn;
    function __construct($servername, $username, $password, $dbname, $dbport = 0000) {
        $this->conn = new mysqli($servername, $username, $password, $dbname, $dbport );         
    }
 
// Run a query
    public function query($sql) {
        return $this->conn->query($sql);
    }

// Clean incoming data
    public function clean($data) {
        return $this->conn->real_escape_string($data);
    }    
}
    
define("DB_HOST","localhost");
define("DB_USER","myusername");
define("DB_PASS","mypass");
define("DB_NAME","mydbname");
define("db",new DB(DB_HOST, DB_USER, DB_PASS, DB_NAME));

$sql = db->query("INSERT INTO my_table (field_one, field_two, field_three) VALUES ( 'test1', 'test2', 'test3')");

I thought I could then do something (like this):

if (mysqli_query(db, $sql)) {
    $last_id = mysqli_insert_id(db);
    echo "New record created. Last inserted ID is: " . $last_id;
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error(db);
}

Error message:

Fatal error: Uncaught TypeError: mysqli_query(): Argument #1 ($mysql) must be of type mysqli, DB given in /var/www/vhosts/***.****.com/httpdocs/admin/test.php:36
Stack trace: #0 /var/www/vhosts/***.****.com/httpdocs/admin/test.php(36): mysqli_query() #1 {main} thrown in /var/www/vhosts/***.****.com/httpdocs/admin/test.php on line 36 line 36 

This would be the first line of the second block: if (mysqli_query(db, $sql)) {


Where am I going wrong? Would really appreciate an explanation w/ what and why?

Also might be a dumb question, but in the examples I’ve read online -none of which match my connection set up exactly- none mention whether the name of the column for ID matters? My format is"table_id" rather than just "id", and it is set to auto-increment.

2

Answers


  1. It would be helpful if you actually included the error message, but the problem is that your DB class is neither an extension of the Mysqli class, nor does it implement a relevant interface, and as such you cannot simply drop it into functions that expect a Mysqli object. As ADyson already mentioned your wrapper class is unnecessary and actively getting in your way here, as it will many other places.

    Ditch the wrapper class and use the Mysqli object directly.

    Also, global state is generally a bad idea, especially for DB connections. It allows "spooky action at a distance", aka code that appears completely unrelated having side-effects via shared objects in the global namespace, as well as handcuffing you to only ever being able to use a single DB connection without making significant rewrites.

    Generally it is best to pass a DB connection into the call or object that requires it, eg:

    $db = new Mysqli(...);
    
    foo($db, $arg1, $arg2);
    
    $f = new Foo($db, ...);
    
    Login or Signup to reply.
  2. Unfortunately, your code is destined to fail regardless of the changes below, but let me explain why you’re seeing the problem you encountered before explaining why this still wouldn’t work.


    The Error

    Your DB class isn’t a type of mysqli, but this can be fixed by changing class DB to:

    class DB extends mysqli
    

    Here’s a breakdown of what’s happening:

    <?php
    
    // Defines a new class, DB, which is type DB.
    class DB {}
    
    // Defines "db" as an instance of the DB class, still type DB.
    define("db",new DB(DB_HOST, DB_USER, DB_PASS, DB_NAME));
    
    // This is trying to pass "db" as the first argument to mysqli_query().
    if (mysqli_query(db, $sql)) {}
    

    The problem is that mysqli_query is expecting the following:

    function mysqli_query(
        mysqli $mysql,
        string $query,
        int|null $result_mode = MYSQLI_STORE_RESULT
    ): bool|mysqli_result
    

    As you can see, the first parameter expects type mysqli, whether instanced directly with new mysqli() or indirectly with extends mysqli. The fact that you’ve instanced a new mysqli inside of DB doesn’t make a difference, you could copy the entire mysqli class but if you name it DB it’s still going to be typed in that way without an extension.

    Once you make this change, your argument should be accepted.


    The Problem

    The underlying issue is that your DB class still won’t be appropriate for executing mysqli class code.

    If you take a look at the mysqli class docs, you’ll notice that your class isn’t appropriately designed to contain your connection information and will ultimately fail because the function you’re calling can’t access $conn. Your wrapper works for very basic use-cases to call the query() and clean() methods, but that’s all. It doesn’t actually inject the necessary information required for the parent mysqli class to function properly.

    Technically, you could remedy this by exposing $conn with a getter:

    <?php
    class DB {
        // ...other stuff
        public function getConn() {
            return $this->conn;
        }
    }
    
    mysqli_query(db->getConn(), $sql);
    

    Or by using the __toString() magic method which will always treat db as the connection when you aren’t calling other methods like db->query().

    <?php
    class DB {
        // ...other stuff
        public function __toString() {
            return $this->conn;
        }
    }
    
    mysqli_query(db, $sql);
    

    However, this seems really impractical unless you have some reason to hide the rest of the mysqli functionality.

    Wrappers

    This would almost be appropriate for creating a database wrapper containing logic for interacting with functions like mysqli_query inside of its own class, but it doesn’t seem the be the goal you have in mind.

    A wrapper is useful because it allows you to implement logic in one place. You’re using functions outside of this class requiring mysqli though, so it doesn’t actually aid you in this situation. However, a corrected example could be something like this:

    <?php
    class DB {
        // ...other stuff
        public function db_query($sql) {
            return mysqli_query($this->conn, $sql);
        }
    }
    
    // Now, if you change databases you can just edit db_query.
    db->db_query($sql);
    

    Child Classes

    The correct way to build a class in this manner would be to inject your __construct arguments into the parent class, but since you’re overwriting the query() method to call $conn, this would also result in a broken instance of your database class.

    An example of how you’d correctly build a child class would look something like this:

    <?php
    class DB extends mysqli {
        public function __construct($host, $user, $pass, $database, $port = 3306) {
            parent::__construct($host, $user, $pass, $database, $port);
        }
    
        // New custom methods go here.
    }
    
    // Now a fully functional mysqli child!
    $db = new DB(DB_HOST, DB_USER, DB_PASS, DB_NAME);
    

    My recommendation would be to just create a direct instance through $db = new mysqli() instead of going through any of these abstraction processes. Although abstracting classes this way has its uses, since you’re still in the learning stages I think it’s adding extra, unnecessary complexity.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search