Jump to content

Archived

This topic is now archived and is closed to further replies.

elwoody

how2: Retrieve query results withut knowing the column names

Recommended Posts

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.