Page 1 of 1

Attempting to display query results as columns in PHP

Posted: Mon Nov 07, 2005 5:33 pm
by avstudio1
Hi.

i'm using PHP5, MYSQL, Apache2.

Quite simply, I am attempting to swap the way in which my table is displayed. I take a simple query: SELECT ID, NAME FROM TABLE, and throw it into an HTML table (I've tried various techniques).
This usually results in a table not unlike the following:

1 A
2 B
3 C
etc.

But I can't figure out how to make the ID accross the top, with its corresponding data under for each ID.

EXAMPLE:

1 2 3
A B C

I hope this is not too difficult. Can someone please point me to the appropriate technology or function I should be using?

Thanks.

Posted: Mon Nov 07, 2005 7:10 pm
by feyd
viewtopic.php?t=37448 may be of interest..

Posted: Mon Nov 07, 2005 9:59 pm
by avstudio1
OK, that helped a bit.

This is what I came up with. I still think it's a bit cumbersome, as it creates as many queries as there are resulting rows, but it works at least.

I'm open to improvements .....

Code: Select all

$InitQuery = "select * from DCM_Status";
$InitResult = mysql_query($InitQuery);
$MaxFields = mysql_num_rows($InitResult);

mysql_free_result($InitResult);

$SQL = array();
$Q = array();
$Result = array();
$i = 1;

echo "<table><tr>";
while ($i <= $MaxFields)
   {
   $SQL[$i] = "select * from DCM_Status where DCM_ID = " . $i;
   $Q[$i] = mysql_query($SQL[$i]);
   $Cache[$i] = array();
   echo "<td>";
   echo "<table>";

   while($Result[$i] = mysql_fetch_assoc($Q[$i]))
      {
      $Cache[$i] = $Result[$i];
      echo "<tr><td>" . $Cache[$i]['DCM_ID'] . "</td></tr>";
      echo "<tr><td>" . $Cache[$i]['DCM_Status'] . "</td></tr>";
      echo "<tr><td>" . $Cache[$i]['DCM_Count'] . "<td></tr>";
      }
   echo "</td>";

   mysql_free_result($Q[$i]);
   $i++;
   echo "</tr></table>";
}
The result looks like what I am after:

Code: Select all

1 2 3 4
1 1 1 1
2 5 7 3

Posted: Tue Nov 08, 2005 1:16 am
by s.dot
seems like you could store your results in an array, and avoid doing a while loop inside of a while loop.

ie:

Code: Select all

while($this = $that)
{
   $newarray[title] = $this['field'];
}
then reference the array inside of one while loop, ie: echo $newarray[$i]

if that makes sense.. i tried to explain it good.

Posted: Tue Nov 08, 2005 10:12 am
by pickle
This should do it with only 1 query:

**Untested**

Code: Select all

$result = results_of_query;

while($row = mysql_fetch_assoc($result))
{
  //loop through each result row, and put each different data field
  //in its own array
  foreach($row as $index=>$value)
  {
      $rearranged[$index][] = $value;
   }
}

/*
$rearranged will now be of this form:
array['name'][0] = 'George'
             [1] = 'Hank'
             [2] = 'Javier'
     ['id'][0] = '1145'
     ['id'][1] = '4421'
     ['id'][2] = '6643'
*/

echo '<table>';
//loop through each data type
foreach($rearranged as $value_array)
{
   echo '<tr>';
   //loop through each value
   foreach($value_array as $value)
   {
      echo '<td>'.$value.'</td>';
   }
   echo '</tr>';
}
echo '</table>';
The resulting table will look like:

Code: Select all

George   Hank   Javier
 1145    4421    6643

Posted: Tue Nov 08, 2005 10:44 am
by avstudio1
Consider it tested, and functional ...... thanks.