current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote -1 down vote favorite

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!

share|improve this question
    
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 9:37
    
BTW, why use prepare when you are using String Interpolation and not really prepare ing your statement? –  Hanky 웃 Panky May 14 '13 at 9: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 9: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 9:43
    
Nevermind, my issue was in the SQL, sorry. I'm now using the prepare/execute properly –  Corey Thompson May 14 '13 at 9:47
add comment

3 Answers

up vote 0 down vote

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++)
share|improve this answer
    
So should I use the while loop or the new for loop? –  Corey Thompson May 14 '13 at 9:44
    
@CoreyThompson You can try both, but while is better than for, for fetching records from db. –  Yogesh Suthar May 14 '13 at 9: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 9: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 9:46
add comment
up vote 0 down vote

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)

share|improve this answer
add comment
up vote -1 down vote

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));
share|improve this answer
    
I have now changed it to this, however the for loop still doesn't output "things" :( –  Corey Thompson May 14 '13 at 9: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 9:46
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.