Query Results Based on Columns, Not Rows

I'm trying to figure out how to retrieve query results by column, not by row. I've never had to do this before and I'm having a difficult time trying to figure out how to approach it. Let's say I have a table of employees....

-|- employeeID -|- FirstName -|- LastName -|- HomePhone -|-

And I want to display the query results in an HTML table. But instead of the normal table format, I want to "rotate" the table and display the results by columns, not rows.

-|- employeeID(1) -|- employeeID(2) -|- employeeID(3) -|-
FirstName(1) -|- FirstName(2) -|- FirstName(3) -|-
LastName(1) -|- Firstname(2) -|- FirstName(3) -|-
HomePhone(1) -|- Firstname(2) -|- Firstname(3) -|-
Department(1) -|- Department(2) -|- Department(3) -|-


What is the most efficient way to "rotate" the results like this?

 

You can't select "colums" per se... you just select data. We just think of them in "rows" because that's how arrays are arranged.

That said, I recommend placing the column names in an array, then doing a loop within a loop to output your data, like this:

/* which columns you want from the DB */
$keys = array ("ID", "firstname", "lastname", "phone", "department");

// code to connect and select DB goes here

// fetch the data, use implode so we select columns in the same
// order as the "keys" array
$find = mysql_query("SELECT ".implode(',', $keys)." FROM tablename ;");

while ($found = mysql_fetch_assoc($find)) {
	$data[]	= $found;
}

// go thru the data array within the $keys array
for ($i = 0; $i < count($keys); $i++) {
	
	echo "<tr>";
	
	for ($j = 0; $j < count($data); $j++) {
		echo "<td>".$data[$j][$keys[$i]]."</td>";
	}
	
	echo "</tr>";
}

Code (markup):

 

Thanks a bunch, Sketch. Some green coming your way! One problem I'm having though. The query is working find and pulling the correct data. And even the table is being drawn with the correct number and rows and cells based on the query results, however there isn't any data being displayed in each cell.

Is there something wrong in this part of the code?

echo "<td>".$data[$j][$keys[$i]]."</td>";

PHP:

It looks like everything else is working perfectly as the correct number of rows and columns are being drawn... just without any data in them.

 

I tried it out locally and it worked... could be a difference in PHP or MySQL versions. Do a print_r($data) and see if that's populated.

 

Got it figured out. I was winding up with elements like $data[0][employeeID] instead of $data[0][0]. Thanks for the help, works perfectly now!

 

Actually, you were supposed to have $data[0][employeeID] ... that's why I had the column names in $keys, so that when you called $data[$j][$keys[$i]] you'd get the right output. But, as long as you got it to work that's what matters