Determine last result of sql statement

[Kay1021]

I was wondering if there was a way to tell the last result from an sql statement. Or maybe there is an easier way all together. Basically i have categories being outputted based on the sql statement. However after each one I wanted a comma....the problem with the code I have now (see below)...is that it also adds a comma to the very last one...that looks odd. I know mysql_num_rows tells you the total number of results....so i figured you could use that along with a counter....but i can't seem to work out the right code...either the comma remains or a i get weird results.... Any advice? Thanks

 

 

$sql2="SELECT * FROM category WHERE cat_name = '$curr_cat'";

			$qry3 = mysql_query($sql2);
			$total = mysql_num_rows($qry3);					


			if (mysql_num_rows($qry3) > 0) {
				while ($rs = mysql_fetch_assoc($qry3)) {

					$category= $rs['area'];
					print $category;

						if($total >1){
                                                             print ',  ';
                                                        }

				}
			}

 

 

the output right now for one results is 

 

Chocolate

 

 

for more than one result

 

Chocolate, Candy, Chips, Pop,

 

 

for more than one result I would like it to be:

 

Chocolate, Candy, Chips, Pop

 

[Ken2k7]

You don't have to. Let MySQL do it. It'll be easier on your part too!

$sql2 = "SELECT GROUP_CONCAT(area) AS listings FROM category WHERE cat_name = '$curr_cat' LIMIT 1";
$result = mysql_query($sql2) or die(mysql_error());
$result = mysql_fetch_assoc($result) or die(mysql_error());
echo $result['listings'];

 

Like it?

 

Well I guess it's not exactly like yours. MySQL doesn't add the space after the comma.

 

But you can use substr if you really want to use your method.

[Kay1021]

Thank you thank you! Way easier....i've never seen that stuff before....but it looks like it's good to know!

 

Thanks

[JD*]

You can also do the following:

 

$result = mysql_query("SELECT * FROM category WHERE cat_name = '$curr_cat'") or die(mysql_error());

for($i=0;$i < mysql_num_rows($result);$i++)
{
    echo mysql_result($result, $i, "area").($i + 1 < mysql_num_rows($result) ? ", " : "");
}

 

If I did it right, it should give you each area followed by a comma until it reaches the last one, where it will not.

[Kay1021]

Thank you that is also handy...i couldn't for the life of me remember how to do the loop

[Ken2k7]

JD*, you realize that you're making a lot of function calls to mysql_num_rows(), right? If that SQL were to return 100 rows, you'll be executing that function 200 times. And each time, it has to traverse an array of 100 elements. :-\

[Psycho]

Ken2k7's option is probably the most efficient, but just for the sake of learning here's another approach

 

<?php

$query = "SELECT * FROM category WHERE cat_name = '$curr_cat'";
$result = mysql_query($query) or die(mysql_error());

while($record = mysql_fetch_assoc($result))
{
    $catNames = $record['area'];
}
echo implode(', ', $catNames);

?>