How do I rewrite this php/mysql query using PDO?

I have many queries written in the following format:
list($blocked_id, $tries) = mysql_fetch_array(mysql_query("SELECT id, tries FROM BlockedIPs WHERE ip='$ip'"));

Open in new window


I would like to convert them all to use PDO.  How should I rewrite this query using whatever is considered "best practices"?
jeff_zuckerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
You might start with something like this.  I can't really test because I don't have your dataset, but if you plug in your credentials and run this, it should get you some useful output.  See if that helps and post back if you still have questions.

<?php // demo/temp_jeff_zucker.php
/**
 * SEE: http://www.experts-exchange.com/questions/28689925/How-do-I-rewrite-this-php-mysql-query-using-PDO.html
 * REF: http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
 */
error_reporting(E_ALL);

// ORIGINAL
// list($blocked_id, $tries) = mysql_fetch_array(mysql_query("SELECT id, tries FROM BlockedIPs WHERE ip='$ip'"));


/**
 * REVISED TO USE PDO - COMPLETE EXAMPLE
 */

// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$dsn = "mysql:host=$db_host;dbname=$db_name";
try
{
    $pdo = new PDO($dsn, $db_user, $db_word);
}
catch(PDOException $exc)
{
    var_dump($exc);
    trigger_error('NO PDO Connection', E_USER_ERROR);
}
// var_dump($pdo);

// DEFINE THIS VARIABLE BEFORE USING IT IN THE BINDPARAM() METHOD
$ip    = 'whatever';

// CREATE A QUERY FOR USE WITH BINDPARAM()
$sql = "SELECT id, tries FROM BlockedIPs WHERE ip = :ip";

// PREPARE THE QUERY
$pdos = $pdo->prepare($sql);

// BIND THE VARIABLE AND TRY THE QUERY
$pdos->bindParam(':ip', $ip, PDO::PARAM_STR);

try
{
    $pdos->execute();
}
catch(PDOException $exc)
{
    var_dump($exc);
    trigger_error($exc->getMessage(), E_USER_ERROR);
}

// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = $pdos->rowCount();
$num_fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND NO DATA ";
    echo PHP_EOL;
}
else
{
    echo "<br/>QUERY: $sql ";
    echo "<br/>FOUND $num_fmt ROWS OF DATA ";
    echo PHP_EOL;
}

// RETRIEVE THE DATA
while ($row = $pdos->fetchAll(PDO::FETCH_OBJ))
{
    // ROW BY ROW PROCESSING IS DONE HERE
    foreach ($row as $key => $obj)
    {
        echo "$key: ";
        print_r($obj);
        echo PHP_EOL;
    }
}

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.