Multiples Queries inside Loop not all work

Laramischa · June 7, 2013, 12:50am

I try to optimize a script I have so that it loads faster.
Now I have been sitting here for hours already but do not get it to work correctly.

The first part works fine, but what does not work is getting the names of the sire_id ($sq query) and dam_id (($ dq query)
it shows as resuls “array” for all of them.

This is what I have written so far
[php]
$query = “SELECT COUNT(sire_id) as c, sire_id FROM dog WHERE sire_id IS NOT NULL GROUP BY sire_id ORDER BY c desc LIMIT 150”;
$result = mysql_query($query) ;

					$i=0;
				
					while ($line = mysql_fetch_array($result)) {
						
			$sireQuery = "SELECT name, id, sire_id, dam_id FROM dog WHERE id = '$line[sire_id] '";
			$sireResult = mysql_query($sireQuery) ;
			$sireLine = mysql_fetch_array($sireResult);
			$sr = mysql_query("SELECT name FROM dog WHERE id like $sireLine[2]") ;
			$sl = mysql_fetch_array($sr);		
			$dr = mysql_query("SELECT name FROM dog WHERE id like $sireLine[3]") ;
			$dl = mysql_fetch_array($sr);						

				$i++;
						
				echo "</td><td>".$i. '&nbsp;', '&nbsp;', '&nbsp;',"<a href='details.php?id=".$line[1]."'>"  .$sireLine[0]. "</td>
				<td>".$line[0]." </td>
				<td>".$sl." </td>
				<td>".$dl." </td></tr>";

}[/php]

richei · June 8, 2013, 2:32am

Before this redone, what is it you’re trying to do with this code?

Laramischa · June 8, 2013, 3:20am

This code is one of the statistics I show on my Website for the public which is (what you probably see already) a dog pedigree database
This statistic show the males which have the most offspring produced. What I after this will do with the script is that the list also can be asked up per country.
Next to this from the most used males I will have the same for the females with most offspring, but that is simply replacing the sire_id into the dam_id
On top of this statistic page is shown how many males/females in total are used for breeding.

Right now how the script is written it loads very slow (it get written to a table in my SQL database where it gets the data from)
So with improving it by taking the data just straight out of my dog database (which I made IMO a nice start with) I hope to have it load faster, I do understand that multiple queries in my loop is not very smart.

Here is a screen shot from what the page does:

Well sire (father)/dam (mother) of the dogs in that list do not show right now there I do not get that to work but that should come next to the dogs who is the father and mother

richei · June 8, 2013, 4:10am

Do you have multiple entries in the same table for each dog? and what’s the table structure?

Laramischa · June 8, 2013, 4:17am

This is my table “dog”

– Table structure for table `dog`

CREATE TABLE IF NOT EXISTS dog (
id int(20) NOT NULL auto_increment,
reg_nr varchar(80) collate utf8_unicode_ci default NULL,
name varchar(80) collate utf8_unicode_ci NOT NULL,
postTitle varchar(80) collate utf8_unicode_ci default NULL,
HipList varchar(80) collate utf8_unicode_ci default NULL,
sex enum(‘male’,‘female’) collate utf8_unicode_ci default NULL,
yearofbirth smallint(4) default NULL,
monthofbirth tinyint(4) default NULL,
dayofbirth tinyint(4) default NULL,
yearofdeath smallint(4) default NULL,
monthofdeath tinyint(4) default NULL,
dayofdeath tinyint(4) default NULL,
landofbirth varchar(80) collate utf8_unicode_ci default NULL,
landofstanding varchar(80) collate utf8_unicode_ci default NULL,
size varchar(80) collate utf8_unicode_ci default NULL,
weight float default NULL,
callName varchar(80) collate utf8_unicode_ci default NULL,
coi double default NULL,
source varchar(80) collate utf8_unicode_ci default NULL,
title varchar(512) collate utf8_unicode_ci default NULL,
Zuchtschau varchar(80) collate utf8_unicode_ci default NULL,
sire_id int(20) default NULL,
dam_id int(20) default NULL,
notes varchar(2048) collate utf8_unicode_ci default NULL,
changed timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
creator int(10) default NULL,
last_modifier int(10) default NULL,
eyeTest varchar(80) collate utf8_unicode_ci default NULL,
elbows varchar(80) collate utf8_unicode_ci default NULL,
Thyroid varchar(80) collate utf8_unicode_ci default NULL,
dentition varchar(80) collate utf8_unicode_ci default NULL,
heartTest varchar(80) collate utf8_unicode_ci default NULL,
causeDeath varchar(80) collate utf8_unicode_ci default NULL,
hiddenNotes varchar(2048) collate utf8_unicode_ci default NULL,
LonghairCarrier enum(‘YES’,‘NO’) collate utf8_unicode_ci default NULL,
hipText varchar(80) collate utf8_unicode_ci default NULL,
Hunting varchar(2048) collate utf8_unicode_ci default NULL,
Wesenstest varchar(80) collate utf8_unicode_ci default NULL,
Variety enum(‘Shorthair’,‘Longhair’) collate utf8_unicode_ci default NULL,
PRIMARY KEY (id),
KEY sire_id (sire_id),
KEY dam_id (dam_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=179839 ;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /;
/!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /;
/!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

This is my table “topsires”

– Structure for view `TopSires`

CREATE ALGORITHM=UNDEFINED DEFINER=weimaran@localhost SQL SECURITY DEFINER VIEW TopSires AS select count(0) AS Anzahl,a.sire_id AS sire_id from dog a where (a.sire_id is not null) group by a.sire_id order by count(0) desc limit 150;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /;
/!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /;
/!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

And last table “topsires1”

– Structure for view `TopSires1`

CREATE ALGORITHM=UNDEFINED DEFINER=weimaran@localhost SQL SECURITY DEFINER VIEW TopSires1 AS select TopSires.Anzahl AS Anzahl,TopSires.sire_id AS topd_id,dog.name AS name,dog.sire_id AS topsire_id,dog.dam_id AS topdam_id from (TopSires join dog) where (TopSires.sire_id = dog.id);

–
– VIEW `TopSires1`
– Data: None

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /;
/!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /;
/!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

And basically I want to get rid of the tables topsires.
I have also those tables from top dams.
While IMO it is unnecessary those tables there you can get the info straight out of my dog database

richei · June 8, 2013, 4:59am

So basically, you have 2 tables, 1 is the dog’s info and a table of just the dog’s id? i’m trying to figure out where you get the sire and dame id.

Laramischa · June 8, 2013, 5:04am

For the dog ID and the dog info that is just one table.
The second table is info inserted from the dog info (automatically) and that is right now what I load and do not want that way.

In my dog table is basically all needed info from the dogs, so ID and also Sire and Dam ID.
-> id int(20) NOT NULL auto_increment,
-> sire_id int(20) default NULL,
-> dam_id int(20) default NULL,

Laramischa · June 8, 2013, 5:06am

So my startic topic the script is what I so far have build to get it all out of the dog info and can delete the “TopSires” table

Laramischa · June 8, 2013, 5:20am

So what I want to achieve is that it “counts” the Sire ID, orders them by the Sire ID (descending)
Than I have the males which gave the highest amount of puppies first.

What than has to be done is getting the name of that sire ID and from that dog again his sire_id and dam_id to show his parents
which is my first query within the “while” loop.
And THAN what has to happen is that it gets the names again from the parents of that dog. Which I tried to do with the 2d and 3d query in the while loop (this part does not work)

And of course it is that the sire ID (and also dam ID) are links to the “ID” of each dog

richei · June 8, 2013, 5:29am

Does that table with the sire/dame ids have multiple entries for the same dogs or is it just a lookup table? i’m trying to get a sense of how the information is stored for each dog because it seems rather unorganized.

Laramischa · June 8, 2013, 5:41am

That table for the sires / dams I mentioned is just a “live” table (do not know how to call it else)
Where the info of the 150 most used sires right now automatically gets stored on the moment offspring of dogs get added in the database
So what it does is taking that sire with 460 Offspring and puts that in that table.

(So this is where I want to get rid of)
Which (as you can see) is not in my script I so far wrote anymore

That dog and its info is still in the “dog” table as well so it is double up the info stored.

And to get the amount of puppies and the numbering of their place in the top used can be done with queries too

So you only have to look at my “dog” structure/table that is where I want to get straight away all the info from.

Multiples Queries inside Loop not all work

– Table structure for table dog

– Structure for view TopSires

– Structure for view TopSires1

– – VIEW TopSires1 – Data: None

– Table structure for table `dog`

– Structure for view `TopSires`

– Structure for view `TopSires1`

–
– VIEW `TopSires1`
– Data: None