Converting PHP query to PDO query

Question

I have some php that is receiving a variable from jquery and querying the DB. I recently learned that I need to use PDO to prevent SQL Injections and such so I have been trying to convert my query to it. I am new at php anyway so this is turning out to be more difficult than I thought (even though all the articles I read looked quite straightforward)...The DB connection is working and 'name' is receiving the right value but it is not updating the page like it used to. I am guessing it has to do with my loop that contains the json_encode. Below is my old php and then my attempt at turning it into PDO format.

Old PHP:

$dbstylename = $_POST['name'];
$result = mysql_query("SELECT * FROM style where stylename like '$dbstylename'");
$array = mysql_fetch_row($result);

echo json_encode($array);

mysql_close($con);
?>

New PDO attempt:

<?php

include 'db.php';

try {
    $dbConnection = new PDO('mysql:host=$dbhost;dbname=$dbhost;', $user, $pass);
    $dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    $dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    #Prepare the query
    $dbstylename = $_POST['name'];
    $result = $dbConnection->prepare('SELECT * FROM style where stylename like :dbstylename');
    #bind
    $result->bindParam(':dbstylename', $dbstylename, PDO::PARAM_STR);
    #execute
    if ($result->execute(array($dbstylename))) {
        while ($row = $result->fetch()) {
            json_encode($row);
        }
    }
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}
?>

================UPDATE==============================

In addition to @MadaraUchiha great answer and follow up help I had to change my jQuery from this (which worked before PDO):

$.ajax({
    url: '../test.php',
    type: 'POST',
    data: {'name' : target},
    dataType: 'json',
    success: function(data) {
        var styleid = data[0];
        var stylename = data[1];
        var stylecss = data[2];
        $('#codeTest').html("<b>id: </b><br />"+styleid+"<br /><b> stylename: </b><br />"+stylename+"<br /><b> stylecss: </b><br />"+stylecss);
    }
});

To this:

$.ajax({
    url: '../test.php',
    type: 'POST',
    data: {'name' : target},
    dataType: 'json',
    success: function(data) {
        var styleid = data.styleid;
        var stylename = data.stylename;
        var stylecss = data.stylecss;
        $('#codeTest').html("<b>id: </b><br />"+styleid+"<br /><b> stylename: </b><br />"+stylename+"<br /><b> stylecss: </b><br />"+stylecss);
    }
});

Answer 1

In my original post I mentioned my jQuery change. However, I read up a bit more about PDO in the manual (specifically: http://www.php.net/manual/en/pdostatement.fetch.php) and found that if I changed $row = $result->fetch(PDO::FETCH_ASSOC); from @MadaraUchiha answer to $row = $result->fetch(PDO::FETCH_BOTH); I could keep my original jQuery that used the array/bracket notation.

Answer 2

Let me start with this, it's great that you're working on improving from the old ext/mysql to PDO. Well done!

Well, first, you don't need to check for errors! Since you've set PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION, an Exception would be thrown if there's an error! so your if statement on

if ($result->execute(array($dbstylename))) {

Is redundant.

Second, since you've already bound the parameter with bindParam, passing it again with the array is also redundant.

Lastly, if you only expect one result, you can drop the while loop, or even use $result->fetchAll(PDO::FETCH_ASSOC) to fetch all of the result into a single array.

---

Now for the real problem, you aren't echoing the result of json_encode(), like you used to in the first script (You're just calling it without doing anything with the result).

Corrected code, with all of the above taken into account:

<?php

include 'db.php';

try {

    $dbConnection = new PDO('mysql:host=$dbhost;dbname=$dbhost;', $user, $pass);
    $dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    $dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    #Prepare the query
    $dbstylename = $_POST['name'];
    $result = $dbConnection->prepare('SELECT * FROM style where stylename like :dbstylename');
    #bind
    $result->bindParam(':dbstylename', $dbstylename, PDO::PARAM_STR);
    #execute
    $result->execute();
    $row = $result->fetch(PDO::FETCH_ASSOC);
    echo json_encode($row);

} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}
?>

Other than that, you're PDO code is flawless, keep it up!

Answer 3

Also sequence of json encoded strings with array elements are not equal to hole json encoded array. Replace while loop with

echo json_encode($result->fetchAll());