Jump to content


Photo

how2: Retrieve query results withut knowing the column names


  • Please log in to reply
2 replies to this topic

#1 elwoody

elwoody
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 11 October 2006 - 10:19 PM

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

#2 akitchin

akitchin
  • Staff Alumni
  • Advanced Member
  • 2,516 posts
  • LocationCalgary, AB, Canada

Posted 11 October 2006 - 10:44 PM

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:

<?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>';
  }
?>

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:

<?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>';
  }
?>

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

#3 elwoody

elwoody
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 12 October 2006 - 01:24 AM

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.


<?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>";
?>





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users