How do I convert a script using mysql_ functions t

问题:

EDIT: Whether or not to use mysqli_ is outside the scope of this question. Consider using PDO.

---

What steps need to be taken to convert a script from using the deprecated mysql_ functions to mysqli_?

Is there anything that needs to be done differently when using mysqli_ instead of mysql?

Here's a basic script using mysql_ functions:

<?php

//define host, username and password

$con = mysql_connect($host,$username,$password);
if (!$con) {
    die('Could not connect: ' . mysql_error());
}

$db_name ="db1";
mysql_select_db($dbname, $con);

$value1 = mysql_real_escape_string($input_string);

$query = 'SELECT * FROM table1 WHERE table1.col1=' . $value1 . '';
$result = mysql_query($query, $con);

while($row = mysql_fetch_assoc*$result)
{
    $col1 = $row['col1'];
    $col2 = $row['col2'];

    echo $col1 . ' ' . $col2 . '<br />';
}

mysql_close($con);
?>

回答1:

Note: Converting from mysql_ to mysqli_ may not be optimal. Consider PDO if you're prepared to convert all of your code to OOP.

It can be tempting to try to replace all instances of mysql_ with mysqli_ and pray it works. You'd be close but not quite on point.

Connecting to the database:

Fortunately, mysqli_connect works closely enough to mysql_query that you can just swap out their function names.

mysql_:

$con = mysql_connect($host, $username, $password);

mysqli_:

$con = mysqli_connect($host, $username, $password);

Selecting a database

Now, with most of the other functions in the mysqli_ library, you'll need to pass mysqli_select_db the database connection as its first parameter. Most of the mysqli_ functions require the connection object first.

For this function, you can just switch the order of the arguments you pass to the function. If you didn't pass it a connection object before, you have to add it as the first parameter now.

mysql_:

mysql_select_db($dbname, $con);

mysqli_:

mysqli_select_db($con, $dbname);

As a bonus, you can also pass the database name as the fourth parameter to mysqli_connect - bypassing the need to call mysqli_select_db.

$con = mysqli_connect($host, $username, $password, $dbname);

Sanitize user input

Using mysqli_real_escape_string is very similar to mysql_real_escape_string. You just need to pass the connection object as the first parameter.

mysql_:

$value1 = mysql_real_escape_string($input_string);

mysqli_:

$value1 = mysqli_real_escape_string($con, $input_string);

Very Important: Preparing and Running a Query

One reason the mysql_ functions were deprecated to begin with was their inability to handle prepared statements. If you simply convert your code to mysqli_ without taking this important step, you are subject to some of the largest weaknesses of the mysql_ functions.

It's worth reading these articles on prepared statements and their benefits:

Wikipedia - Prepared Statements

PHP.net - MySQLi Prepared Statements

Note: When using prepared statements, it's best to explicitly list each column you're attempting to query, rather than using the * notation to query all columns. This way you can ensure you've accounted for all of the columns in your call to mysqli_stmt_bind_result.

mysql_:

$query = 'SELECT * FROM table1 WHERE table1.col1=' . $value1 . '';
$result = mysql_query($query, $con);
while($row = mysql_fetch_assoc*$result)
{
    $col1 = $row['col1'];
    $col2 = $row['col2'];

    echo $col1 . ' ' . $col2 . '<br />';
}

mysqli_:

$query = 'SELECT col1,col2 FROM table1 WHERE table1.col1=?';
if ($stmt = mysqli_prepare($link, $query)) {

    /* pass parameters to query */
    mysqli_stmt_bind_param($stmt, "s", $value1);

    /* run the query on the database */
    mysqli_stmt_execute($stmt);

    /* assign variable for each column to store results in */
    mysqli_stmt_bind_result($stmt, $col1, $col2);

    /* fetch values */
    while (mysqli_stmt_fetch($stmt)) {
        /*
            on each fetch, the values for each column 
            in the results are automatically stored in 
            the variables we assigned using 
            "mysqli_stmt_bind_result"
        */
        echo $col1 . ' ' . $col2 . '<br />';
    }

    /* close statement */
    mysqli_stmt_close($stmt);
}

Showing errors

Showing errors works a little differently with mysqli_. mysqli_error requires the connection object as its first parameter. But what if the connection failed? mysqli_ introduces a small set of functions that don't require the connection object: the mysqli_connect_* functions.

mysql_:

if (!$con) {
    die('Could not connect: ' . mysql_error());
}

if (!$result) {
    die('SQL Error: ' . mysql_error());
}

mysqli_:

/* check connection error*/
if (mysqli_connect_errno()) {
    die( 'Could not connect: ' . mysqli_connect_error() );
}

/* check query error */
if ($stmt = mysqli_prepare($link, $query)) {

    // ... execute query

    if (mysqli_stmt_error($stmt)) {
        echo 'SQL Error: ' . mysqli_stmt_error($stmt);
    }
}

回答2:

EXAMPLE.

This is your dbc class

<?php

class dbc {

    public $dbserver = 'server';
    public $dbusername = 'user';
    public $dbpassword = 'pass';
    public $dbname = 'db';

    function openDb() {    
        try {
            $db = new PDO('mysql:host=' . $this->dbserver . ';dbname=' . $this->dbname . ';charset=utf8', '' . $this->dbusername . '', '' . $this->dbpassword . '');
        } catch (PDOException $e) {
            die("error, please try again");
        }        
        return $db;
    }

    function getAllData($qty) {
        //prepared query to prevent SQL injections
        $query = "select * from TABLE where qty = ?";
        $stmt = $this->openDb()->prepare($query);
        $stmt->bindValue(1, $qty, PDO::PARAM_INT);
        $stmt->execute();
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return $rows;
    }    
?>

your PHP page:

<?php 
require "dbc.php";

$getList = $db->getAllData(25);

foreach ($getList as $key=> $row) {
         echo $row['columnName'] .' key: '. $key;
    }

回答3:

Do not convert mysql_ functions to mysqli_. Period.

There is not a single reason to do that.

First, there is no reason to do that just mechanically, changing only function names leaving algorithm the same:

Without implementing parameterized queries such a move makes very little sense.
If your only concern is "Deprecated" errors - you can just turn them off with

error_reporting(E_ALL & ~E_DEPRECATED);

and continue happily using old mysql_*
Note that you will need that only in 2-3 years, when PHP 5.5 will reach shared hostings.
So, there is no need to hurry as well.

Second, what you really need is to eliminate all the bare API calls from the code

encapsulating them in to some sort of abstraction library. This should be your main concern, not a particular API used in this library, which could be changed in a wink.

Finally, the only real reason for switching from mysql_* to mysqli_* is parameterized queries.

And mysqli is totally unusable with them.

With prepared statements PDO is the only choice you have.

Let me show you what I mean.
Imagine we have an array of checkboxes from HTML form to add into query dynamically.
With PDO we can have some relatively sane and quite concise (however still uselessly complex and polluted) code:

$in  = str_repeat('?,', count($_GET['cat']) - 1) . '?';
$sql = "SELECT * FROM table WHERE category IN ($in)";
$stm = $db->prepare($sql);
$stm->execute($_GET['cat']);
$data = $stm->fetchAll();

With mysqli such a trivial case will take you hours of writing and debugging several pages of extremely intricate code.

Just try it and see.

However, even PDO require some obscure and useless code to create certain query parts. So, the best way would be to use some more intelligent library, such as safemysql, which will do all the job internally, from binding to fetching, making all your code into single line:

$data = $db->getALL("SELECT * FROM table WHERE category IN (?a)", $_GET['cat']);