-1

I have seven SQL queries that are being executed using the mysqli_multi_query function:

if (mysqli_multi_query($conn, $airlinesql)) {
  do {
    if ($result = mysqli_store_result($conn)) {
      while ($row = mysqli_fetch_row($result)) {
        HELP!!
      }
      mysqli_free_result($result);
    }
  } while (mysqli_next_result($conn));
}
//Build and fill HTML table
HELP!!

Each of the seven queries returns only one element per row, but the number of rows returned for each query is unknown - could even be NULL. I want each query result to populate its own separate column in an HTML table. In other words, the first query may return 5 rows each having only one element. I want each these 5 elements to go into its own row in the first column of the HTML table. The second query result may return 1 element. This element should reside in row 1 of the second column. Etc. Although number of rows returned per query is unknown (and could even be null), the number of columns is fixed at 7 (since that is the number of queries). I'm guessing tossing all results into an array is the right idea, but I can't figure out how to build the table from the array using while/for loops since you typically build by row whereas my thinking is stuck with building by column due to the unknown number of rows returned per query. Or, maybe there's a better way than using mysqli_multi_query? Thanks for your help.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Mike Garofano
  • 17
  • 2
  • Yes, stick that data into a multi-dimensional array first, and determine what the maximum number of results returned by the “largest” query was - that’s how many table rows you’ll want to create. So use a simple `for` loop for that then, and inside another `for` loop for the seven columns. Then just use `isset` to check if the array contains an entry at the respective position - if those, output that value as the table cell content, otherwise a `-` or nothing. – CBroe Aug 14 '20 at 07:39
  • I think the most imporant question is why do you need to use `mysqli_multi_query`? Is there any way you can avoid this? Can you use normal mysqli query? Can you use PDO instead? – Dharman Aug 14 '20 at 11:50
  • I kinda expected to see `mysqli_more_results()` in your snippet like this: https://stackoverflow.com/a/22469722/2943403 I once thought the multi query function was cute -- now I never see any advantage in calling it. It was a passing fad for me personally. May we see your 7 sql strings? Do you perhaps just need a single query with `UNION`s? – mickmackusa Aug 14 '20 at 12:02
  • @mickmackusa I don't think you need `mysqli_more_results()` but I agree this function is so cumbersome there is no reason to ever use it. – Dharman Aug 14 '20 at 12:04

1 Answers1

0

Went with CBroes' suggestion. I know...it's ugly and probably horribly inefficient but IT WORKS. Thanks for your help. The last time I did any coding was using FORTRAN in 1992 so I need all the help I can get!

  $alldata = array();
  $colno = 0;
  $rowcount = 0;
  // Execute multi query
if (mysqli_multi_query($conn, $sql)) {
  do {
    if ($result = mysqli_store_result($conn)) {
      $rowno = 0;
      while ($row = mysqli_fetch_row($result)) {
        foreach ($row as $value) {
          $alldata[$rowno][$colno] = $value;
          $rowno++;
        }
        //count rows in current pass; keep highest value
        if ($rowno > $rowcount) {
          $rowcount = $rowno;
        }
      }
      $colno++;
      mysqli_free_result($result);
    }
  } while (mysqli_next_result($conn));
}
//dump array into table
$outputtable .= "<table>";
for ($i = 0; $i <= $rowcount-1; $i++) {
  $outputtable .= "<tr>";
  for ($j = 0; $j <= $colcount; $j++) {
    if (isset($alldata[$i][$j])) {
      $outputtable .= "<td>" . $alldata[$i][$j] . "</td>";
    }
    $outputtable .= "</tr>";
  }
}    
$outputtable .= "</table>";
echo $outputtable;
Mike Garofano
  • 17
  • 2