Page 1 of 1

Count number of displayed results

Posted: Wed Jul 02, 2003 5:38 am
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

Posted: Wed Jul 02, 2003 5:45 am
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?

Posted: Wed Jul 02, 2003 6:25 am
by jarow
thanks...tried it and it didn´t work

Posted: Wed Jul 02, 2003 7:01 am
by []InTeR[]
Whats the error?

Re: Count number of displayed results

Posted: Wed Jul 02, 2003 7:39 am
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)",

Posted: Thu Jul 03, 2003 4:18 am
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