Asked  6 Months ago    Answers:  5   Viewed   50 times

I just finished an introduction course in PHP, and throughout the stackoverflow forum people have recommended that I switch to PDO, prepared statements or MYSQLi, I briefly checked the manual but most of it went over my head.

I've been using mysql_* functions up till now so these concepts are new to me. I think they are used to access and perform database specific actions, but I'm not sure.

So what is the difference between PDO, prepared statements and MySQLi, are they different features that accomplishes the same task? Are they compatible in a script or is it "choose one or the other"? And lastly which offers the best performance?

Update: Thanks for the answers, I'll be hunting for more PDO tutorials.

For reference I also found the following posts useful:

Which one is fast and light - mysqli or PDO

mysqli or PDO - what are the pros and cons?

 Answers

72

At the basic level the mysql, mysqli and PDO extensions all answer the question how do I talk to the database? They all provide functions and functionality to connect to a database and send and retrieve data from it. You can use them all at the same time establishing several connections to the database at once, but that's typically nonsense.

mysql* is a very simple extension that basically allows you to connect to the database, send it SQL queries and not much else.
mysqli improves this (as the name suggests) by adding parameterized queries and a few other things into the mix.
PDO is an extension that abstracts several database drivers into one package, i.e. it allows you to use the same code to connect to MySQL, Oracle, MS SQL Server and a number of other databases without needing to use database specific extensions or rewrite your code when you switch databases (in theory at least). It also supports parameterized queries.

If you know you're going to be using MySQL exclusively, mysqli is a good choice. Especially since you can use it in a procedural way, what you're already used to from the mysql extension. If you're not familiar with OOP, that's helpful. Otherwise, PDO is a nice object oriented, flexible database connector.


* Note that the mysql extension is now deprecated and will be removed sometime in the future. That's because it is ancient, full of bad practices and lacks some modern features. Don't use it to write new code.

Thursday, June 10, 2021
 
Zulakis
answered 6 Months ago
94
try {
    // PDO code

    // Transaction start

    // Throw manual exception here if error occurs (transaction rollback too)
    throw new MyException("all went tits up");

   // Transaction commit

} catch (PDOException $e) {
    // Transaction rollback
    // Code to handle the exception
} catch (MyException $e) {
    // Transaction rollback
    // Code to handle the exception   
}

The thing is, you're going to have duplicate code which wont smell too nice. I would recommend just catching "Exception" e.g.:

try {
    // PDO code

    // Transaction start

    // Throw manual exception here if error occurs (transaction rollback too)
    throw new MyException("all went tits up");

   // Transaction commit

} catch (Exception $e) {
    // Transaction rollback
    // Code to handle the exception
}
Wednesday, March 31, 2021
 
TecHunter
answered 9 Months ago
79

You can use FIND_IN_SET for that:

DELETE FROM `shop_categories` WHERE FIND_IN_SET(id, :id)"
Saturday, May 29, 2021
 
Gilko
answered 7 Months ago
63

fetchAll returns an array containing all of the result set rows. So you can access to password with $data_array[0]['password'] if you used it. You may want use fetch instead.

$data_array = $stmt->fetch(PDO::FETCH_ASSOC);
Saturday, May 29, 2021
 
JakeGR
answered 7 Months ago
31

All of the mysqli functions/methods can fail in which case they will return false. I.e. if prepare() fails $stmt isn't an object you can call a method on but a bool(false). You have to check the return values and add some error handling, e.g.

$stmt = $mysqli->prepare('SELECT name FROM `rooms` WHERE r_id=?');
if ( !$stmt ) {
    printf('errno: %d, error: %s', $mysqli->errno, $mysqli->error);
    die;
}

$b = $stmt->bind_param('i', $roomID);
if ( !$b ) {
    printf('errno: %d, error: %s', $stmt->errno, $stmt->error);
}

$b = $stmt->execute();
if ( !$b ) {
  and so on and on

see http://docs.php.net/mysqli-stmt.errno et al


in this case you probably bumped into the problem that you can't create an other statement while there are still results/result sets pending for the previous statement.
see http://docs.php.net/mysqli-stmt.close:

Closes a prepared statement. mysqli_stmt_close() also deallocates the statement handle. If the current statement has pending or unread results, this function cancels them so that the next query can be executed.
Wednesday, September 1, 2021
 
Ryan Stewart
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share