Reformatting Data

[poorleno]

Im hoping someone could help me with this and point me in the right direction

I have a database table called reports which contains the fields “engine” “keyword” and “position” which contains data as below:

Engine keyword Position
Google holiday 1
Altavista holiday 3
Yahoo holiday 11
Google holiday offers 3
Altavista holiday offers 31
Yahoo holiday offers 13

What i want to do is display the information in a html table like:

Keyword Google Altavista Yahoo
holiday 1 3 11
holiday offers 3 31 13

the list of ‘different’ engines could grow so the outputted table would need to grow horizontally.

Help appreciated

[Ilyes]

<?php
$con = mysql_connect("localhost","username","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("db_name", $con);

$result = mysql_query("SELECT * FROM reports");

while($row = mysql_fetch_array($result))
  {
  echo $row['engine'] . " " . $row['keyword'] . " " . $row['position'];
  echo "<br />";
  }

mysql_close($con);
?>

I hope this will help you enough.

[illzz]

The reply that Ilyes posted will just display each row as it is in the database, not how OP requested.

Here is one possible solution, although there may be a more efficient way to do this...

 <?php
$con = mysql_connect("localhost","username","password");
if (!$con) {
    die('Could not connect: ' . mysql_error());
}

mysql_select_db("db_name", $con);

$result = mysql_query("SELECT engine, keyword, position FROM reports");

// Initialize the arrays (in case they are defined above)
$display  = array();
$engines  = array();
$keywords = array();

while($row = mysql_fetch_array($result)) {
    if (!in_array($row['engine'], $engines)) {
        $engines[]  = $row['engine'];
    }
    if (!in_array($row['keyword'], $keywords)) {
        $keywords[] = $row['keyword'];
    }
    $display[$row['engine']][$row['keyword']] = $row['position'];
}
mysql_close($con);

// Display results in a table
echo '<table>';
echo '<tr>';
echo '<th>Keyword</th>';
foreach ($engines as $engine) {
    echo '<th>';
    echo $engine;
    echo '</th>';
}
echo '</tr>';
foreach ($keywords as $keyword) {
    echo '<tr>';
    echo '<td>';
    echo $keyword;
    echo '</td>';
    foreach ($engines as $engine) {
        echo '<td>';
        echo $display[$engine][$keyword];
        echo '</td>';
    }
    echo '</tr>';
}
echo '</table>';
?>