Explore our sites

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

UPDATE:
WORKING CODE, Thanks jon for the push in the right direction.

<?php

     $stmt2 = $conn->prepare("SELECT * FROM userItems WHERE id=:id");

        foreach ($moodItems as $id2)
            {

    // bind the parameters
        $stmt2->bindValue(":id", $id2);    

      if ($stmt2->execute()) {

            if($result = $stmt2->fetch(PDO::FETCH_ASSOC)) {

    // initialise an array for the results 
            $itemName2 = $result['itemName'];
                $name2 = $result['userId'];
              echo $itemName2."<br>";
              echo $name2."<br>";
        }
     }
   }   
      ?>

I am having trouble using a SQL query in a foreach loop. I am trying to use the value from the first query(which is an array) and use it in the second query.

The first query works correctly to give me the proper value(array).But for some reason when I try to use the array in the foreach it does not work properly, it does not show any errors...it just does not fetch any data from the database.
IE/ echo $itemName2; <----does not get any info from database

Any help would be great. Thanks.

here is the code I am working with:

<?php           
$attrs = array(PDO::ATTR_PERSISTENT => true);

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// prepare the statement.
$stmt = $conn->prepare("SELECT * FROM userMoodboard WHERE name=:name");

// bind the parameters
$stmt->bindValue(":name", $loggedInUser->username);

// initialise an array for the results 
if ($stmt->execute()) {

    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $imageUrl = $row['imageUrl'];
            $moodItems = $row['moodItems'];
            $moodItems = json_decode($moodItems);

?>
<img src='<?php echo $imageUrl;?>' class="thumbnail"></img>
    <?php
    $stmt = $conn->prepare("SELECT * FROM userItems WHERE id=:id");

        foreach ($moodItems as $id)
               {
    // bind the parameters
        $stmt->bindValue(":id", $id);    

    // initialise an array for the results 
        if($stmt->execute()) {
            $itemName2 = $row['itemName'];
                $name2 = $row['userId'];

              echo $itemName2;
              echo $name2;
        }
        }   

    }

    }
?>
share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

There are a couple of things wrong with your code, but we'll start with your second statement.

$stmt = $conn->prepare("SELECT userId FROM userItems WHERE id=:id");

foreach ($moodItems as $id)
{
    // bind the parameters
    $stmt->bindValue(":id", $id);    

    // initialise an array for the results 
    if($stmt->execute()) {
        $itemName2 = $row['itemName'];
        $name2 = $row['userId'];

        echo $itemName2;
        echo $name2;
    }
}

You are trying to pull out $row['itemName'] and $row['userId'], however, you never SELECT the itemName column in that query. So if you want that information, you'll have to select it first. Aside from that, you execute the query, but you never fetch the row with information.

Those are the basics of why the second portion will not work how you want it to.

Now, for the bigger picture. Most of the block of code you provide is a nested if for your first $stmt->execute() and then within a while($row = ... Which, by itself is fine. However, later within the same block, you prepare another statement, which is perfectly acceptable, but you assign it to the same $stmt variable that you are using for the loop in the first place, which will also cause you problems. You'll want to assign a new variable for your second prepared statement, so you can work with the new data-set. Also, going back to the previous block I posted in, you'll want to fetch it to a variable that is not $row, as that is also a used variable.

share|improve this answer
 
thanks for the detailed answer... I have tried this code but still no luck, any idea where my syntax went wrong?? code $stmt2 = $conn->prepare("SELECT * FROM userItems WHERE id=:id"); foreach ($moodItems as $id) { // bind the parameters $stmt2->bindValue(":id", $id); // initialise an array for the results if($result = $stmt2->fetch(PDO::FETCH_ASSOC)) { $itemName2 = $result['itemName']; $name2 = $result['userId']; echo $itemName2; echo $name2; } } –  JustAnotherDude Oct 13 '13 at 14:50
 
From what I can see, it looks like you need to $stmt2->execute before you use $stmt2->fetch, otherwise your query hasn't been ran after you bind the parameter. –  Jon Oct 13 '13 at 19:50
add comment
up vote 0 down vote

UPDATE: As pointed out by Jon, my answer applies to Zend_Db_Adapter and not to PDOStatement (which you are using). My apologies.

The problem probably is that you're doing $stmt->execute() on the second SQL query. execute() does not return results, what you want to do is to fetch(), which does return results.

share|improve this answer
 
Also, your first query is currently being run twice, because execute() runs it once, and fetch() runs it a second time. –  Hamza Kubba Oct 13 '13 at 0:20
 
You don't want to use fetch() in a loop either... you want to do something like $results = $stmt->fetch(); once, then work with $results. fetch() executes a query and returns multiple rows, not a single row. –  Hamza Kubba Oct 13 '13 at 0:21
3  
First, you should edit your answer rather than adding multiple comments to it. Secondly, you should research your own answer to make sure you are providing valid information. PDOStatement::fetch() returns the next row, it does not execute the query. –  Jon Oct 13 '13 at 0:33
 
Thanks Jon. I was confusing PDOStatement::fetch() with Zend_Db_Adapter which I am very familiar with, where fetch() functions execute the query. I was not aware that PDOStatement worked differently, my apologies. –  Hamza Kubba Oct 14 '13 at 17:25
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.