best way to select data?

[homerUK]

hi guys..

I have the following data which I need to export (for example)

process_name	2002	2003	2004	2005	2006
====================================================
process1	1.23	4.32	234.21	221.43	665.32
process2	0.98	0.34	5.32	2.56	7.56
process3	1.11	2.43	5.41	1.34	6.32

etc etc

what would be the best way of getting this data from the database? I currently use a massive loop in sql... eg:

SQL > get the process name
SQL > get the answer for year 2002
SQL > get the answer for year 2003
SQL > get the answer for year 2004
etc etc
then move on to the next row (the next process)

as you can see there is an SQL query for EACH row and EACH element of data. I am wondering if there is a better way to do it? The SQL export is taking FOR EVER at the moment as there can be upwards of 500 rows (processes) which need exporting...

[eak]

can you show the code you have so far?

from what you said, your query is only selecting one column from one row.
is that correct? If so, why dont you just select more than one column from each row?

[homerUK]

the code is all over the place... but this is the method its using

PHP Code:

$process_Result = mysql_query("SELECT * FROM processes");
while ($processes = mysql_fetch_array($process_result)) {
   
   $years = mysql_query("SELECT * FROM YEARS") {

       $figures = mysql_query("SELECT * FROM figures where processID=$processes['processID'] AND year=$years['year'];

   }
   
} 


the codes is too big really to post so I've made up the above... it basically gets all the processes, then for each of those, gets the "figures" and each of those is then got for each year...

[eak]

are your tables related in any way?
if so, think about using joins.

also, you shouldnt do the $years query inside the while loop.
it gets the same data every time. you should do it once on the outside
of the loop.

[guelphdad]

can you show the actual layout of your tables?
no you should not do a query inside a php loop to get info from a second table you should be getting that from a join and then the php loops through all the records. see my link to categories/subcategories in my signature.

also, if your data looks anything like the above you should most certainly normalize your table. you should NOT have separate columns for individual years. that would cause you to add a new column each year and would also make other queries more difficult to do than would be necessary with normalized data.