queries inside while fetch array

reisrui

Hello there,

I want to show a line in which it´s columns result from 3 different tables.

I´m starting with a first query that I know it returns 3 results, from this first query I´m showing let´s say column A and B;
As for column C and D, I need to query another table and I´m doing it with a second while (mysql_fetch_array) inside the first one;
It doesn´t end here, I need another field in column E that cames from a 3rd table, so I make a third while next to the second one;
It seems to work, except it shows only the first row that the query nº1 should return!!!
I´m writing to you to confirm that I can create 2 whiles inside another like this and still getting all the rows of the original nº 1 query...
Here´s my code:

while($row = mysql_fetch_array($query1))
{
echo "<tr>";
echo "<td nowrap>".$row{'columnA'}."</td>";
echo "<td nowrap>".$row{'columnB}."</td>";

// reading second table
$prova=$row{'columnA'};
$zona=$row{'columnB'};
$query2 = mysql_query("SELECT nome FROM table2 WHERE numero='$prova' and zona='$zona'");
while($row = mysql_fetch_array($query2))
{
echo "<td nowrap>".$row{'colC'}."</td>";
echo "<td nowrap>".$row{'colD'}."</td>";
}
// reading third table
$query3 = mysql_query("SELECT distinct distancia FROM p23_14 WHERE numconc='$idconc' and numprova='$prova'");
while($row = mysql_fetch_array($query3))
{
echo "<td nowrap>".$row{'colE'}."</td>";
}

echo "</a>";
echo "</tr>";
}

Roger_Ramjet

Yes you can, but you should not be doing this. Learn about JOINS and do it all with 1 query.

reisrui

Why shouldn´t I? any technical reason? or performance?

I´m trying with joins but I get a lot of lines in my tests...
My ignorance for sure, but it seems a lot more difficult... joining 3 tables...
it returns thousands of results!!!
Googled a bit for joins, but I´m thinking maybe the problem is my tables indexes... I don´t know what to put in the ON clause.

Any help here?

table A indexes:
anoprova, numprova, lugar (3 together)

table B index:
numero (matches the tableA.numprova)

table C indexes:
anoprova, numprova, lugar (3 together)

leatherback

Hi,

Your indexes are not the problem causing loads of results, but more likely the way you joined the tables. please post the joins you have tried and / or the layout of the table.

You should not perform nested queries, because you very quickly run into the trouble of querying the table for hundreds of times, if you output a lot of info, which gets very server intensive as your dataset grows. Try to get all the data in one go, and produce output.

reisrui

Hello, leatherback
Thanks for your reply.

THIS IS THE FINAL RESULT I WANT IN ONE ROW:

table layouts:
TABLE A
anoprova, numprova, lugar, conc, ...., pontos

indexes: anoprova, numprova, lugar

TABLE B
ano, numero, nome, data, enviados,...

indexes: ano, numero

TABLE C
anoprova, numprova, conc, ...., distancia

indexes: anoprova, numprova, lugar

ahundiak

As leatherback says, joins will probably let you get your data in one query.

To answer your original question, yes you can nest while statements to any reasonable level.

However, just to spare your sanity, I would suggest doing all the queries first and storing the results in a big array. Then cycle through the array and generate your html. Trying to nest queries inside of a nested html loop will get confusing real quick. Even if you get it working, modification will be very difficult.

reisrui

Thanks for your reply, ahundiak

Can you hel me you arrays?
When you say "cicle through the array"....
Sorry my newbieness...

ahundiak

So many problems with your code that I suspect this will only make things worse.

while($row = mysql_fetch_array($query1))
{
    $data = array();
    $data['columnA'] = $row['columnA'];
    $data['columnB'] = $row['columnB'];

// reading second table
$prova=$row['columnA'];
$zona=$row['columnB'];
// This query is broke, 
$query2 = mysql_query("SELECT nome FROM table2 WHERE numero='$prova'   and zona='$zona'");
while($row2 = mysql_fetch_array($query2)) {
    $data['columnC'] = $row2['colC'];
    $data['columnD'] = $row2['colD'];

    // reading third table
    $query3 = mysql_query("SELECT distinct distancia FROM p23_14 WHERE numconc='$idconc' and numprova='$prova'");
    while($row3 = mysql_fetch_array($query3))
    {
       $data['columnE'] = $row3['colE'];
    }
}
echo "<tr><td>{$data['columnA']}</td><td>{$data['columnB']}</td>...<tr>\n";
}

But again, this is a really poor way to do things. Spend an hour or two learning how to write basic queries. It will save you tons of time later on.

Roger_Ramjet

Provide the info you gave is correct, here is your joined-up query

"SELECT A.anoprova, A.numprova, B.nome, B.data, B.enviados, C.distancia, A.pontos
FROM A INNER JOIN B ON(A.numprova=B.numero)
INNER JOIN C ON(A.anoprova=C.anoprova)"

reisrui

Thank you all!
Problem solved!