0

I have a loop that should display a summary of all MySQL results. It does this by determining how may results there will be and then loops that many times. So if there are 43 results that match my query, instead of displaying all 43 results, it only displays the first result 43 times. Please help! Here's my code:

if (empty($res)) {

echo 'No results found';

} else if($num>1){

    echo "<b>".$num_rows."<b> results found...<br>";

    while ($res = mysql_fetch_assoc($result)) {

    echo "<a href='#'>{$res['dealer']}</a><br>";

    }

} else {

echo "<table border=\"0\"><tr><td colspan=\"2\"><span class=\"dealer\">" . $res['dealer'] . "</span></td></tr><tr><td><span class=\"label\">Pin: </span><span class=\"inf\">" . $res['pin'] . "</span></td><td><span class=\"label\">OB CODE: </span><span class=\"ob\">" . $res['ob'] . "</span></td></tr><tr><td><span class=\"label\">Program Director:</span></td><td><span class=\"inf\">" . $res['contact'] . "</span></td></tr><tr><td valign=\"top\"><span class=\"label\">Address:</span></td><td><span class=\"inf\">" . $res['address'] . "<br>" . $res['city'] . ", " . $res['state'] . "<br>" . $res['zip'] . "</span></td></tr><tr><td><span class=\"label\">Dealer Phone:</span></td><td><span class=\"inf\">" . $res['phone'] . "</span></td></tr><tr><td><span class=\"label\">Codes Valid on:</span></td><td><span class=\"inf\">" . $res['website'] . "</span></td></tr></table>";

}

Thanks a lot in a advance!

cream
  • 1,019
  • 5
  • 14
  • 24
  • 2
    You should stop using `mysql_*` functions. They're being deprecated. Instead use [PDO](http://php.net/manual/en/book.pdo.php) (supported as of PHP 5.1) or [mysqli](http://php.net/manual/en/book.mysqli.php) (supported as of PHP 4.1). If you're not sure which one to use, [read this SO article](http://stackoverflow.com/questions/13569/mysqli-or-pdo-what-are-the-pros-and-cons). – Matt Aug 02 '12 at 20:30
  • Please format your code so it's readable – Madbreaks Aug 02 '12 at 20:30
  • No problem, it got a little funky trying to copy/paste it over. I had to mess with spacing to get it to properly post as code. – cream Aug 02 '12 at 21:05
  • Where is `$num_rows` defined? Think you mean `$num`. I'd suggest slooowly looking over your code to make sure you understand each step, etc. It seems like you're thrashing around a bit. No offense intended. – Madbreaks Aug 02 '12 at 21:34

4 Answers4

3

That's because you've only fetched the first row (when you did $res = mysql_fetch_assoc($result);). What you're trying to do is actually unnecessary: mysql_fetch_assoc() will automatically move the data pointer ahead every time it runs. What you can do instead (and is in fact common practice) is this:

//...
while ($res = mysql_fetch_assoc($result)) {
    echo "<a href='#'>{$res['dealer']}</a><br />";
}
//...

On a side note, the mysql_* functions are soon to be deprecated. You can use either mysqli or PDO instead; if you're a lazy bum with a lot of code to switch (like me), you can use the mysqli procedural functions - they're almost identical to the original mysql_* functions.

Palladium
  • 3,465
  • 3
  • 12
  • 19
  • i tried it like this with the curly braces but got and error: unexpected "{" – cream Aug 02 '12 at 21:26
  • @HiggsBoson That would most likely be because you inserted it wrongly. I can't guess at how you inserted it; you'll have to show me. – Palladium Aug 02 '12 at 21:30
  • wait, sorry, forgot to remove the quotes. it's still not working though. returns "no results found" every time. – cream Aug 02 '12 at 21:30
  • 1
    @HiggsBoson That very likely has to do with your `if(empty($res))` line, since you no longer have a `$res` for it to check the emptiness of anymore. – Palladium Aug 02 '12 at 21:31
  • thank you! why didn't i see that? okay, i changed it to if($num==0). now i get all the results if there is more than one, but if there is only one it returns the table, but doesnt fill in the variables from the database. do i need to put curlies on all of those as well? – cream Aug 02 '12 at 21:38
  • nevermind, just needed to define $res within the else{}! thank you so much for your help! – cream Aug 02 '12 at 21:41
  • @HiggsBoson Any time, my friend. Any time. Best of luck with the rest of the code. Also, as you may have heard: `mysql` is being deprecated. Switch to `PDO` or `mysqli` while you still have the chance. – Palladium Aug 02 '12 at 21:42
1

You should use

$res = mysql_fetch_assoc($result);

each time you want to retrieve a new row

Calvin Jia
  • 846
  • 5
  • 5
1

You have to do this inside your loop:

$res = mysql_fetch_assoc($result);

...as that only pulls the first result otherwise. Here's the correct way to do it:

while($res = mysql_fetch_assoc($result)){
    echo "<a href='#'>".$res['dealer']."</a><br>";
    i++;
}

Cheers

Madbreaks
  • 17,159
  • 7
  • 50
  • 64
  • so do i still need the $i < $num ? if so where does it go? doing it the way you posted is making it return "no results found" every time.. – cream Aug 02 '12 at 20:40
  • I'm happy to help further but please update your code first so I can see what you're working with. – Madbreaks Aug 02 '12 at 20:44
  • `if(empty($res))` -- that can't work, you haven't declared `$res` yet, right? How about `if($num == 0)` – Madbreaks Aug 02 '12 at 21:31
0

use:

while($res = mysql_fetch_assoc($result)) { echo $res['dealer']; }

arnoudhgz
  • 1,284
  • 9
  • 16