php foreach() w/ MySQL WHERE w/ while{} returning only the last array() item

Question

My result is spitting out a duplicate of each record as opposed to a unique foreach match. As you can see I am trying to loop through and get each unique value WHERE $s=id.. I know the query needs to be within the while(),

The array $seminar being passed from checkboxes on my form.

if ($seminar) {
    foreach ($seminar as $s)
        $interest .= "$s ";
}

NOTE: The $interest is what I am using to insert into my table (it's not part of this problem)

For array data, this is what is being returned by $seminar

Array
(
    [0] => 1
    [1] => 8
    [2] => 9
    [3] => 10
    [4] => 13
)

Here is my code:

$query_seminars = mysql_query("SELECT id, name, UNIX_TIMESTAMP(moment) FROM seminars WHERE id={$s}");
    while ($sem_row = mysql_fetch_assoc($query_seminars))
    {
        $names .= "-- " . $sem_row['id'] . " " . $sem_row['name'] . date('D, F j, Y, g:i a',     $sem_row['moment']) . "<br />";

}

You mix up the variables $sem_row and $seminar - your PHP should throw a lot of warnings if you set the appropriate error reporting. Just kick the foreach and you will be happy. — BurninLeo
– BurninLeo, Commented Jul 6, 2011 at 23:16
BurninLeo, its still not working. I can produce the array. I updated my above code to show exactly what I am working with now.. I can only get the last item of the array() (in the above case 13) record to print out. — OldWest
– OldWest, Commented Jul 7, 2011 at 17:15

Brian · Accepted Answer · 2011-07-06 23:21:22Z

5

You do not need the foreach in the while loop, here:

$query_seminars = mysql_query("SELECT id, name, UNIX_TIMESTAMP(moment) FROM seminars WHERE id={$s}");
while ($sem_row = mysql_fetch_assoc($query_seminars))
{
    $names .= "-- " . $sem_row['id'] . " " . $sem_row['name'] . date('D, F j, Y,     g:i a', $sem_row['moment']) . "<br />";
}

Also to use named indexes you use mysql_fetch_assoc, not mysql_fetch_array. (Already in above code)

edited Jul 6, 2011 at 23:21

answered Jul 6, 2011 at 23:16

Brian

13.6k13 gold badges62 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

OldWest Over a year ago

This is the exact problem I am running into. I was able to produce this result earlier. I am only getting that last item on the $s array, so only one record is printing out. I am updating my code above to show how my array is being generated.

OldWest Over a year ago

I meant $seminar array not $s array.

Brian Over a year ago

@OldWest that's because you have WHERE id={$s}. That's limiting your result set to 1 record, remove that bit of code and all records will appear.

Branco Medeiros · Accepted Answer · 2011-07-07 00:25:44Z

The problem is not that MySql is returning duplicate (actualy tri-plicate) items. The problem is that you are iterating over each record with your foreach, as others pointed out.

One usual idiom used with MySql is:

$sql = "SELECT id, name, UNIX_TIMESTAMP(moment) FROM seminars WHERE $s=id";

if ($q = mysql_query($sql)) {
  while ($f = mysql_fetch_assoc($q)) {
    //($f is an associative array of the record value, 
    //with keys for each field)
    //... do something with $f
    echo "--" . htmlspecialchars($f['name']) 
      . date('D, F j, Y, g:i a', $f['moment']) 
      . "<br /><br />";
  }
} elseif ($e = mysql_error()) {
//do something with the error message
//...
}

You are seeing "duplicates" because you are using a foreach inside the while loop to iterate by each field of the returned record. Since your query is returning records with three fields each (id, name and moment), you are processing each record three times.

Hi Branco, thanks for the comments, but this is still not working. I can only get the last item of my array() to print with my foreach. I updated my above code to show what I am doing now.

Collectives™ on Stack Overflow

php foreach() w/ MySQL WHERE w/ while{} returning only the last array() item

2 Answers 2

3 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Related