PHP PDO sql query and results

Question

I'm having issues implementing an SQL query using PDO.

$friend_emails = $pdo->prepare("SELECT DISTINCT User2
                    FROM members
                    WHERE User1 = '$user'
                    UNION
                    SELECT DISTINCT User1
                    FROM members
                    WHERE User2 = '$user'");
$friend_emails->execute();

for($i=0; $row = $friend_emails->fetch(); $i++) { 
    echo "foo";
}

"foo" doesn't show at all. I var_dumped $row and $friend_emails->fetch() both of which are a

boolean false

so I'm not exactly sure why that is, I thought it would return an array of the data.

Any help is greatly appreciated!

After the `execute()` call check `$friend_emails->errorInfo()` for any error messages. Besides, if you use a `prepare()` statement, you should not insert the dynamic values there, but in the `execute()` later on. — Sirko, May 14 '13 at 09:37
BTW, why use `prepare` when you are using String Interpolation and not really `prepare` ing your statement? — Hanky Panky, May 14 '13 at 09:38
@Sirko I added the ->errorInfo(), do I need to echo it? If not then there are no errors. — Corey Thompson, May 14 '13 at 09:40
@CoreyThompson `errorInfo()` returns an array with information about what went wrong. You could use `var_dump()` to have a look at it outputs in debugging. In a productive system you should have a more sophisticated error handling. — Sirko, May 14 '13 at 09:43
Nevermind, my issue was in the SQL, sorry. I'm now using the prepare/execute properly — Corey Thompson, May 14 '13 at 09:47

score 1 · Accepted Answer · answered May 14 '13 at 09:39

1

use while here

while($row = $friend_emails->fetch()) { 
   echo "foo";
}

Your for loop will never execute because

for($i=0; $row = $friend_emails->fetch(); $i++)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ // here $i is not present

The correct format will be

for($i=0; $i <= count($friend_emails->fetch()); $i++)

answered May 14 '13 at 09:39

Yogesh Suthar

30,424
18
72
100

So should I use the while loop or the new for loop? – Corey Thompson May 14 '13 at 09:44
@CoreyThompson You can try both, but while is better than for, for fetching records from db. – Yogesh Suthar May 14 '13 at 09:46
Just realised my issue was an SQL issue, however the while loop is better so I'm using that. Thanks! – Corey Thompson May 14 '13 at 09:46
`count($friend_emails->fetch())` will return **1**... And it will run only once... **Only the `while` loop is applicable here**... – shadyyx May 14 '13 at 09:46

score 1 · Answer 2 · edited May 23 '17 at 12:29

Your problem is wrong variable names.

a variable returned by $pdo->prepare() contains not a single email, but PDO statement. Thus it ought to be named accordingly.
Also you are not using prepared statements while you should

So, mo make this code consistent and readable:

$sql = "SELECT DISTINCT User2 FROM members WHERE User1 = ?
        UNION
        SELECT DISTINCT User1 FROM members WHERE User2 = ?";
$stmt = $pdo->prepare();
$stmt->execute(array($user,$user));
$emails = $stmt->fetchAll(PDO::FETCH_COLUMN, 0)

and now you have your emails in a convenient array which you can iterate over:

foreach($emails as $email) {
    echo $email;
}

Also you have to configure your PHP and PDO as shown in this answer to be able to see all the errors if any occurred (as query error could be also quite possible reason for the whole code fail)

score -1 · Answer 3 · answered May 14 '13 at 09:39

-1

change your code to

$friend_emails = $pdo->prepare("SELECT DISTINCT User2
                    FROM members
                    WHERE User1 = :user
                    UNION
                    SELECT DISTINCT User1
                    FROM members
                    WHERE User2 = :user");
$friend_emails->execute(array('user' => $user));

answered May 14 '13 at 09:39

Miguelo

1,078
6
13

I have now changed it to this, however the for loop still doesn't output "things" :( – Corey Thompson May 14 '13 at 09:42
My error was in the SQL, sorry my bad. Thanks for your help and I am now using this code. – Corey Thompson May 14 '13 at 09:46

PHP PDO sql query and results

3 Answers3