1 row for each eval, but all the results in one line?

[ezekielnin]

Hi everyone,

 

I'm working on a database project where biology searchers will be able to store information on animals, from all kind of species.

This database contains a framework to store info on animals themselves but also on evaluations that they go thru.

 

Each evaluation has some basic info that you need to enter, but each species also have criterias that needs to be evaluated. I do this without problem with a dynamix php form. So this means I have a table for animals, evaluation, criterias, species_type and results.

 

Now I need to output a report, some kind of excel file, actually a CSV file. I have no problem generating the CSV file. But I have issues putting the information in the "right" way.

 

What I get is one row for each results of each criteria :

 

What I'd like to get is 1 row for each eval + all the results of the criterias evaluated  on one line:

 

My mysql version is 4.1.22.

 

My mysql query is :

Select
animal.Nom_Animal As 'Animal ID',
caract.Caract As 'Criteria',
resultat.Valeur As 'Result',
evaluation.Notes As 'Notes',
status.Status As 'Current status',
type_immigrant.Type_Immigrant As 'Emigrant type',
type_animal.Type_Animal As 'Species',
sexe.Sexe As 'Gender',
status1.Status As 'Status at eval',
  	status1.Code As 'Status code'
From
evaluation 
        Inner Join resultat On resultat.ID_Evaluation = evaluation.ID_Evaluation 
        Inner Join caract On resultat.ID_Caract = caract.ID_Caract 
        Inner Join animal On evaluation.ID_Animal = animal.ID_Animal 
        Inner Join type_animal On type_animal.ID_Type_Animal = animal.ID_Type_Animal 
        Inner Join  sexe On sexe.ID_Sexe = animal.ID_Sexe
Inner Join type_immigrant On type_immigrant.ID_Type_Immigrant = animal.ID_Type_Immigrant
        Inner Join status On status.ID_Status = animal.ID_Status
Inner Join status status1 On status1.ID_Status = evaluation.ID_Status
Where
animal.ID_Type_Animal LIKE '".$type."'          // refers to a specific animal type choosen in a previous form
AND animal.ID_Sexe LIKE '".$sexe."'              // refers to a gender selection in a previous form
AND animal.ID_Status LIKE '".$status."'         // refers to an animal status selected in a previous form
Order By
animal.Nom_Animal

 

My database tables and structure are the following (I'm showing you only the pertaining part because the rest is big.ructure.PNG[/img]

 

I might do a double for loop to do this in php but this will generate a lot of queries since there are 11 000 evaluations lines in the eval table and 33 000 result lines in the result table.

 

So there I am, I'm not asking for a complete answer. I'm a hard worker and I want to get this right. So far I've been able to work a lot of things right, but with this problem i'm wondering if my structure is just not compatible with this kind of request. I wonder if sql views could help me, but i'm not sure...

 

thanks in advance for all your answers!! 

[ezekielnin]

Here is the complete structure of my tables :

Animal

 

Evaluations

 

Caract (custom criterias)

 

Caract type

 

Results (contains all the results ferering to one criteria id  and one evaluation id

[ezekielnin]

And I haven't been able to run the explain query in phpmyadmin for this particular query. I'm not very good with mysql, i must admit. 

[Barand]

I don't know which fields you need to output, but if you want results on one line it looks as though you need GROUP_CONCAT() with a GROUP BY evaluation_id.

[xyph]

In te future, you're better off giving us a SQL export of the table structure, rather than a screenshot from phpMyAdmin.

[ezekielnin]

@barand : What I want is almost exactly what group_concat does, but I would like each value in a different column. Is it possible?

 

@xyph : I'll try to build a proper sql, some of the data I got is confidential.

[xyph]

We don't need the data, though a bit of sample data saves the people helping you from a bit of work. The easier you make it for us to help you, the more help you'll get.

[ezekielnin]

Here's a complete sql clean file for the structure and some evaluation data so you can test. https://dl.dropbox.com/u/1252284/php/test_data_for_forum.sql

 

Thanks again for all your help! 

[Barand]

Where you have headings:

 

Evaluation | Results |  Custom criteria results

 

what is it you want listed? It bears no resemblance at all to your query.