Why is only the first record being returned from this PHP array?

Question

I'm trying to return four records from my MySQL database but only the first record is being returned. I've searched but I'm unsure why this is. Can someone point me in the right direction?

    <?php
session_start();
function displayImage($username){
    $imageDate  = $_POST['imageDate'];
    $result = mysql_query("
        SELECT
            imageName
        FROM
            images AS i
        INNER JOIN
            users AS u ON i.userID = u.UserID
        WHERE
            u.username = '$username'
        AND
            i.imageDate = '$imageDate'

    ") or die(mysql_error());

    //return a message to the users explaining ......
        if (!isset($_POST['Submit'])) {
            // this does nowt yet!!!
            $output = "Nothing selected yet.";
        }
        else {
        //This is a while loop to store the SQL results into ......
        $row = array(mysql_fetch_assoc($result));
        foreach ($row as $picName) {
        $cam = $_POST['cam'];
        $fullPath = $username . "/" . $cam . "/" . $picName['imageName'];
        // $output = //this works fine
                reset($images);
            }
        }
        var_dump($row);
        echo "<br />";
        return $output;
    }
?>

Answer 1

You should use loop to fetch result:

    while ($row = mysql_fetch_assoc($result)) {
        $cam = $_POST['cam'];
        $fullPath = $username . "/" . $cam . "/" . $row['imageName'];
    }

Answer 2

As others here have said, you need to use a while loop for this, I've tidied the code up a tiny bit and added a couple of other things for you to consider.

The actual reason for this is that when you use mysql_fetch_assoc it brings back one result resource and removes it from resources it has left to return. So if you just try and store it in an array, you get the first one in the array, and nothing else. When you use a while loop it works by basically saying "if mysql_fetch_assoc has something to give, then do the code inside the loop".

<?php

session_start();

function displayImage($username) {
    // Also, as others have said, check out PDO, or MySQLi, as they
    // both provide a better interface with MySQL an have better
    // security features.
    $username  = mysql_real_escape_string($username);
    $imageDate = mysql_real_escape_string($_POST['imageDate']);

    $result = mysql_query("
        SELECT
            imageName
        FROM
            images AS i
        INNER JOIN
            users AS u ON i.userID = u.UserID
        WHERE
            u.username = '$username'
    AND 
            i.imageDate = '$imageDate'
    ") or die(mysql_error());

    //return a message to the users explaining
    if (!isset($_POST['Submit'])) {
        // this does nowt yet!!!
        $output = "Nothing selected yet.";
    } else {
        $cam = $_POST['cam'];

        $images = array(); // This is part of the "you could do something 
                           // like" further down.
        while ($row = mysql_fetch_assoc($result)) {
            $fullPath = $username . '/' . $cam . '/' . $row['imageName'];
            // $output = //this works fine
            var_dump($row);
            echo "<br />";

            // Or you could do something like:

            $images[] = $username . '/' . $cam . '/' . $row['imageName'];

            // Then outside of this while loop you'd have all of your image 
            // paths stored in this $images array. 
            // 
            // It depends on how you want to handle outputting them.
        }
    }

    return $output;
}

The comments in the code go through my main points.

I also moved that $cam = $_POST['cam'] from inside of the loop, to outside of it, as it doesn't need to go in the loop because it's value will always be the same, regardless of which loop item you're going over.

Answer 3

The way to use mysql_fetch_assoc or mysql_fetch_array is this:

while ($row = mysql_fetch_assoc($result)) {
    // This is an example...
    echo $row["id"];
    echo $row["name"];
}

Answer 4

If you still need an explanation:

1. Why you got only one record??

When you declare:

$row=array(mysql_fetch_assoc($result));

This means that you get one record from the database, the mysql_fetch_assoc() method was run only once, so your method calls 1 row from the database table.

2. How to get this to work?

Like you have seen in the example given by others, use a while loop instead.

2.1 How does the loop work?

When you call:

     while (<some command1 equates to true or false>) {<some command2>}

At first they run somecommand1. If it returns true, do some command2. After command2 has finished, run somecommand1 again. If it still true, do somecommand2 and so on... A nice example is the command you're using.

     while ($row = mysql_fetch_assoc($result))
     {
         echo $row['fieldname1'];
         echo $row['fieldname2'];
     }

In this loop $row = mysql_fetch_assoc($result) is Somecommand1 as explained. This function returns a resource which equates to true if there is another row returned from the database. After one run through of the code within the while loop it skips onto the next row. Then it re-runs the mysql_fetch_assoc function. If there is still some record in the table that matches the query, this function will still do whatever is in your while loop.

If you need more examples, take a look at w3schools.com. I started PHP from there.