mysql到mysqli连接代码失败了吗？

Question

I've seen this code that's been floating around, and also the fixed? version. Basically I've gotten this to work:

mysql_connect("host","client_name","client_pw");

mysql_select_db("database");

$q=mysql_query("SELECT * FROM table");

while($e=mysql_fetch_assoc($q))

$output[]=$e;

print(json_encode($output));

mysql_close();

but for some reason I feel it should be in mysqli. I'm new, and tried to write an equivalent mysqli OO code:

$mysqli = new mysqli("host", "client_name", "client_pw");
$mysqli->select_db("database");

$q = "SELECT * FROM table";

 while($e=$mysqli->fetch_assoc($q))
            $output[]=$e;
     print(json_encode($output));
    mysql_close();

It fails. I've tried other combinations, such as preparing a query and executing it, and setting that as $e, but all fail.

Do I have to manually build the array for the json_encode or something?

Maybe a better question is why I want to reinvent the wheel, but this has been bothering me.

Answer 1

Ah, I see you are not one with the database. Let us perform an exercise.

Close your eyes, breathe in, breathe out.

Relax.

You are one with the database.

You are one with the code.

Repeat after me.

Prepare.

Bind.

Execute.

Repeat it.

Again.

This is your new mantra, my friend.

You've accidentally skipped a step in your existing code. Let's throw it out and start over.

I am going to show you how to use PDO, one of the better ways PHP has to communicate with a database. It's less convoluted than the mysqli extension.

// Make sure these variables contain the correct data.
    $pdo = new PDO("mysql:host=$hostname;dbname=$database", $username, $password);
// Ask PDO to throw exceptions instead of warnings.
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Here's our SQL.  We're getting back a PDOStatement object here.
    $sh = $pdo->prepare('SELECT * FROM Foo WHERE bar = ?');
// That question mark is a placeholder.  bindValue lets us replace the question mark
// with the specified data.  This is called a prepared statement.  The end result is
// *complete and total immunity* from SQL Injection, if performed correctly.
    $sh->bindValue(1, "I'm looking for a bar that is equal to this.");
// Okay, we've bound everything, let's run the query.
    $sh->execute();
// And assuming there are no errors (note my severe lack of error handling),
// we should now have our complete list of data from the database.
    print_r($sh->fetchAll(PDO::FETCH_ASSOC));

// Alternatively, we could pass bound variables as an array to execute:
    $sh = $pdo->prepare('SELECT * FROM Foo WHERE bar = ?');
    $sh->execute(array( "I'm a bar!" ));

// And of course, we can use variables in the binding...
    $bar = 746;
    $sh = $pdo->prepare('SELECT * FROM Foo WHERE bar = ?');
    $sh->bindValue(1, $bar);
    $sh->execute();

PDO's support for prepared statements and placeholders makes it one of the best choices for database access in modern PHP.

(mysqli also has access to prepared statements, but it forces you to also bind result variables, and that can be damned awkward under a lot of circumstances.)

fetchAll(PDO::FETCH_ASSOC) returns a multidimensional array. The outer array is numerically indexed, each value being a row. Each row is a string-keyed array, where the keys are column names and the values are the data from the database. There are a few other things that fetchAll can do, though I haven't found many of them to be useful. You can also fetch one row at a time

You can probably pass the results directly to json_encode, if you'd like, and not suffer too many problems.

Understand that you will want to add appropriate error detection to this code. I have omitted it here for brevity.