how2: Retrieve query results withut knowing the column names

[elwoody]

I'm trying to use mySQL directive DECRIBE to lsit the column names in a query result, then use that data to output the results to a page, without explicitly specifying the column names.  I've gotten this far, without success:

<?php    //... get initial city data
$city = "NYC";
$query1  = "SELECT * FROM mastercities WHERE CityCode ='" .$city ."'";
$result1 = mysql_query($query1) or Die("query 1 failed");

$query2  = "DESCRIBE mastercities";
$result2 = mysql_query($query2) or Die("query 2 failed");

while($row1 = mysql_fetch_array($result1))
{
while ($row2 = mysql_fetch_array($result2))
{
$currentRow = "'".$row2['Field']."'";
echo "<break>".$row1[$currentRow].",  ";
}
echo "<para>";
}
?>


I know these queries work independently, but as a novice php person, I'm stuck - any guidance would be appreciated...

Thanks,

Elwoody

[akitchin]

since you're grabbing potentially many rows from mastercities, don't slow it down by running through mastercities' DESCRIBE command each time you've got a row from the SELECT.  pull out the fieldnames first:

[code]<?php
  $query2  = "DESCRIBE mastercities";
  $result2 = mysql_query($query2) or Die("query 2 failed");

  $fieldnames = array();

  while ($col_info = mysql_fetch_assoc($result2))
  {
    $fieldnames[] = $col_info['Field'];
  }

  $city = 'NYC';
  $query1  = "SELECT * FROM mastercities WHERE CityCode ='" .$city ."'";
  $result1 = mysql_query($query1) or Die("query 1 failed");

  while ($row = mysql_fetch_assoc($result1))
  {
    foreach ($fieldnames AS $col)
    {
      echo "<break>{$row[$col]}";
    }
    echo '<para>';
  }
?>[/code]

to be honest i don't know whether this will fix your specific problem (since you haven't told us how this script isn't working), but it should put a little less strain on the database.  one thing i should mention is the foreach() works on a row returned by MySQL the same way it works on an array:

[code]<?php
  $city = 'NYC';
  $query1  = "SELECT * FROM mastercities WHERE CityCode ='" .$city ."'";
  $result1 = mysql_query($query1) or Die("query 1 failed");

  while ($row = mysql_fetch_assoc($result1))
  {
    foreach ($row AS $val)
    {
      echo "<break>$val";
    }
    echo '<para>';
  }
?>[/code]

this would effectively achieve the same thing with less server strain.

[elwoody]

Thanks - you got me over the hump!  The following code takes a query and outputs the column names and row data, in a table, without explicitly identifying the column names.  Handy thing for me.


[code]<?php

$query  = "DESCRIBE mastercities";
$columnNames = mysql_query($query) or Die("query failed");
echo "<table border='1' cellspacing='1' cellpadding='1'><tr>";

while ($row = mysql_fetch_array($columnNames)) {
echo "<td>".$row['Field']."</td>";
}
echo "</tr><tr>";
  $query1  = "SELECT * FROM mastercities";
  $result1 = mysql_query($query1) or Die("query 1 failed");

  while ($row = mysql_fetch_assoc($result1))
  {
echo "<tr>";
    foreach ($row AS $val)
    {
echo "<td>"."$val"."&nbsp;</td>";
    }
    echo '</tr>';
  }
  echo "</table>";
?>[/code]