PHP MYSQL - cross result from database extrapolation

Question

From a given database I need to extrapolate the results in this way:

LINES contain the types of sport brands e.g. Nike, Reebook, etc.
COLUMNS contain the stores e.g. store 1, store 2, store 3, etc.

As a "cross" result, the table must show the remaining sizes per store/brand.

see following example:

<!DOCTYPE html>
<html>

<head>
<style>
table, th, td {
    border: 1px solid black;
    border-collapse: collapse;
}
th, td {
    padding: 5px;
    text-align: left;    
}
</style>
</head>

<body>

<table style="width:100%">
  <tr>
    <th>#</th>
    <td>Store 1</td>
    <td>Store 2</td>
    <td>Store 3</td>
    <td>Store 4</td>
    <td>Store n</td>
  </tr>
  <tr>
    <th rowspan="5">Sport Brand 1</th>
    <td>Remaining Size 1</td>
    <td>Remaining Size 3</td>
    <td>Remaining Size 3</td>
    <td>Remaining Size 2</td>
    <td>Remaining Size 1</td>
  </tr>
  <tr>
    <td>Remaining Size 2</td>
    <td>Remaining Size 4</td>
    <td>Remaining Size 4</td>
    <td>Remaining Size 3</td>
    <td>Remaining Size 2</td>
  </tr>
  <tr>
    <td>Remaining Size 3</td>
    <td>Remaining Size n</td>
    <td></td>
    <td></td>
    <td>Remaining Size 3</td>
  </tr>
  <tr>
    <td>Remaining Size 4</td>
    <td></td>
    <td></td>
    <td></td>
    <td>Remaining Size 4</td>
  </tr>
  <tr>
    <td>Remaining Size n</td>
    <td></td>
    <td></td>
    <td></td>
  </tr>
  <tr>
    <th rowspan="5">Sport Brand 2</th>
    <td>Remaining Size 1</td>
    <td>Remaining Size 3</td>
    <td>Remaining Size 3</td>
    <td>Remaining Size 2</td>
    <td>Remaining Size 1</td>
  </tr>
  <tr>
    <td>Remaining Size 2</td>
    <td>Remaining Size 4</td>
    <td>Remaining Size 4</td>
    <td>Remaining Size 3</td>
    <td>Remaining Size 2</td>
  </tr>
  <tr>
    <td>Remaining Size 3</td>
    <td>Remaining Size n</td>
    <td></td>
    <td></td>
    <td>Remaining Size 3</td>
  </tr>
  <tr>
    <td>Remaining Size 4</td>
    <td></td>
    <td></td>
    <td></td>
    <td>Remaining Size 4</td>
  </tr>
  <tr>
    <td>Remaining Size n</td>
    <td></td>
    <td></td>
    <td></td>
  </tr>
  <tr>
    <th rowspan="5">Sport Brand 3</th>
    <td>Remaining Size 1</td>
    <td>Remaining Size 3</td>
    <td>Remaining Size 3</td>
    <td>Remaining Size 2</td>
    <td>Remaining Size 1</td>
  </tr>
  <tr>
    <td>Remaining Size 2</td>
    <td>Remaining Size 4</td>
    <td>Remaining Size 4</td>
    <td>Remaining Size 3</td>
    <td>Remaining Size 2</td>
  </tr>
  <tr>
    <td>Remaining Size 3</td>
    <td>Remaining Size n</td>
    <td></td>
    <td></td>
    <td>Remaining Size 3</td>
  </tr>
  <tr>
    <td>Remaining Size 4</td>
    <td></td>
    <td></td>
    <td></td>
    <td>Remaining Size 4</td>
  </tr>
  <tr>
    <td>Remaining Size n</td>
    <td></td>
    <td></td>
    <td></td>
  </tr>
  <tr>
    <th rowspan="5">Sport Brand 4</th>
    <td>Remaining Size 1</td>
    <td>Remaining Size 3</td>
    <td>Remaining Size 3</td>
    <td>Remaining Size 2</td>
    <td>Remaining Size 1</td>
  </tr>
  <tr>
    <td>Remaining Size 2</td>
    <td>Remaining Size 4</td>
    <td>Remaining Size 4</td>
    <td>Remaining Size 3</td>
    <td>Remaining Size 2</td>
  </tr>
  <tr>
    <td>Remaining Size 3</td>
    <td>Remaining Size n</td>
    <td></td>
    <td></td>
    <td>Remaining Size 3</td>
  </tr>
  <tr>
    <td>Remaining Size 4</td>
    <td></td>
    <td></td>
    <td></td>
    <td>Remaining Size 4</td>
  </tr>
  <tr>
    <td>Remaining Size n</td>
    <td></td>
    <td></td>
    <td></td>
  </tr>
  <tr>
    <th rowspan="5">Sport Brand n</th>
    <td>Remaining Size 1</td>
    <td>Remaining Size 3</td>
    <td>Remaining Size 3</td>
    <td>Remaining Size 2</td>
    <td>Remaining Size 1</td>
  </tr>
  <tr>
    <td>Remaining Size 2</td>
    <td>Remaining Size 4</td>
    <td>Remaining Size 4</td>
    <td>Remaining Size 3</td>
    <td>Remaining Size 2</td>
  </tr>
  <tr>
    <td>Remaining Size 3</td>
    <td>Remaining Size n</td>
    <td></td>
    <td></td>
    <td>Remaining Size 3</td>
  </tr>
  <tr>
    <td>Remaining Size 4</td>
    <td></td>
    <td></td>
    <td></td>
    <td>Remaining Size 4</td>
  </tr>
  <tr>
    <td>Remaining Size n</td>
    <td></td>
    <td></td>
    <td></td>
  </tr>
</table>

</body>
</html>

<?php
$dsn = 'mysql:dbname=name;host=localhost;charset=utf8';
$user = 'root';
$password = 'password';
$options = array(
PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
); 

try {
$db = new PDO($dsn, $user, $password, $options);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}

// query
$sql = $db->prepare ('SELECT * FROM table ORDER BY id ASC');
$sql->execute();

foreach($sql as $row)   {
    $Store = ($row['store']);
    $SportBrand = ($row['SportBrand']);
    $RemainingSize = ($row['RemainingSize']);

How can I handle this results in HTML/PHP in order to have them as the above example ?

Refer this link [mysql pivot query](http://stackoverflow.com/questions/12382771/mysql-pivot-crosstab-query) — adarsh hota, Jan 30 '15 at 11:16
@adarshhota thanks for your answer but there, if I'm not in mistake, I have the count of results. — Olivia S., Jan 30 '15 at 11:53

PHP MYSQL - cross result from database extrapolation

0 Answers0