Converting from MySQL to MySQLi help!

BenSeagrave

So recently I've been told that I should be using the new MySQLi instead of MySQL syntax. I had a quick look online and on the php manual but couldn't really find anything to help.

If I was to do this in MySQL:

<?php
$db = mysql_connect("localhost" , "root", "") or die("Check connection parameters!");
mysql_select_db("testdb", $db) or die(mysql_error($db));

$query = "SELECT * FROM entries";
$result = mysql_query($query) or die(mysql_error($db));

while($row = mysql_fetch_assoc($result)) {
    echo "$row['author']";
    echo "$row['content']";
}
?>

How would I do this in MySQLi?

Also someone was telling me about another type of database connection I think it was called PDO. What's the difference between this and the other two and which one should I be using?

Thanks!

Derokorian

Well to make it real simple and not worry about doing it the OO way (which you should learn, but the transition is easier if you just focus on moving libraries first IMO) you would just do it like this:

<?php 
$db = mysqli_connect("localhost" , "root", "") or die("Check connection parameters!");
// Optionally skip select_db and use: mysqli_connect(host,user,pass,dbname) 
mysqli_select_db($db,"testdb") or die(mysqli_error($db)); 

$query = "SELECT * FROM entries"; 
$result = mysqli_query($db, $query) or die(mysqli_error($db)); 

while($row = mysqli_fetch_assoc($result)) { 
    echo "$row['author']"; 
    echo "$row['content']"; 
}

To do the exact same thing the OO way:

<?php
$db = new mysqli('localhost','root','','testdb');
if( $db->connect_error ) {
   die('Connect Error (' . $db->connect_errno . ') '
            . $db->connect_error);
}

$query = "SELECT * FROM entries";
$result = $db->query($query);
if( !$result ) {
   die('Query failed!<br>'.$db->error);
}
if( $result->num_rows == 0 ) {
   die('No rows returned');
}
while( $row = $result->fetch_assoc() ) {
   echo $row['author'];
   echo $row['content'];
}

HTH

Bonesnap

The one major difference I noticed immediately between MySQL and MySQLi is MySQLi functions require the database resource for its functions whereas for MySQL it's optional. Note this only applies if you use it procedurally.

dalecosp

Plus one for Bonesnap. That's pretty much my experience as well, although I was surprised the mysqli_fetch_array() did NOT require the link identifier....

Bonesnap

dalecosp;11004986 wrote:
Plus one for Bonesnap. That's pretty much my experience as well, although I was surprised the mysqli_fetch_array() did NOT require the link identifier....

Indeed.

And I just noticed many functions don't require the resource because they're not actually part of the mysqli class; they're part of the mysqli_result class. :eek:

laserlight

Bonesnap wrote:
And I just noticed many functions don't require the resource because they're not actually part of the mysqli class; they're part of the mysqli_result class.

Rather, those functions are part of the mysqli_result class because they don't require the connection resource but deal with the result retrieved 😉