CODEWITHSUNDEEP
phpmysqlcsv
ageoff
ageoff

Reputation: 2828

Combining 6 queries into 1 resultset for exporting?

Ok, so this may sound a little strange and maybe over complicated. Here is the situation. I 2 sets of 3 queries. I will try to make a simple example to explain exactly what I am trying to do:

Queries:

//First set of queries
$query1 = "SELECT Name, Date FROM Table1";
$query2 = "SELECT Type, Place, Location FROM Table2";
$query3 = "SELECT One FROM Table3";

//Second set of queries
$query4 = "SELECT Name, Date FROM Table1 WHERE ID=1";
$query5 = "SELECT Type, Place, Location FROM Table2 WHERE ID=1";
$query6 = "SELECT One FROM Table3 WHERE ID=1";

You just have to trust me when I tell you that I CANNOT combine these two sets of queries. these are over simplified select statements to get the concept of what I am trying to do.

So here is my php code:

//Set 1
$data1 = mysql_query($query1) or die(mysql_error());
$data2 = mysql_query($query2) or die(mysql_error());
$data3 = mysql_query($query3) or die(mysql_error());
while ($line1 = mysql_fetch_array($data1, MYSQL_ASSOC) &&
        $line2 = mysql_fetch_array($data2, MYSQL_ASSOC)) {
    while ($line3 = mysql_fetch_array($data3, MYSQL_ASSOC)) {
        //COMBINE $line1, line2, line3 into a single $lineSet1 -- HOW DO I DO THIS?
    }
}

//Set 2
$data4 = mysql_query($query4) or die(mysql_error());
$data5 = mysql_query($query5) or die(mysql_error());
$data6 = mysql_query($query6) or die(mysql_error());
while ($line4 = mysql_fetch_array($data4, MYSQL_ASSOC) &&
        $line5 = mysql_fetch_array($data5, MYSQL_ASSOC)) {
    while ($line6 = mysql_fetch_array($data6, MYSQL_ASSOC)) {
        //COMBINE $line4, line5, line6 into a single $lineSet2 -- HOW DO I DO THIS?
    }
}

//Append $lineset1 and $lineset2 so I have 1 resultset $results
$result = array_merge($lineSet1, $lineSet2);
//So now I can pass this $result array into my array2csv function that takes a multidimensional array:
function array2csv(array &$array)
{
   if (count($array) == 0) {
     return null;
   }
   ob_start();
   $df = fopen("exportedLeads{$_SESSION['id']}.csv", 'w');
   fputcsv($df, array_keys(reset($array)));
   foreach ($array as $row) {
      fputcsv($df, $row);
   }
   fclose($df);
   return ob_get_clean();
}

I know this seems really complicated, but I am pretty confused and not that good at php. Any help would be appreciated. Thanks! 

TABLE1:

ID | Name | Date

TABLE2:

ID | Table1_ID | Type | Place | Location

TABLE3:

ID | Table1_ID | One

EDIT: I have been reading into JOIN statements. Is this possible a case for that?

Upvotes: 0

Views: 53

Answers (2)

Prix
Prix

Reputation: 19528

You can resume both of your sets into a single query like using JOIN assuming your ID's match.

First set into 1 query:

SELECT t1.Name, t1.Date, t2.Type, t2.Place, t3.One FROM Table1 t1
  JOIN Table2 t2
    ON t2.Table1_ID = t1.ID
  JOIN Table3 t3
    ON t3.Table1_ID = t1.ID

Second set into 1 query:

SELECT t1.Name, t1.Date, t2.Type, t2.Place, T2.Location, t3.One
  FROM Table1 t1
  JOIN Table2 t2
    ON t2.Table1_ID = t1.ID
  JOIN Table3 t3
    ON t3.Table1_ID = t1.ID
 WHERE t1.ID = 1

Upvotes: 1

Marc B
Marc B

Reputation: 360682

Assuming your three queries all return the SAME number of rows, you can abuse the assignment operator:

while(($row1 = mysql_fetch($result1))
   && ($row2 = mysql_fetch($result2))
   && ($row3 = mysql_fetch($result3))) {

to fetch from all three result sets in parallel. The downside is that if your result sets are different lengths, you'll only fetch as many records as there are in the SHORTEST result set. e.g. as soon as any of those fetch calls returns false, the entire while aborts, even if there's still data in the other two results.

Upvotes: 0

Related Questions