PHP only returning first MySQL result

Question

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!

You should stop using mysql_* functions. They're being deprecated. Instead use PDO (supported as of PHP 5.1) or mysqli (supported as of PHP 4.1). If you're not sure which one to use, read this SO article. — Matt
– Matt, Commented Aug 2, 2012 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
– cream, Commented Aug 2, 2012 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
– Madbreaks, Commented Aug 2, 2012 at 21:34

Palladium · Accepted Answer · 2012-08-02 20:33:39Z

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.

answered Aug 2, 2012 at 20:33

Palladium

3,7734 gold badges17 silver badges19 bronze badges

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

7 Comments

cream Over a year ago

i tried it like this with the curly braces but got and error: unexpected "{"

Palladium Over a year ago

@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.

cream Over a year ago

wait, sorry, forgot to remove the quotes. it's still not working though. returns "no results found" every time.

Palladium Over a year ago

@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.

cream Over a year ago

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?

|

Calvin Jia · Accepted Answer · 2012-08-02 20:31:14Z

1

You should use

$res = mysql_fetch_assoc($result);

each time you want to retrieve a new row

answered Aug 2, 2012 at 20:31

Calvin Jia

8665 silver badges5 bronze badges

Comments

Madbreaks · Accepted Answer · 2012-08-02 20:31:54Z

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

answered Aug 2, 2012 at 20:31

Madbreaks

19.6k7 gold badges62 silver badges75 bronze badges

3 Comments

cream Over a year ago

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..

Madbreaks Over a year ago

I'm happy to help further but please update your code first so I can see what you're working with.

Madbreaks Over a year ago

if(empty($res)) -- that can't work, you haven't declared $res yet, right? How about if($num == 0)

arnoudhgz · Accepted Answer · 2012-08-02 20:33:25Z

0

use:

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

answered Aug 2, 2012 at 20:33

arnoudhgz

1,2849 silver badges19 bronze badges

Collectives™ on Stack Overflow

PHP only returning first MySQL result

4 Answers 4

7 Comments

Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related