Displaying results of a query

revez2002

Hello,

I am trying to display the results from two different queries. The second query is based on a variable from the first query, and I am having a tough time to get it to work properly.

It appears that once the second for statement is completed, the first for statement also terminates.

What am I overlooking?

  // Query the PROJECTS TABLE to display the schedule for the week specified
	  $query_01 = mysql_query("SELECT 
	  COMPANY.company_name, 
	  COMPANY.corp_name, 
	  COMPANY.prov_state, 
	  PROJECTS.proj_ref_ID, 
	  PROJECTS.proj_type, 
	  PROJECTS.interface_type, 
	  PROJECTS.number_of_QJs 
	  FROM COMPANY, PROJECTS
	  WHERE PROJECTS.company_ID=COMPANY.company_id
	  AND install_start_date='2005-04-18'
	  ORDER BY COMPANY.corp_name, COMPANY.company_name");

$project_results = mysql_num_rows($query_01); 

for ($i=0; $i < $project_results; $i++) {
$row_01 = mysql_fetch_array($query_01);
$proj_ref_ID = stripslashes($row_01["proj_ref_ID"]);
$proj_type = stripslashes($row_01["proj_type"]);
$company_name = stripslashes($row_01["company_name"]);
$corp_name = stripslashes($row_01["corp_name"]);
$prov_state = stripslashes($row_01["prov_state"]);
$interface_type = stripslashes($row_01["interface_type"]);
$number_of_QJs = stripslashes($row_01["number_of_QJs"]);

// Query the PERSONNEL_SCHEDULE TABLE to display the resources assigned for the week specified
$query_02 = mysql_query("SELECT 
personnel_name, 
activity_type, 
activity_start_year, 
activity_start_month, 
activity_start_day, 
activity_finish_year, 
activity_finish_month, 
activity_finish_day 
FROM PERSONNEL_SCHEDULE WHERE proj_ref_ID='$proj_ref_ID'");

$personnel_schedule_results = mysql_num_rows($query_02); 

for ($k=0; $k < $personnel_schedule_results; $k++) {	

$row_02 = mysql_fetch_array($query_02);
$activity_start_year = stripslashes($row_02["activity_start_year"]);
$activity_start_month = stripslashes($row_02["activity_start_month"]);
$activity_start_day = stripslashes($row_02["activity_start_day"]);
$activity_finish_year = stripslashes($row_02["activity_finish_year"]);
$activity_finish_month = stripslashes($row_02["activity_finish_month"]);
$activity_finish_day = stripslashes($row_02["activity_finish_day"]);
?>

<?php } } ?>

</table>

liquorvicar

a couple of stylistic points that may help you find the error.
you can simplify this:

for ($i=0; $i < $project_results; $i++) {
$row_01 = mysql_fetch_array($query_01);

while ($row_01 = mysql_fetch_array($query_01)) {

and

$proj_ref_ID = stripslashes($row_01["proj_ref_ID"]);
$proj_type = stripslashes($row_01["proj_type"]);
$company_name = stripslashes($row_01["company_name"]);
$corp_name = stripslashes($row_01["corp_name"]);
$prov_state = stripslashes($row_01["prov_state"]);
$interface_type = stripslashes($row_01["interface_type"]);
$number_of_QJs = stripslashes($row_01["number_of_QJs"]);

simplifies to

foreach ($row_01 as $key=>$value) {
  $data[$key] = stripslashes($value);
}

the second tip will really help you if you ever change your DB structure.

as for your error, have you definitely got more than one row in the first query?

revez2002

Hello liquorvicar,

Thank you very much for your suggestions.

Yes, the data in the table that matches the first query should display 5 rows of information.

However, I noticed that the data in the table for the second query only had information relating to one of the rows. Once I added a recordset (with most of the values empty except for the proj_ref_ID variable) it displayed properly.

While I am still not able to get the code to do exactly what I was intending, this will still provide a workable solution for now.

THANKS!

revez2002

liquorvicar

hmmm, have you tried combining the 2 SQL queries and seeing what happens?
ie.

$SQL = "SELECT
      C.company_name,
      C.corp_name,
      C.prov_state,
      PR.proj_ref_ID,
      PR.proj_type,
      PR.interface_type,
      PR.number_of_QJs,
      PE.personnel_name,
      PE.activity_type,
      PE.activity_start_year,
      PE.activity_start_month,
      PE.activity_start_day,
      PE.activity_finish_year,
      PE.activity_finish_month,
      PE.activity_finish_day
      FROM COMPANY C, PROJECTS PR, PERSONNEL_SCHEDULE PE
      WHERE PR.company_ID=C.company_id
      AND PR.proj_ref_ID=PE.proj_ref_ID AND install_start_date='2005-04-18'
      ORDER BY COMPANY.corp_name, COMPANY.company_name";
$query = mysql_query($sql,$c);
while ($row=mysql_fetch_assoc($query)) {

revez2002

Thanks for the additional suggestion.
I will also give this a try.
revez2002