Transpose rows into columns

[tbajaj]

I am fetching a data from database which comes out like this

 

EmpID First Name DoJ Supervisor

'34698' 'Ian Bobby' '2011-08-09' 'Daniel Lin'

'34684' 'Sally Dodd' '2011-08-09' 'James Kirk'

'34871' 'Alan Dandy' '2011-08-09' 'Pamela Roy'

 

I want to Transpose it like this

EmpID '34698' '34684' '34871'

First Name 'Ian Bobby' 'Sally Dodd' 'Alan Dandy'

DoJ '2011-08-09' '2011-08-09' '2011-08-09'

Supervisor 'Daniel Lin' 'James Kirk' 'Pamela Roy'

 

Please help

 

[trq]

Where is your code?

[tbajaj]

Here is the code I am struggling with. I thought it was not taking me anywhere because the output is only 2 columns

<?php

$link = mysql_connect("myserver", "mylogin", "mypass123");

if (!$link) {

    die('Not connected : ' . mysql_error());

}

$hostname = mysql_get_host_info($link);

 

// make ddms the current db

$db_selected = mysql_select_db("primarydb", $link);

if (!$db_selected) {

    die ('Can\'t use ddms : ' . mysql_error());

}

 

 

//this is just a marker to see where I am on the output

//printf("%s\n", "XXX");

// Get all the data from the table

$result = mysql_query("SELECT EmpID, FirstName, DoJ, Supervisor FROM primarydb.projects where projectID like '34%'; ") or die(mysql_error()); 

$html = "<table border=1>";

//printf("%s\n", "YYY");

$numOfCols = mysql_num_fields($result);

    $numOfRows = mysql_num_rows($result);

    $info = mysql_fetch_assoc($result);

    if($numOfRows > 0){

        do {

            foreach($info as $column => $value) {

                $html.= "<tr><td>$column</td><td>$value</td></tr>";

            }

        }

        while ($info = mysql_fetch_array($result));

    }

    $html.=  "</table>";

    mysql_free_result($result);   

    echo $html;

?>

[laffin]

$rows=array();
while($info=mysql_fetch_array($result,MYSQL_NUM);
{
  foreach($info as $key=>$val)
  {
      $rows[$key][]=$val;
  }
}
foreach($rows as $row)
{
  echo '<tr>';
  foreach($row as $cell)
  {
    echo "<td>$cell</td>";
  }
  echo '</tr>';
}

[tbajaj]

Dear Laffin, I feel like giving you a hug. Very simple and elegant code. I had to make minor modifications and I am glad that I also used my brain besides picking yours.

My code need one more blessing. How do I put Column Titles as my first Column?

So, if you see my example, my first row was actually column name from the table.

I really appreciate it. Thanks

 

[laffin]

Well than we would need a different query to grab the field names, and use them instead of a numeric entry when calling mysql.
than post these in a upper loop 
$rows=array();
while($info=mysql_fetch_array($result,MYSQL_ASSOC) // Changed from MYSQL_NUM to MYSQL_ASSOC, to provide headers
{
  foreach($info as $key=>$val)
  {
      $rows[$key][]=$val;
  }
}
echo '<tr>';
foreach($rows as $header=>$row) // Grab the header names from each column
{
   echo "<td>$header</td>';
}
echo '</tr>';
foreach($rows as $row)
{
  echo '<tr>';
  foreach($row as $cell)
  {
    echo "<td>$cell</td>";
  }
  echo '</tr>';
}

[tbajaj]

Hello Laffin, thanks again.

I have attached the complete file of code. These things are requesting your attention

* Although the new piece of code you added is generating header but as a regular header and not transposed.

* After I execute that piece of code, my index is reaching end of array, so I ended up fetching again, may be you know how to fix that too

Finally, I made minor modification like concatenation to html variable, instead of echoing. That is just FYI.

Please advise.

Thanks

17347_.php

[laffin]

Well than we would need a different query to grab the field names, and use them instead of a numeric entry when calling mysql.
than post these in a upper loop 
$rows=array();
while($info=mysql_fetch_array($result,MYSQL_ASSOC) // Changed from MYSQL_NUM to MYSQL_ASSOC, to provide headers
{
  foreach($info as $key=>$val)
  {
      $rows[$key][]=$val;
  }
}
foreach($rows as $header=>$row) // Grab the header names from each column
{
   echo "<tr><td>$header</td>';
  foreach($row as $cell)
  {
    echo "<td>$cell</td>";
  }
  echo '</tr>';
}

 

was my mistake

[tbajaj]

Dear Laffin. I am very appreciative. I bow to your php skills. You are a life savior.