Need help displaying things correctly.

[catlover]

Hello,

I'm new to PHP and have a little problem with a script. I hope someone will be kind enough to point me in the right direction to fix what I've scripted. If so, thank you.

I need the script to display breeds of cats and how many articles (or links to articles) have been submitted about that breed into my database.

For example:

[b]Bengal (1)
Burmese (3)
Persian (2)
Siamese (2)[/b]


What the script is currently returning is :

[b]Bengal (8)
Burmese (8)
Burmese (8)
Burmese (8)
Persian (8)
Persian (8)
Siamese (8)
Siamese (8)[/b]

I need the script to return only one instance of each breed and only the number of articles for that breed.

Here is the script:

[code]<table class="text1" width="100%" border="0" cellpadding="2" cellspacing="0"> <?

$result=mysql_query("SELECT breed FROM animals WHERE animals.breed = animals.breed ORDER BY breed")or die( "Error! Could not select the database: " . mysql_error() );
$num_rows = mysql_num_rows($result);
while ($category = mysql_fetch_array($result))  { ?>

<tr>
<td width="11"><img src="images/down_arrow.jpg" width="10" height="10"></td>
<td width="100%"><a href="<?=$path;?><?=$category['breed'];?>.php">
<? echo ($category['breed']);?></a>
<a href="<?=$path;?><?=$category['breed'];?>.php">
(<? echo "$num_rows";?>)
<?}?>[/code]

Thank you for your time!

[firedrop84]

I think you should use GROUP BY in your SQL query

[catlover]

GROUP BY returns:

[b]Bengal (4)
Burmese (4)
Persian (4)
Siamese (4)[/b]

That fixed the duplicate breed names. Thank you.

Any idea about how to sort the results of the articles instead of it counting the breeds?

[catlover]

I'm not familiar with how Invision boards work, so I didn't know the reply was "meshing" with the last reply.

Does anyone know how to sort how many "articles" are in a particular "breed" based on the previous code change (thanks to Firedrop for the first problem!)?

All the script needs now is the sorting of "articles" pertaining to the particular "breed".

I've fooled with quite a few queries, arrays, etc, but I don't "get it".

[sambib]

[!--quoteo(post=359861:date=Mar 30 2006, 10:44 AM:name=catlover)--][div class=\'quotetop\']QUOTE(catlover @ Mar 30 2006, 10:44 AM) [snapback]359861[/snapback][/div][div class=\'quotemain\'][!--quotec--]
I'm not familiar with how Invision boards work, so I didn't know the reply was "meshing" with the last reply.

Does anyone know how to sort how many "articles" are in a particular "breed" based on the previous code change (thanks to Firedrop for the first problem!)?

All the script needs now is the sorting of "articles" pertaining to the particular "breed".

I've fooled with quite a few queries, arrays, etc, but I don't "get it".
[/quote]

only guessing but--->

$breed = animals['breed'];
$num = count(animals['breed']);

echo '<p>$breed ($num)</p>';

[firedrop84]

I think u should use COUNT function in your SQL. for exmaple SELECT COUNT(breed) FROM ...

check out this link for more details of COUNT function

[a href=\"http://www.techonthenet.com/sql/count.php\" target=\"_blank\"]http://www.techonthenet.com/sql/count.php[/a]


Regards,
Ahmed

[catlover]

Thanks every one for the help!

I've added a second query using COUNT. I used "mysql_fetch_array" again.

COUNT seems to work, but it's only showing the count for the last "breed".

[b]Bengal
Burmese
Persian
Siamese (2)[/b]

Any idea on how to make it return the COUNT for all "breeds"?

[LordStanley]

$result=mysql_query("SELECT breed FROM animals WHERE animals.breed = animals.breed ORDER BY breed")or die( "Error! Could not select the database: " . mysql_error() );

that query doesnt make sense

where animals.breed=animals.breed will ALWAYS Be true. and you dont even need the animals. because your only selecting from one database

heres a logical way of doing what you need

[code]<table class="text1" width="100%" border="0" cellpadding="2" cellspacing="0"> <?

$result=mysql_query("SELECT breed, count(breed) as num FROM animals ORDER BY breed")or die( "Error! Could not select the database: " . mysql_error() );
while ($category = mysql_fetch_array($result))  {

echo '<tr>
<td width="11"><img src="images/down_arrow.jpg" width="10" height="10"></td>
<td width="100%"><a href="'.$path.$category['breed'].'.php">'.$category['breed'].'</a>
<a href="'.$path.$category['breed'].'.php">
( '.$category['num'].')';
}[/code]

thats not tested, but it should work...

try that

[catlover]

LordStanley,

Thanks for your help!

I had to change ORDER BY to GROUP BY, but it now works!

Thank you!


[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]Error! Could not select the database: Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause[/quote]