Count number of displayed results

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
jarow
Forum Commoner
Posts: 83
Joined: Tue Jan 28, 2003 2:58 am

Count number of displayed results

Post by jarow »

this should be easy but still can´t figure it out. I have a database of animal species. Users can search by various criteria such as order, class, family, genera, etc. NOW what I want to do is count the number of genera and species that appear in the results of that search (of course it varies depending on what value they used to do the search)

The following is my search query. It works just fine and I have no problems with it.

Code: Select all

mysql_select_db($database_connFauna, $connFauna);
$query_rsspecies = sprintf("SELECT * FROM fauna WHERE phylum LIKE '%%%s%%' AND clase LIKE '%%%s%%' AND orden LIKE '%%%s%%' AND familia LIKE '%%%s%%' AND genero LIKE '%%%s%%'AND especie LIKE '%%%s%%' AND ('%s'!='' OR '%s'!='' OR '%s'!='' OR '%s'!='' OR '%s'!='' OR '%s'!='') ORDER BY phylum, clase, orden, familia, genero, especie ASC", $varphylum_rsspecies,$varclase_rsspecies,$varord_rsspecies,$varfam_rsspecies, $vargen_rsspecies, $vartax_rsspecies,$varclase_rsspecies,$varphylum_rsspecies,$varord_rsspecies,$varfam_rsspecies, $vargen_rsspecies,$vartax_rsspecies);
$rsspecies = mysql_query($query_rsspecies, $connFauna) or die(mysql_error());
$totalRows_rsspecies = mysql_num_rows($rsspecies);


Of course when I do this query I get all the genera in the database:

Code: Select all

mysql_select_db($database_connFauna, $connFauna);
$query_rsgenero = "SELECT DISTINCT genero FROM fauna";
$rsgenero = mysql_query($query_rsgenero, $connFauna) or die(mysql_error());
$row_rsgenero = mysql_fetch_assoc($rsgenero);
$totalRows_rsgenero = mysql_num_rows($rsgenero);


How can I combine or reference the two so that I am only getting the genera that result from the original search. Any suggestions would be much appreciated.

Jarow
[]InTeR[]
Forum Regular
Posts: 416
Joined: Thu Apr 24, 2003 6:51 am
Location: The Netherlands

Post by []InTeR[] »

SELECT DISTINCT genero FROM fauna WHERE phylum LIKE '%%%s%%' AND clase LIKE '%%%s%%' AND orden LIKE '%%%s%%' AND familia LIKE '%%%s%%' AND genero LIKE '%%%s%%'AND especie LIKE '%%%s%%' AND ('%s'!='' OR '%s'!='' OR '%s'!='' OR '%s'!='' OR '%s'!='' OR '%s'!='')

I think?
jarow
Forum Commoner
Posts: 83
Joined: Tue Jan 28, 2003 2:58 am

Post by jarow »

thanks...tried it and it didn´t work
[]InTeR[]
Forum Regular
Posts: 416
Joined: Thu Apr 24, 2003 6:51 am
Location: The Netherlands

Post by []InTeR[] »

Whats the error?
Tubbietoeter
Forum Contributor
Posts: 149
Joined: Fri Mar 14, 2003 2:41 am
Location: Germany

Re: Count number of displayed results

Post by Tubbietoeter »

Code: Select all

mysql_select_db($database_connFauna, $connFauna);
$query_rsspecies = sprintf("select distinct (*) from ( SELECT * FROM fauna WHERE phylum LIKE '%%%s%%' AND clase LIKE '%%%s%%' AND orden LIKE '%%%s%%' AND familia LIKE '%%%s%%' AND genero LIKE '%%%s%%'AND especie LIKE '%%%s%%' AND ('%s'!='' OR '%s'!='' OR '%s'!='' OR '%s'!='' OR '%s'!='' OR '%s'!='') ORDER BY phylum, clase, orden, familia, genero, especie ASC)",
jarow
Forum Commoner
Posts: 83
Joined: Tue Jan 28, 2003 2:58 am

Post by jarow »

Got it...InTeR I used your suggestion and it worked fine...must have had a comma or something out of place when I originally tried it.

Many thanks for your time

jarow
Post Reply