mysql query to display results in repeating columns

Question

I am trying to build a comparison table using mysql query and php.

I would like the result to be displayed in a columns, like this:

<table border="1" cellspacing="0" cellpadding="0">
  <tr>
    <td width="151" scope="col">product</td>
    <td width="89" scope="col">product1</td>
    <td width="78" scope="col">product2</td>
    <td width="77" scope="col">product3</td>
  </tr>
  <tr>
    <td>type</td>
    <td>type2</td>
    <td>type3</td>
    <td>type5</td>
  </tr>
  <tr>
    <td>size</td>
    <td>size2</td>
    <td>size1</td>
    <td>size4</td>
  </tr>
  <tr>
    <td>price</td>
    <td>4.99</td>
    <td>3.99</td>
    <td>3.59</td>
  </tr>
</table>

but I can only get the table to show the results - not a row title too (i.e. I want the first column to show 'product', 'type', 'size', 'price'.

The code I have so far is

    <?php
// query the database
$result = mysql_query($query_getproducts);

// cols we are interested in (from the SQL query)
$cols = array(
        'product',
    'type',
    'size',
    'price',
  );

// initialize rotated result using cols
$rotated = array();
foreach($cols as $col) {
  $rotated[$col] = array();
}

// fill rotated array
while(($row = mysql_fetch_assoc($result)) !== false) {
  foreach($cols as $col) {
    $rotated[$col][] = $row[$col];

  }
}

// echo html
echo "<table border=1 width=473>";
echo "<tr>";

echo "</tr>";
foreach($rotated as $col => $values) {
  echo "<tr>";

  foreach($values as $value) {
    echo "<td> " . htmlentities($value) . "</td>";
  }
  echo "</tr>";
}
echo "</table>";
?>

hope someone can help.

BenMorel · Accepted Answer · 2013-11-27 08:32:09Z

1

First of all mysql_* functions are deprecated. You should use PDO or Mysqli.

If you want table headers static ie you want to show table header as "Product,Type,Size, Price" Then use

<tr>
<th>Product</th>
<th>Type</th>
<th>Size</th>
<th>Price</th>
</tr>

Then if your should use mysql_fetch_assoc which returns associative array with column name as there key. You can use that array and print the result using loop. eg:

<?php
$rs=mysql_query($query);
while($row=mysql_fetch_assoc($rs) ){
?>
<tr>
<td><?php echo $row['keyname']?></td>
.....
.....
</tr>
<?php
}
?>

edited Nov 27, 2013 at 8:32

BenMorel

37k52 gold badges208 silver badges339 bronze badges

answered Feb 18, 2013 at 10:10

Rohit Choudhary

2,2571 gold badge24 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Php Geek · Accepted Answer · 2013-02-19 03:56:17Z

0

try like this ,

echo "<table border=1 width=473>";
echo "      <tr>
    <th>Product Name</th>
    <th>Description</th>
    <th>Product Size</th>
    <th>Price</th>
    </tr>";
foreach($rotated as $col => $values) {
 echo "<tr>";

 foreach($values as $value) {
    echo "<td> " . htmlentities($value) . "</td>";
  }
  echo "</tr>";
}
echo "</table>";

edited Feb 19, 2013 at 3:56

answered Feb 18, 2013 at 10:04

Php Geek

1,1071 gold badge15 silver badges38 bronze badges

2 Comments

BEN HALL Over a year ago

thanks -is it possible to put something other than the mysql column title in the first column- e.g. so although the mysql title is say 'productSize', the table would show 'Size'. ??

BEN HALL Over a year ago

ok, so I have changed 'echo $values' to 'echo $col;' and this successfully put the cols array in the first column. But I would like to manually change these values to something else. For example, my first mysql column title is in fact 'productName', so ideally I would want, say, 'Product Name' in my first column. How do I do this instead for this and the other variables? I have tried defining a new array as $titles = array('product Name','description','product size','price', ); and in the echo script, write "$titles" but it just writes the word 'array' in the first column.

Collectives™ on Stack Overflow

mysql query to display results in repeating columns

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related