[SOLVED] Displaying table row data as Columna

[telsiin]

Hello everyone

 

I have a table call Products in MYSQL and was I wondering if I could display two rows as a columns.

Any help would be greatly appreciated

 

 

row 1 my feild label

row 2 Maybe something like where CustomerID ="4"

 

Select  * from Products where CustomerID ="4"; display as columns

 

CustomerID  Product1  Product2  Product3  Product4  Product5  Product6  Product7 

      1            0            0            1            1            0          0              0 

      2            1            1            0            0            1          1              0

      3            1            1            1            0            0          1              3

      4            2            2            1            1            0          4              5

 

resulting in something like this

 

Product1  2

Product2  2

Product3  1

Product4  1

Product5  0

Product6  4

Product7  5

 

 

 

 

[Barand]

Sorry but "SELECT * " tells me nothing about what have in your table to start with and I don't know from your post whether you want

 

CustomerID  Product1  Product2  Product3  Product4  Product5  Product6  Product7 
      1             0             0            1             1            0           0              0 
      2             1             1            0             0            1           1              0
      3             1             1            1             0            0           1              3
      4             2             2            1             1            0           4              5

 

or

 

Product1   2
Product2   2
Product3   1
Product4   1
Product5   0
Product6   4
Product7   5

[PHP Monkeh]

This is pretty much just a basic query, result and fetch, with some simple layout displays in HTML.  Anyway I've posted the code below which should do what you want:

 

<?php

$query = "SELECT * FROM Products WHERE CustomerID = '4'";
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_object($result)) { ?>

<table width="">
<tr><td width="">

Product1

</td><td width="">

<?php echo $row->Product1; ?>

</td></tr>

--- REPEAT FROM HERE ---

<tr><td width="">

Product2

</td><td width="">

<?php echo $row->Product2; ?>

</td></tr>

--- TO HERE ---

</table>

<?php

}

?>

 

Obivously remove my --- REPEAT FROM HERE --- bits that was just to show you which bit to copy & paste so you can do it for Products 3-7.  Obviously change the tables to your spec, or to DIVs   But you get the general idea of how to do this I hope.

[telsiin]

Thank you for your reply

 

however my real problem is that my table has about 150 fields and some of them have a null value so I really need to make the table dynmic with only the field that have a value of one or greater.From customer to customer the table size in trems of "rows" would change as some customer have products that other don't

[Barand]

So are you saying your table looks like this,

CustomerID  Product1  Product2  Product3  Product4  Product5  Product6  Product7 
      1             0             0            1             1            0           0              0 
      2             1             1            0             0            1           1              0
      3             1             1            1             0            0           1              3
      4             2             2            1             1            0           4              5

 

with 150 product columns?

[telsiin]

That exactly what I am saying.

 

Now I know that’s not a conventional table but so you have a better idea of the whole picture or application, I have a second table that has the same 150 item in rows and NOT columns and another 15 fields that I use as reference or ”more like a guide” where every # 1 = a product that goes to that district and every # 0 = a product that does NOT go to that district. To make thing more complicated each store in the district can request a product that would not normally go to that district or discontinue selling a product that would normally go to that district

 

 

Product, North district, South district, West district, East district, Southeast district….etc

 

Product1 1 0 0 1 1

 

Product2 0 1 1 1 0

 

Product3 1 1 1 0 1

 

Product4 1 0 0 1 0

 

Product5 1 0 0 1 1

 

Product6 0 1 1 0 0

 

Product7 0 1 1 0 1

 

[Barand]

http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

[telsiin]

Thank you for the link but it had nothing to do with what I am trying to accomplish

 

I have gotten a bit closer with this code

 

?php require_once('../Connections/testing.php'); ?>
<?php

mysql_select_db($database_testing, $testing);
$query_Recordset1 = "SELECT * FROM Product where CustomerID='2'";


$Recordset1 = mysql_query($query_Recordset1, $testing) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalrows_Recordset1 = mysql_num_rows($Recordset1);




echo '<table width="15%" border="1" cellpadding="1" cellspacing="1">';
for ($i=0; $i < mysql_num_fields ($Recordset1); $i++) {
                       	echo "<tr><td>"; 
					echo ( mysql_field_name ($Recordset1, $i) );
                        echo "</td>";
                        $product = ( mysql_field_name ($Recordset1, $i));
	for ($x=0; $x < mysql_num_rows ($Recordset1); $x++) {
                       	echo "<td>"; 
					echo "$row_Recordset1[$product]"; 
				    echo "</td></tr>";
               }
		}	

echo "</table>";
?>

 

However I still need to select only those value  that are not null and have a 1 or greater number to show up in the table

[GingerRobot]

Actually, the link had everything to do with what you are trying to accomplish. You are storing your data in a very bad way. Sure, you might eventually get this particular problem sorted. But you will have great difficult each and every time you have to do something.

 

Thats not to mention that inefficiency of it all.

[GingerRobot]

However, if you insist on carrying on this way, then the following code should help:

 

<?php
$sql= "SELECT * FROM Product where CustomerID='2'";
$result = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_assoc($result)){
foreach($row as $k => $v){
	if($v != 0){
		echo $k. ' : '.$v.' <br />';
	}
}
}
?>

[telsiin]

GingerRobot Thank you

 

The code worked great

 

As I said before I know that data base was/is not normalized. However on several different forums I looked I could not find anything about converting rows to columns and since the scope of my project had to do with a just one table. I wasn't going to try to revamp the whole database. Of course in a future date when I am actually get paid for it. I might actual try to tackle that nightmare

 

Again Thank You Very Much