while loops

Anon

Hi i'm new to php and i'm trying to extract some info from a mysql database, what i need it to do is get a task no and info from one table then get the matching defects from another table and the matching parts from another table. This all works fine and it inserts the data great however it gets taskno 1 and puts the defect found then the part but that taskno may have 2 defects and 3 parts, and it only takes the first one found then goes on to taskno 2 and it's defect and part. I hope someone can understand what i'm going on about 🙂 I tried inserting a while loop in the code but it just times out with a screen full of the same first defect. Here is the defect code with out the while loop:
<?
$db = mysql_connect ("localhost", "root", "orange");
mysql_select_db ("camco", $db);
$query3 = "SELECT * FROM defects WHERE jobno = '$row9[6]' AND taskno = '$row[3]'";
$res3 = mysql_query ($query3, $db);
$row3 = mysql_fetch_row ($res3);

printf ("\n<tr><td></td>\n", $row3[3], $row3[3], $row3[3]);
printf ("\n<td><font face=arial size=2>%s</td>\n", $row3[4], $row3[4], $row3[4]);
printf ("\n<td><font face=arial size=2>%s</td></tr>\n", $row3[5], $row3[5], $row3[5]);

thanks, Martin

Anon

If I understand you correctly you want to get a list of:
task, defects for this task,parts for this defect.

SELECT * FROM table GROUP BY task,defect,part;

This will give all the data grouped by task,
so you get all the tasks.
Then grouped by defect, so you get all the defects for each task.
Then grouped by part, so you get all parts for each defect for each tak.

Try it out in the mysql commandline and see if this is good for you.

Anon

I tried it but that's not going to work as the task, defect and part are all from seperate tables. The output it's trying to create is similar to this

taskno description
1 oil change
defects
filter
parts
new filter

taskno description
2 etc

I hope this makes sense. here is the whole code for this
<?
$db = mysql_connect ("localhost", "root", "orange");
mysql_select_db ("camco", $db);
$query = "SELECT * FROM lambitems WHERE servicetypeno = '$row9[21]' ORDER BY taskno";
$res = mysql_query ($query, $db);

  for ($i = 0; $i < mysql_num_rows ($res); $i++) {
  $row = mysql_fetch_row ($res);

echo '<table border=1>';
echo '<tr><td><font face=arial size=2><b>Task No</td><td><font face=arial size=2><b>Task Description</td><td><font face=arial size=2><b>Nature</td><td><font face=arial size=2><b>Interval</td><td><font face=arial size=2><b>Cert. Person</td><td><font face=arial size=2><b>Hours</td><td><font face=arial size=2><b>Date</td></tr>';
printf ("<tr><td><input type=\"READONLY\" name=\"taskno\" size=\"6\" value=\"$row[3]\"></td>\n");
printf ("\n<td><font face=arial size=2>%s</td>\n", $row[1], $row[1], $row[1]);
printf ("\n<td><font face=arial size=2>%s</td>\n", $row[4], $row[4], $row[4]);
printf ("\n<td><font face=arial size=2>%s\n", $row[5], $row[5], $row[5]);
printf ("<input type=\"hidden\" name=\"taskno\" size=\"6\" value=\"$row[3]\"></td>\n");

?>
<?
$db = mysql_connect ("localhost", "root", "orange");
mysql_select_db ("camco", $db);
$query2 = "SELECT * FROM workdetail WHERE custno = '$row9[6]' AND techno = '$row[3]' ORDER BY techno";
$res2 = mysql_query ($query2, $db);

  $row2 = mysql_fetch_row ($res2);

printf ("\n<td><font face=arial size=2>%s</td>\n", $row2[15], $row2[15], $row2[15]);
printf ("\n<td><font face=arial size=2>%s</td>\n", $row2[3], $row2[3], $row2[3]);
printf ("\n<td><font face=arial size=2>%s\n", $row2[5], $row2[5], $row2[5]);
echo '<tr><td></td><td><font face=arial size=2><b>Defect Found</td><td><font face=arial size=2><b>Description</td></tr>';

?>
<?
$db = mysql_connect ("localhost", "root", "orange");
mysql_select_db ("camco", $db);
$query3 = "SELECT * FROM defects WHERE jobno = '$row9[6]' AND taskno = '$row[3]' GROUP BY taskno";
$res3 = mysql_query ($query3, $db);
$row3 = mysql_fetch_row ($res3);

printf ("\n<tr><td></td>\n", $row3[3], $row3[3], $row3[3]);
printf ("\n<td><font face=arial size=2>%s</td>\n", $row3[4], $row3[4], $row3[4]);
printf ("\n<td><font face=arial size=2>%s</td></tr>\n", $row3[5], $row3[5], $row3[5]);

?>
<?

$db = mysql_connect ("localhost", "root", "orange");
mysql_select_db ("camco", $db);
$query4 = "SELECT * FROM parts WHERE jobno = '$row9[6]' AND taskno = '$row[3]'";

$res4 = mysql_query ($query4, $db);

  $row4 = mysql_fetch_row ($res4);

echo '<tr><td></td><td></td><td><font face=arial size=2><b>Part</td><td><font face=arial size=2><b>Part No</td><td><font face=arial size=2><b>Cost</td></tr>';
printf ("\n<tr><td></td>\n", $row4[3], $row4[3], $row4[3]);
printf ("\n<td></td>\n", $row4[4], $row4[4], $row4[4]);
printf ("\n<td><font face=arial size=2>%s</td>\n", $row4[5], $row4[5], $row4[5]);
printf ("\n<td><font face=arial size=2>%s</td>\n", $row4[6], $row4[6], $row4[6]);
printf ("\n<td><font face=arial size=2>%s</td></tr>\n", $row4[7], $row4[7], $row4[7]);
echo '</table>';
echo '<table border=0><tr><td><br></td></tr></table>';

}
mysql_free_result ($res);
mysql_free_result ($res2);
mysql_free_result ($res3);
mysql_free_result ($res4);

mysql_close ($db);
mysql_close ($db);
mysql_close ($db);
mysql_close ($db);

It draws out the task nos then fills in the info relevent to that task no, then goes onto the next taskno until there are no tasks left. What i need it to do is pull out all the defects not just one and all the parts that relate to the taskno and then go onto the next taskno and pull the defects and parts. I hope this makes sense, thanks Martin

Anon

You can do all this with just one JOIN statement, check out the mysql manual.

As for the method you described, you are allmost there.

Just notice that this is a very complex way of getting the results,
it will do a lot of queries to your database.

So here goes.
First off, you don't have to open a new database connection for every query.
You can run as many queries as you want using the same connection.

What you have to do is this:

open a database connection.
request all the task items (select * from workdetail)
then start a while loop so you can work with every item in the results:

$task_result=mysql_query($your_query_here);
while ($task_result_row=mysql_fetch_array($task_result))
{
};

Inside this loop you can execute another query, to get the defects for each task:

$defect_result=mysql_query("select bla defect where jobid=".$task_result_row["jobID"]);

then you can create another while loop (all still inside the first loop)
to work with all the defects and to fetch all the parts for this defect.

You'll end up with three while loops. The first is to get the jobs, the second to get the defects for the jobs, and the third to get the parts for the defects.