PDO ->prepare on mysql query won't work

Question

I have a mysql query that I'd like to wrap-up like this: $sql = $dbh->prepare("SELECT * FROM log"); Then, I execute it like this: $sql->execute(); , now my question is why can't I use it into a: foreach($dbh->query($sql) as $row) loop ? When I try to get it to run into the foreach loop the loggin form does not read from the db anymore. If I remove the dbh->prepare statement it works just fine, but I wanted to block SQL Injection on it. Thanks.

Answer 1

You are using prepared statements $db->prepare($my_sql_query). When you use prepared statements, you usually may have some variables binded to the query. For example

$my_query = 'SELECT * FROM users WHERE user_id=:user_id'
$prepared_statement = $db->prepare($my_query);
$prepared_statement->bindValue(':user_id', 123);

Now when you have binded your values you need to execute your query

$prepared_statement->execute();

When you execute the prepared statement it generates the actual sql code and then executes it in mysql. And then to retrieve the results you would execute

$record = $prepared_statement->fetch(); //if you want to get only one record from the table
$records = $prepared_statement->fetchAll(); // if you want to get multiple records from the table

foreach($records as $row) {
    // your code here...
}

If you're using $db->query() you should be able to get the results with the following code:

$sql = 'SELECT name, color, calories FROM fruit ORDER BY name';
foreach ($db->query($sql) as $row) {
    print $row['name'] . "\t";
    print $row['color'] . "\t";
    print $row['calories'] . "\n";
}

// outputs 
// apple   red     150
// banana  yellow  250
// kiwi    brown   75
// lemon   yellow  25
// orange  orange  300
// pear    green   150
// watermelon      pink    90

(taken from php.net)

There is a big difference between the methods query and prepare:

1. query method will directly execute the sql code you've entered
2. prepare will "prepare" your sql code. This process replaces all params in the sql code with some actual values (i.e. SELECT * FROM users WHERE user_id=:user_id will become SELECT * FROM users WHERE user_id=1). Thus, when you use prepared statements you need to do execute so that the code is send to mysql.

Answer 2

You need to fetch the results first (after you execute the statement):

$rows = $sql->fetchAll();
foreach($rows as $row){
  ...
}

---

query() expects string only (the SQL query). But you were passing the return result of prepare() to it, which is a PDOStatement object.

prepare() is used in succession with execute(), not query(). It makes sense to prepare your statements when you have input parameters that you want to pass to execute().

In short:

$stm = $dbh->prepare('SELECT...');
$stm->execute();

is equivalent to:

$stm = $dbh->query('SELECT...');

if you have no input parameters to send.