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
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:
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 ofmysqli
, but this can be fixed by changingclass DB
to:Here’s a breakdown of what’s happening:
The problem is that
mysqli_query
is expecting the following:As you can see, the first parameter expects type
mysqli
, whether instanced directly withnew mysqli()
or indirectly withextends mysqli
. The fact that you’ve instanced anew mysqli
inside ofDB
doesn’t make a difference, you could copy the entiremysqli
class but if you name itDB
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 executingmysqli
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 thequery()
andclean()
methods, but that’s all. It doesn’t actually inject the necessary information required for the parentmysqli
class to function properly.Technically, you could remedy this by exposing
$conn
with a getter:Or by using the
__toString()
magic method which will always treatdb
as the connection when you aren’t calling other methods likedb->query()
.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: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 thequery()
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:
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.