combining two queries into one array

[revez2002]

Hello,

I have viewed some of the other posts relating to combining queries, and I still don't quite get it. What I would like to do is to perform two queries on a mySQL database, combine the results from those queries, and then display the results individually as line items in a table.

The code that I am using right now is as follows, and any help with this would be appreciated.

revez2002

//////////////////////////////////////////////////////////////////////////

<?php
	  $query_01 = mysql_query("SELECT
	  COMPANY.company_name,
	  PERSONNEL_SCHEDULE.proj_ref_ID,
	  PERSONNEL_SCHEDULE.personnel_name,
	  PERSONNEL_SCHEDULE.activity_type,
	  PERSONNEL_SCHEDULE.activity_start_year,
	  PERSONNEL_SCHEDULE.activity_start_month,
	  PERSONNEL_SCHEDULE.activity_start_day,
	  PERSONNEL_SCHEDULE.activity_finish_year,
	  PERSONNEL_SCHEDULE.activity_finish_month,
	  PERSONNEL_SCHEDULE.activity_finish_day
	  FROM COMPANY, NRT_PERSONNEL_SCHEDULE
	  WHERE PERSONNEL_SCHEDULE.company_ID=COMPANY.company_id
	  AND activity_start_year='$schedule_year'
	  AND activity_start_month='$schedule_month'
	  ORDER BY COMPANY.company_name");

  $personnel_schedule_results = mysql_num_rows($query_01);

  $query_02 = mysql_query("SELECT
  COMPANY.company_name,
  PERSONNEL_SCHEDULE.proj_ref_ID,
  PERSONNEL_SCHEDULE.personnel_name,
  PERSONNEL_SCHEDULE.activity_type,
  PERSONNEL_SCHEDULE.activity_start_year,
  PERSONNEL_SCHEDULE.activity_start_month,
  PERSONNEL_SCHEDULE.activity_start_day,
  PERSONNEL_SCHEDULE.activity_finish_year,
  PERSONNEL_SCHEDULE.activity_finish_month,
  PERSONNEL_SCHEDULE.activity_finish_day
  FROM COMPANY, PERSONNEL_SCHEDULE
  WHERE PERSONNEL_SCHEDULE.company_ID=COMPANY.company_id
  AND activity_start_year='$schedule_year'
  AND activity_start_month='$schedule_month_minus_01'
  AND activity_finish_month='$schedule_month'	  
  ORDER BY COMPANY.company_name");

  $personnel_schedule_results_02 = mysql_num_rows($query_02);

  $the_combined_results = ($personnel_schedule_results + $personnel_schedule_results_02);

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

  for ($i=0; $i < $personnel_schedule_results_02; $i++) {
	    $row_02 = mysql_fetch_array($query_02);
		}

  $the_combined_query = array_merge($row_01, $row_02);

for ($i=0; $i < $the_combined_results; $i++) {
  $row_03 = mysql_fetch_array($the_combined_query);
  $proj_ref_ID = stripslashes($row_03["proj_ref_ID"]);
  $company_name = stripslashes($row_03["company_name"]);
  $activity_type = stripslashes($row_03["activity_type"]);
  $personnel_name = stripslashes($row_03["personnel_name"]);
  $activity_start_year = stripslashes($row_03["activity_start_year"]);
  $activity_start_month = stripslashes($row_03["activity_start_month"]);
  $activity_start_day = stripslashes($row_03["activity_start_day"]);
  $activity_finish_year = stripslashes($row_03["activity_finish_year"]);
  $activity_finish_month = stripslashes($row_03["activity_finish_month"]);
  $activity_finish_day = stripslashes($row_03["activity_finish_day"]);

?>

<table border="0" cellpadding="0" cellspacing="0" width="780">
<tr>
<td width="5" bgcolor="#FFFFFF"></td>
<td class="general_content_03" width="<?php echo $company_width; ?>" bgcolor="#FFFFFF"><a class="message_center" href="<?php echo "rms.php?directory=projects&page_name=pro_003&proj_ref_ID=$proj_ref_ID"; ?>"><?php echo "$company_name"; ?></a></td>
<td width="1" bgcolor="#ebebeb"></td>

<?php
    $k = "1";
    while ($k <= $days_in_month){
    echo "<td class='general_content_02_white' height=\"20\" align='center' valign='middle' width='20' ";

if(($activity_start_day <= $k) && ($activity_finish_day >= $k) && ($activity_start_month == $activity_finish_month)) {
echo "background=\"content/schedules/images/$resource_color.jpg\">";
} elseif(($activity_start_day <= $k) && ($activity_start_month <> $activity_finish_month)) {
echo "background=\"content/schedules/images/$resource_color.jpg\">";
} elseif(($k==$first_sunday) || ($k==$second_sunday) || ($k==$third_sunday) || ($k==$fourth_sunday) || ($k==$fifth_sunday)) {
    echo "bgcolor=\"#EBEBEB\">";
    } elseif(($k==$first_saturday) || ($k==$second_saturday) || ($k==$third_saturday) || ($k==$fourth_saturday) || ($k==$fifth_saturday)) {
    echo "bgcolor=\"#EBEBEB\">";
    } else {
    echo "bgcolor=\"#FFFFFF\">";
    }
    echo $k;
    echo "</td>";

  }
?>

[Weedpacket]

 for ($i=0; $i < $personnel_schedule_results; $i++) {
$row_01[] = mysql_fetch_array($query_01);
}

for ($i=0; $i < $personnel_schedule_results_02; $i++) {
$row_02[] = mysql_fetch_array($query_02);
}

$the_combined_query = array_merge($row_01, $row_02); 

Four characters' difference.
And it's already an array. Not a MySQL result set, so

$row_03 = mysql_fetch_array($the_combined_query);

is an error.

[planetsim]

The problem with array_merge is in this case if you have field names the same the last one is used if its an associate of course if its a numeric index then its just a reindex.

[revez2002]

Weedpacket,

I understand what you mean when you say that the $row3 variable throws an error.

However, what exactly is meant by "four characters difference"?

revez2002

[Weedpacket]

Originally posted by revez2002
However, what exactly is meant by "four characters difference"?

Compare the code I wrote with the corresponding code you had. There are four characters in mine that are not in yours.

[Roger_Ramjet]

He means that his code put each row into an array ($row[]) while yours just overwrites each row with the next one ($row).

What I don't understand is why you are not using a Union query to do all of this for you? Am I missing something in your output processing?

$sql = "SELECT
      COMPANY.company_name,
      PERSONNEL_SCHEDULE.proj_ref_ID,
      PERSONNEL_SCHEDULE.personnel_name,
      PERSONNEL_SCHEDULE.activity_type,
      PERSONNEL_SCHEDULE.activity_start_year,
      PERSONNEL_SCHEDULE.activity_start_month,
      PERSONNEL_SCHEDULE.activity_start_day,
      PERSONNEL_SCHEDULE.activity_finish_year,
      PERSONNEL_SCHEDULE.activity_finish_month,
      PERSONNEL_SCHEDULE.activity_finish_day
      FROM COMPANY, NRT_PERSONNEL_SCHEDULE
      WHERE PERSONNEL_SCHEDULE.company_ID=COMPANY.company_id
      AND activity_start_year='$schedule_year'
      AND activity_start_month='$schedule_month'
      ORDER BY COMPANY.company_name
UNION
SELECT
      COMPANY.company_name,
      PERSONNEL_SCHEDULE.proj_ref_ID,
      PERSONNEL_SCHEDULE.personnel_name,
      PERSONNEL_SCHEDULE.activity_type,
      PERSONNEL_SCHEDULE.activity_start_year,
      PERSONNEL_SCHEDULE.activity_start_month,
      PERSONNEL_SCHEDULE.activity_start_day,
      PERSONNEL_SCHEDULE.activity_finish_year,
      PERSONNEL_SCHEDULE.activity_finish_month,
      PERSONNEL_SCHEDULE.activity_finish_day
      FROM COMPANY, PERSONNEL_SCHEDULE
      WHERE PERSONNEL_SCHEDULE.company_ID=COMPANY.company_id
      AND activity_start_year='$schedule_year'
      AND activity_start_month='$schedule_month_minus_01'
      AND activity_finish_month='$schedule_month'      

      ORDER BY COMPANY.company_name";

This will return the results from both queries as 1 result set, ready for use without any need for array merge etc.

[revez2002]

Thanks WeedPacket,

I should have looked more closely.

The output from this script seems to indicate the right number of results, however, there is information missing from table rows that are generated. I think that it has something to do with my last for loop.

Any additional suggestions would be appreciated.

	for ($i=0; $i < $combined_results; $i++) {	
	  $proj_ref_ID = stripslashes($row_03["proj_ref_ID"]);
	  $company_name = stripslashes($row_03["company_name"]);
	  $activity_type = stripslashes($row_03["activity_type"]);
	  $personnel_name = stripslashes($row_03["personnel_name"]);
etc...

Roger, my web host isn't running MySQL version 4.X, and so I don't think that I can use UNION in my SQL statement.

revez2002

[Roger_Ramjet]

Then change your host. Something as basic as a Union query really is a must, and I personally could not work without subqueries.

[revez2002]

I am in the process of upgrading to a host that supports mySQL version 4.x. In the meantime, I am still trying to make the script work.

The code that I first posted has been changed as per below. All of the right information appears to be contained in the merged array (I used the print_r function to display the data and cross referenced the records in the database that met the search paramaters).

However, information from the array still does not display when the script is run. I think I am overlooking something in the last for loop. Help in resolving this would be greatly appreciated.

 <?php 
      $query_01 = mysql_query("SELECT 
      COMPANY.company_name, 
      PERSONNEL_SCHEDULE.proj_ref_ID, 
      PERSONNEL_SCHEDULE.personnel_name, 
      PERSONNEL_SCHEDULE.activity_type, 
      PERSONNEL_SCHEDULE.activity_start_year, 
      PERSONNEL_SCHEDULE.activity_start_month, 
      PERSONNEL_SCHEDULE.activity_start_day, 
      PERSONNEL_SCHEDULE.activity_finish_year, 
      PERSONNEL_SCHEDULE.activity_finish_month, 
      PERSONNEL_SCHEDULE.activity_finish_day 
      FROM COMPANY, NRT_PERSONNEL_SCHEDULE 
      WHERE PERSONNEL_SCHEDULE.company_ID=COMPANY.company_id 
      AND activity_start_year='$schedule_year' 
      AND activity_start_month='$schedule_month' 
      ORDER BY COMPANY.company_name"); 

    $personnel_schedule_results = mysql_num_rows($query_01); 

  $query_02 = mysql_query("SELECT 
  COMPANY.company_name, 
  PERSONNEL_SCHEDULE.proj_ref_ID, 
  PERSONNEL_SCHEDULE.personnel_name, 
  PERSONNEL_SCHEDULE.activity_type, 
  PERSONNEL_SCHEDULE.activity_start_year, 
  PERSONNEL_SCHEDULE.activity_start_month, 
  PERSONNEL_SCHEDULE.activity_start_day, 
  PERSONNEL_SCHEDULE.activity_finish_year, 
  PERSONNEL_SCHEDULE.activity_finish_month, 
  PERSONNEL_SCHEDULE.activity_finish_day 
  FROM COMPANY, PERSONNEL_SCHEDULE 
  WHERE PERSONNEL_SCHEDULE.company_ID=COMPANY.company_id 
  AND activity_start_year='$schedule_year' 
  AND activity_start_month='$schedule_month_minus_01' 
  AND activity_finish_month='$schedule_month'       
  ORDER BY COMPANY.company_name"); 

  $personnel_schedule_results_02 = mysql_num_rows($query_02); 

  $the_combined_results = ($personnel_schedule_results + $personnel_schedule_results_02); 

  for ($i=0; $i < $personnel_schedule_results; $i++) { 
        $row_01[] = mysql_fetch_array($query_01); 
        } 

  for ($i=0; $i < $personnel_schedule_results_02; $i++) { 
        $row_02[] = mysql_fetch_array($query_02); 
        } 

  $row_03 = array_merge($row_01, $row_02); 

for ($i=0; $i < $the_combined_results; $i++) { 
  $proj_ref_ID = stripslashes($row_03["proj_ref_ID"]); 
  $company_name = stripslashes($row_03["company_name"]); 
  $activity_type = stripslashes($row_03["activity_type"]); 
  $personnel_name = stripslashes($row_03["personnel_name"]); 
  $activity_start_year = stripslashes($row_03["activity_start_year"]); 
  $activity_start_month = stripslashes($row_03["activity_start_month"]); 
  $activity_start_day = stripslashes($row_03["activity_start_day"]); 
  $activity_finish_year = stripslashes($row_03["activity_finish_year"]); 
  $activity_finish_month = stripslashes($row_03["activity_finish_month"]); 
  $activity_finish_day = stripslashes($row_03["activity_finish_day"]); 

?>