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);
    }
});

Your PDO version is not echoing the encoded result. (Though if you want to only echo one row, you don't need the while loop.) — user1233508
– user1233508, Commented Nov 19, 2012 at 20:19
@Ibu I think it is querying fine but I don't think I am bring back the results correctly so that the jQuery can update the page..does that make sense? =\ — tehaaron
– tehaaron, Commented Nov 19, 2012 at 20:21
@DCoder I didn't think I needed the loop since I didn't use it before (because I only return 1 row at a time) but the closest article I found used it so I figured it wouldn't actually hurt since it will only return a single row anyway. — tehaaron
– tehaaron, Commented Nov 19, 2012 at 20:22

Madara's Ghost · Accepted Answer · 2012-11-19 20:27:16Z

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!

edited Nov 19, 2012 at 20:27

answered Nov 19, 2012 at 20:21

Madara's Ghost

176k52 gold badges274 silver badges314 bronze badges

Sign up to request clarification or add additional context in comments.

16 Comments

dev-null-dweller Over a year ago

Also passing an array to execute is redundant, since it was bound earlier.

tehaaron Over a year ago

Thanks so much! This worked on my friends server that I was originally testing on. Unfortunately, when I move it to mine I am getting a Response back of ERROR: SQLSTATE[HY000] [2002] php_network_getaddresses: getaddrinfo failed: Name or service not known so I guess I need to look into that now..sigh..lol it s never ending battle!

Madara's Ghost Over a year ago

@tehaaron: It probably means that your $dbhost is wrong. (Maybe you want localhost?

tehaaron Over a year ago

@MadaraUchiha it is localhost and all the settings worked before I enabled PDO. So I am guessing I need to check some ini files or socket files or something?

tehaaron Over a year ago

@MadaraUchiha I got the connection fixed for pdo on my server and it looks like I have a problem now. Instead of passing name: test1 it is passing test1: undefined it looks like it is taking the value and making it the key?

|

Vladimir · Accepted Answer · 2012-11-19 20:26:37Z

1

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());

answered Nov 19, 2012 at 20:26

Vladimir

5665 silver badges5 bronze badges

Comments

tehaaron · Accepted Answer · 2012-11-21 20:52:57Z

0

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.

answered Nov 21, 2012 at 20:52

tehaaron

2,25010 gold badges31 silver badges54 bronze badges

Collectives™ on Stack Overflow

Converting PHP query to PDO query

3 Answers 3

16 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

16 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related