PHP PDO prepared query won't return results from for loop

Question

Hi,

I have the following code:

$link = new PDO("mysql:dbname=$databasename;host=127.0.0.1",$username,$password);
$query = $link->prepare("SELECT * FROM index WHERE sbeid=:idvar");
for($j = 1; $j < count($array); $j++)
{
    if($array[$j][16] == "TRUE" || $array[$j][16] == "FALSE")
    {
        $paramforquery = $array[$j][25];
        $query->bindParam(":idvar",$paramforquery);
        $query->execute();
        $result = $query->fetchAll();
        //do things with the $result
        $query->closeCursor();
    }
    //else if, do stuff
}
$link = null;

$array is a large array composed of input from a CSV file that successfully loads via fopen().

My problem is this: the query just doesn't work. I know for a fact (ran the query directly on the server with some sample values from the file) that the data is in the database, but when i var_dump the $results each time the for loop runs, I just get an empty array.

What am I doing wrong?

TIA.

Answer 1

Are you sure you are getting a connection ?

try {
    $link = new PDO("mysql:dbname=$databasename;host=127.0.0.1",$username,$password);
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
}

This will catch any exceptions from trying to connect. If this works ok, try putting the query under the $link line and see what's returned.

If your query runs manually, i'd say its something to do with your DB connection. Make sure you've got error reporting turned on.

Additional: In your query you have this :idvar ? Shouldnt you be using a PHP variable like this $idvar.

so

$query = $link->prepare("SELECT * FROM index WHERE sbeid=" . $idvar);

Answer 2

Increase the error reporting - the standard advice.
Set the error mode of the pdo object to ERRMODE_EXCEPTION - you hardly can miss an error that way.
Use a debugger or add some debug output to your script - a real debugger is way better.

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);  
$array = foo();
echo '<pre>Debug: |array|=', count($array), '</pre>';

$link = new PDO("mysql:dbname=$databasename;host=127.0.0.1",$username,$password);
$link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);  
$query = $link->prepare("SELECT * FROM index WHERE sbeid=:idvar");
$query->bindParam(":idvar", $paramforquery);

foreach($array as $row) {
    echo '<pre>Debug: row[16]='; var_dump($row[16]); echo '</pre>';
    if($row[16] == "TRUE" || $row[16] == "FALSE") {
     $paramforquery = $row[25];
     echo '<pre>Debug: paramforquery='; var_dump($paramforquery); echo '</pre>';
     $query->execute();
     echo '<pre>Debug: rowcount='; var_dump($query->rowCount()); echo '</pre>';
     $result = $query->fetchAll();
     //do things with the $result
     $query->closeCursor();
    }
    //else if, do stuff
}
$link = null;