Stupid Query-thinking?

[knoak]

Hi there,

I'm building a website about animals.
There is a mySQL DB behind it.
There are 2 tables: 1 - species
2 - animals

Species could be: birds, fishes etc.
Animals would be: hawk, eagle etc, and orca, whale etc.
So every animal belongs to one of the species.

Anyway, there is an admin part to delete or edit animals or entire
species.

You get an overview of all the species, and behin it is a number of
how much animals there are in it.

I calculated them as following:

(Query to get species){
$species_name=$ query['name'];
$count_animals = mysql_query("SE LECT COUNT(*) FROM animals WHERE
sort_of_species =$species_name" );
$total_animals_ in_species = mysql_result($c ount_total_item s, 0);
echo $species_name." amount=".$total _animals_in_spe cies;
}

To make a long story short, for each positive result in the
"get-species-query" i run another one to count the animals inside.

is this heavy for the server/DB, is there a better way to do this?

Sorry for the long story, i hope it's clear...

Thanks in advance.

greetings knoakske

[John Bokma]

Re: Stupid Query-thinking?

knoak wrote:
[color=blue]
> Hi there,
>
> I'm building a website about animals.
> There is a mySQL DB behind it.
> There are 2 tables: 1 - species
> 2 - animals
>
> Species could be: birds, fishes etc.
> Animals would be: hawk, eagle etc, and orca, whale etc.
> So every animal belongs to one of the species.
>
> Anyway, there is an admin part to delete or edit animals or entire
> species.
>
> You get an overview of all the species, and behin it is a number of
> how much animals there are in it.
>
> I calculated them as following:
>
> (Query to get species){
> $species_name=$ query['name'];
> $count_animals = mysql_query("SE LECT COUNT(*) FROM animals WHERE
> sort_of_species =$species_name" );
> $total_animals_ in_species = mysql_result($c ount_total_item s, 0);
> echo $species_name." amount=".$total _animals_in_spe cies;
> }
>
> To make a long story short, for each positive result in the
> "get-species-query" i run another one to count the animals inside.
>
> is this heavy for the server/DB, is there a better way to do this?[/color]

sort_of_species should be an index. (Probably using a numeric ID is
faster than a varchar).
[color=blue]
> Sorry for the long story, i hope it's clear...[/color]

MySQL? Use explain your query here

read documentation on explain.

--
John MexIT: http://johnbokma.com/mexit/
personal page: http://johnbokma.com/
Experienced programmer available: http://castleamber.com/
Happy Customers: http://castleamber.com/testimonials.html

[Jan Pieter Kunst]

Re: Stupid Query-thinking?

knoak wrote:[color=blue]
> Hi there,
>
> I'm building a website about animals.
> There is a mySQL DB behind it.
> There are 2 tables: 1 - species
> 2 - animals
>
> Species could be: birds, fishes etc.
> Animals would be: hawk, eagle etc, and orca, whale etc.
> So every animal belongs to one of the species.
>
> Anyway, there is an admin part to delete or edit animals or entire
> species.
>
> You get an overview of all the species, and behin it is a number of
> how much animals there are in it.[/color]

assuming the following tables in the db:

mysql> select * from species;
+------------+---------+
| species_id | species |
+------------+---------+
| 1 | birds |
| 2 | fishes |
+------------+---------+

mysql> select * from animal;
+-----------+--------+------------+
| animal_id | animal | species_id |
+-----------+--------+------------+
| 1 | hawk | 1 |
| 2 | eagle | 1 |
| 3 | orca | 2 |
| 4 | whale | 2 |
+-----------+--------+------------+


You can get your numbers in one pass with this query:

mysql> select species, count(animal.sp ecies_id) as 'number of animals'
from species, animal where species.species _id=animal.spec ies_id group by
species;
+---------+-------------------+
| species | number of animals |
+---------+-------------------+
| birds | 2 |
| fishes | 2 |
+---------+-------------------+

HTH,
JP

--
Sorry, <devnull@cauce. org> is a spam trap.
Real e-mail address unavailable. 5000+ spams per month.

[Pedro Graca]

Re: Stupid Query-thinking?

knoak wrote:[color=blue]
> I'm building a website about animals.
> There is a mySQL DB behind it.
> There are 2 tables: 1 - species
> 2 - animals
>
> Species could be: birds, fishes etc.
> Animals would be: hawk, eagle etc, and orca, whale etc.[/color]

SCNR ... orca and whale are fish? :-)
[color=blue]
> So every animal belongs to one of the species.
>
> Anyway, there is an admin part to delete or edit animals or entire
> species.
>
> You get an overview of all the species, and behin it is a number of
> how much animals there are in it.[/color]

It all depends on how your tables are defined.

With these definitions:

mysql> desc species;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| name | varchar(20) | | | | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> desc animal;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| name | varchar(20) | | | | |
| species_id | int(11) | | | | |
+------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

I'd first get only the species' names and how many animals each has:

mysql> select s.name, count(a.id)
-> from species s left join animal a on a.species_id=s. id
-> group by s.name;
+--------+-------------+
| name | count(a.id) |
+--------+-------------+
| bird | 2 |
| fish | 0 |
| mammal | 2 |
+--------+-------------+
3 rows in set (0.00 sec)

and show that on the first selection. When the admin selects a species,
get its animals:

mysql> select a.name
-> from animal a, species s
-> where species_id=s.id
-> and s.name='mammal' ; -- or s.id=3
+-------+
| name |
+-------+
| orca |
| whale |
+-------+
2 rows in set (0.00 sec)

[color=blue]
> is this heavy for the server/DB, is there a better way to do this?[/color]

As much as possible each query should ask exactly what you want and no
more. Always try to do a maximum of one single mysql_query() call per
php page.

Page 1: species -> get the species' names and number of animals in *one*
query

Page 2: animals from a species (passed in $_GET array) -> get just the
animals from the specified species.

--
Mail to my "From:" address is readable by all at http://www.dodgeit.com/
== ** ## !! ------------------------------------------------ !! ## ** ==
TEXT-ONLY mail to the whole "Reply-To:" address ("My Name" <my@address>)
may bypass my spam filter. If it does, I may reply from another address!

[Henk Verhoeven]

Re: Stupid Query-thinking?

Hi knoak,

This question gets only interesting if species contains a hierachy,
like:
animals
birds
birds of pray
hawks
eagles
fishes
mamals
sea mamals
orca's
whales

The hierarchy uses a recursive foreign key equal to the key of the more
generic species group (which is in the same table as the more specific
ones).

Suppose the system is used by a field biologist, there may be another
table holding the numbers of the rings and tags, together with some
descriptive data like date of tagging, gender, coloring, size etc., and
of course a foreign key equal to the key of the most exact species the
tagged animal belongs to. Now we want to know the number of a higer
species group (for example birds) that where tagged after a certain
date. The code should work for a hierarchy of species of arbitrary depth
and any species (group). You can use any combination of SQL and php
code, but it should still perform reasonbly on a single processor off
the shelve server for about 10.000 species in an up to 10 deep
hierarchy. You can add some recursion into the data structure, but not
store the total number of tags per species group in the database.

Have fun!


Henk Verhoeven,
MetaClass.


knoak wrote:[color=blue]
> Hi there,
>
> I'm building a website about animals.
> There is a mySQL DB behind it.
> There are 2 tables: 1 - species
> 2 - animals
>
> Species could be: birds, fishes etc.
> Animals would be: hawk, eagle etc, and orca, whale etc.
> So every animal belongs to one of the species.
>
> Anyway, there is an admin part to delete or edit animals or entire
> species.
>
> You get an overview of all the species, and behin it is a number of
> how much animals there are in it.
>
> I calculated them as following:
>
> (Query to get species){
> $species_name=$ query['name'];
> $count_animals = mysql_query("SE LECT COUNT(*) FROM animals WHERE
> sort_of_species =$species_name" );
> $total_animals_ in_species = mysql_result($c ount_total_item s, 0);
> echo $species_name." amount=".$total _animals_in_spe cies;
> }
>
> To make a long story short, for each positive result in the
> "get-species-query" i run another one to count the animals inside.
>
> is this heavy for the server/DB, is there a better way to do this?
>
> Sorry for the long story, i hope it's clear...
>
> Thanks in advance.
>
> greetings knoakske[/color]