Attempting to display query results as columns in PHP

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
avstudio1
Forum Newbie
Posts: 13
Joined: Mon Nov 07, 2005 5:20 pm

Attempting to display query results as columns in PHP

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

viewtopic.php?t=37448 may be of interest..
avstudio1
Forum Newbie
Posts: 13
Joined: Mon Nov 07, 2005 5:20 pm

Post 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
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
avstudio1
Forum Newbie
Posts: 13
Joined: Mon Nov 07, 2005 5:20 pm

Post by avstudio1 »

Consider it tested, and functional ...... thanks.
Post Reply