[PHP] Migrating from mysql_ to mysqli_

---------------------------------------------------------------------------------
This is a WIP blog post. I will continue updating it as I indulge in more mysqli.
-------------------------------------------------------------------------------

I now have the pleasure to show the process of moving from mysql_ to mysqli_. If you are still on mysql_, you will still be able to use mysql functionality. However, it is being depreciated and it is recommended to make the switch. You will get harassed on StackOverflow for suggesting mysql_* answers, or asking mysql_* questions.

The first change you will make will be the database connector.

Old Way

$connection = mysql_connect('host','user', 'pass');
mysql_select_db('yourDb');

New Way

$mysqli = new mysqli('host','user', 'pass','db');
if($mysqli ->connect_error)
{
   die('Failed: ' . $mysqli->connect_errno . ' ' . $mysqli ->connect_error);
}

The next bit I encountered was getting a query statement through. It would usually be as easy as this:

Old way:

$statement = mysql_real_escape_string('select * from myTable');
$result = mysql_query($statement);

The new way is a bit more complex:

1) Prepare your statement!

$statement = mysqli_prepare($connection, "SELECT name, weight, height FROM myTable WHERE id='".$id."'");

2) Execute your statement!

$statement -> execute();

3) Bind results to variables ! (The cool feature)

$statement -> bind_result($name, $weight, $height);

4) Store the results from the query.
$statement->store_result();

5) (optional) Need to check the row count?
$count = $statement->num_rows;

6) (optional) Need to access a specific row? 0 ?
$statement->data_seek(0);

7) Fetch results (can be done iteratively)
sample: 
$statement->fetch();

Now your binded variables are populated: check $name, $weight

That is a lot of calls for a simple query! You might like this instead:


You can use mysqli_real_escape_string, mysqi_query, mysqli_num_rows in the same manner as mysql_query, with ONE change to your code:
Old way:
<?php 
mysql_result("SELECT blog from TheBestBlogs where name='hazelfresh'"); 
?>

New Way: If using the procedural style as such, you must include the connection variable in your call. This holds true for all procedural calls with mysqli:
<?php 
mysqli_result($mysqli, "SELECT blog from TheBestBlogs where name='hazelfresh'"); 


if(mysqli_num_rows($mysqli, $mysqliResult)>0); 

$escapedWorld = mysqli_real_escape_string($mysqli, "hello world!");
?>


It is a pain to rewrite your queries this way if you use rows and column names: $result['id'] for example. Luckily, there is a way around that too for mysqli:

<?php

// ... document's example code:

    /* bind parameters for markers */
    $stmt->bind_param("s", $city);

    /* execute query */
    $stmt->execute();

    /* instead of bind_result: */
    $result = $stmt->get_result();

    /* now you can fetch the results into an array - NICE */
    while ($myrow = $result->fetch_assoc()) {

        // use your $myrow array as you would with any other fetch
        printf("%s is in district %s\n", $city, $myrow['district']);

    }
?>

Posted in: PHP