Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

PHP

Posted by Boris Kamp
Boris Kamp
16,660 Points

while loop to fetch mysql data

Hi all.

Im have a database with two tables in it from where i'd like to retrieve data. The first table customerinfo contains 17 columns with customer info and I stored all the data in an array using this code:

try {
    $results = $db->prepare("SELECT * FROM persoonsinfo WHERE id = ?");
    $results->bindParam(1,$_SESSION["filemaker_id"]);
    $results->execute();

} catch (Exception $e) {
    echo "data could not be retrieved";
    exit;
}
$persoonsgegevens = $results->fetch(PDO::FETCH_ASSOC);

This works perfect and I can echo the data using echo $persoonsgegevens['firstname']; etcetera.

Now for example, each customer has orders. therefore I created the 2nd table called orders in the orders table is a column that containers the customerID to match the orders to the customers. I use this to only retrieve the orders for the specified customer (bindParam):

$persoonsgegevens["uitkeringen"] = array();

try {
    $results = $db->prepare("SELECT * FROM uitkeringen WHERE contactid = ?");
    $results->bindParam(1,$_SESSION["filemaker_id"]);
    $results->execute();

} catch (Exception $e) {
    echo "data could not be retrieved";
    exit;
}
while ($row = $results->fetch(PDO::FETCH_ASSOC)) {
        $persoonsgegevens["uitkeringen"][] = $row["uitkering"];
    }

    return $persoonsgegevens;

However, I only get the first matched result, it does not loop trough all the matched rows as I want it to. What am I missing?

I use this in my Wordpress Template trying to echo all the orders:

<?php foreach($persoonsgegevens["uitkeringen"] as $uitkering) { ?>
    <p><?php echo $uitkering['id']; ?> </p>
<?php } ?>

But it echoes nothing.

Can somebody correct me here and show me what I've done wrong?

Thanks!

Michael De Marre
Michael De Marre
14,198 Points

I don't see any syntax errors. My guess is that some column or key names in your database don't match up. Are you sure that $persoonsgegevens["uitkeringen"] has an 'id' key?

Boris Kamp
Boris Kamp
16,660 Points

Thank you for giving your feedback Michael! That's what I was thinking, but I double checked everything and I could not find any typo or something. You saying Are you sure that $persoonsgegevens["uitkeringen"] has an 'id' key? got me doing a var_dump($persoonsgegevens); and it turns out nothing is in it somehow....

Im sure the try-catch block part is correct as I can perform this

$uitkeringen = $results->fetch(PDO::FETCH_ASSOC);
var_dump($uitkeringen);

between the try-catch block and the while loop, but it only echoes one row like this:

array(4) { ["id"]=> string(1) "1" ["contactid"]=> string(1) "1" ["datum"]=> string(8) "1/1/2013" ["bedrag"]=> string(5) "1.133" }

instead of all like it should with the while loop.

Does this help you in any way? If you need more info please ask and I'll be glad to answer! Thanks!

2 Answers

Hugo Leon
Hugo Leon
7,763 Points

Hello Boris!

The line with the while loop has this argument:

$row = $results->fetch(PDO::FETCH_ASSOC)

. If you want to retrieve more than one row, you should use the method fetchAll to do that. Because the method fetch only fetches the next row from the result set (just one).

Hope it helps!.

Boris Kamp
Boris Kamp
16,660 Points

Hi Hugo!

That doesn't seem to do the trick for me either, I changed the code to this:

$persoonsgegevens["uitkeringen"] = array();

try {
    $results = $db->prepare("SELECT * FROM uitkeringen WHERE contactid = ?");
    $results->bindParam(1,$_SESSION["filemaker_id"]);
    $results->execute();

} catch (Exception $e) {
    echo "data could not be retrieved";
    exit;
}

$uitkeringen = $results->fetchAll(PDO::FETCH_ASSOC);
var_dump($uitkeringen);

while ($row = $results->fetchAll(PDO::FETCH_ASSOC)) {
        $persoonsgegevens["uitkeringen"][] = $row["uitkering"];
    }

    return $persoonsgegevens;
    var_dump($persoonsgegevens);

The var_dump($uitkeringen); dumps all the $uitkeringen on the front end, so not just one! The var_dump($persoonsgegevens); returns empty again, nothing shown....

So before the while loop when I do the test, al the uitkeringen are stored in the variable $uitkeringen.after the while loop, they are not stored in the $persoonsgegevens["uitkeringen"] somehow

After some figuring out knowing all the uiterkeringen were in the $uitkeringen variable, I tried this in my php page:

<?php foreach($uitkeringen as $uitkering) { ?>
   <tr><td><?php echo $uitkering['datum']; ?></td><td><?php echo $uitkering['bedrag']; ?></tr>
<?php } ?>

and guess what, it works!? so after all it seems I did not even neet the while loop, leaving me with this code request to the database:

try {
    $results = $db->prepare("SELECT * FROM uitkeringen WHERE contactid = ?");
    $results->bindParam(1,$_SESSION["filemaker_id"]);
    $results->execute();

} catch (Exception $e) {
    echo "data could not be retrieved";
    exit;
}
$uitkeringen = $results->fetchAll(PDO::FETCH_ASSOC);

This works perfectly, is it the best and cleanest way as well?

Thanks! you put me on the right track!

Michael De Marre
Michael De Marre
14,198 Points
Michael De Marre
Michael De Marre
14,198 Points

Hey Boris,

I think I've figured it out. You need to do an inner join between the two databases in order to fetch the customer info for the order on the second try block.

Michael De Marre
Michael De Marre
14,198 Points 
try {
    $results = $db->prepare("SELECT * FROM uitkeringen INNER JOIN persoonsinfo ON uitkeringen.id = persoonsinfo.id");
    $results->bindParam(1,$_SESSION["filemaker_id"]);
    $results->execute();

} catch (Exception $e) {
    echo "data could not be retrieved";
    exit;

You may need to modify it a bit depending on the column names in your tables.

http://www.w3schools.com/sql/sql_join_inner.asp

Boris Kamp
Boris Kamp
16,660 Points

Thanks Michael! really appreciate your help, I'll try this out tomorrow when I have the time and keep you posted on the result!

Boris Kamp
Boris Kamp
16,660 Points

I tried this code:

$persoonsgegevens["uitkeringen"] = array();

try {
    $results = $db->prepare("SELECT * FROM uitkeringen INNER JOIN persoonsinfo ON uitkeringen.contactid = persoonsinfo.id");
    $results->bindParam(1,$_SESSION["filemaker_id"]);
    $results->execute();

} catch (Exception $e) {
    echo "data could not be retrieved";
    exit;
}
while ($row = $results->fetch(PDO::FETCH_ASSOC)) {
        $persoonsgegevens["uitkeringen"][] = $row["uitkering"];
    }

    return $persoonsgegevens;
    var_dump($persoonsgegevens["uitkeringen"]);

but the var_dump at the end still turns out empty. The where clause is missing too. Why would I need to do an INNER JOIN in order to use the where loop? Thanks!