Multiple data sets from one query

[lemmin]

Is it possible to return multiple data sets from a single query? Or simply send two separate SELECT statements in one request to have it return multiple arrays of data?

 

Thanks

[fenway]

The latter.

[lemmin]

Thanks for the reply. Can you direct me how to do this?

 

Thanks

[mikosiko]

Thanks for the reply. Can you direct me how to do this?

 

Thanks

 

I wrote this quick example to give you the idea of one way to do it:

<?php
$mysqli = new mysqli("localhost", "youusername", "youpassword", "yourdatabase");

/* Check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

// Define Multi-Query
$query  = "SELECT * FROM table1;";
$query .= "SELECT * FROM table2";

// Execute Multi-Query 
if ($mysqli->multi_query($query)) {
  // Use (or Store) and Process the first result set if it exists 
  // NOTE: Replace "field1"... "field4" with the  fieldnames from table1
  if ($result = $mysqli->use_result()) {
     while ($row = $result->fetch_assoc()) {
        echo $row['field1'] . " - " . 
             $row['field2'] . " - " .
             $row['field3'] . " - " .
             $row['field4'] . "<br />";
     }
     // Close and release first result set
     $result->close();
  }

  // Check if the query generated more results
  if ($mysqli->more_results()) {
     if ($mysqli->next_result()) {     // I wrote 2 if's only for demo purposes.. obviously can be done in only one if
        printf("-- Now Printing Records from Second Table --<br />");  
     
       // Use (or Store) and Process the second result set if it exists 
       // NOTE: Replace "field1"... "field4" with the fieldnames from table2
        if ($result = $mysqli->use_result()) {
           while ($row = $result->fetch_assoc()) {
              echo $row['field1'] . " - " . 
                   $row['field2'] . " - " .
                   $row['field3'] . " - " .
                   $row['field4'] . "<br />";
           }
           // Close and release the second result set
           $result->close();
        }
      }
  }    
}

// Close connection
$mysqli->close();
?>