Split MySQL results across multiple tables

[jonnyuk3]

Hi,

 

I'm not sure if this is possible but hoping someone in here will say it is and show me how.

 

I have a list of suplliers returned from my database, fields include:

 

account name

address 1

address 2

post code and region.

 

I would like to split the data into multiple tables by region and have a header on each table with the region name.

 

This is what i currently have, but that just shows each supplier in one long table.

 

$query  = "SELECT * FROM product_stockist WHERE $cat='Y' ORDER BY Region";

$result = mysql_query($query);

 

$no_of_rows = mysql_num_rows($result);

 

if($no_of_rows > 0){

    echo "<table width=100% align=center callpadding=2 cellspacing=0>";

    echo "</tr>";

}

 

while($row = mysql_fetch_array($result, MYSQL_ASSOC))

{

    echo "<tr>";

    echo "<td>{$row['Account_Name']} </td>".

        "<td>{$row['Address_Line_1']} </td>".

        "<td>{$row['Address_Line_2']} </td>".

        "<td>{$row['Address_Line_4']} </td>".

        "<td>{$row['Post_Code']} </td>".

        "<td>{$row['Region']} </td>";

    echo "</tr>";

}

 

echo "</table";

[jl5501]

You need some logic to detect when region has changed, and close the table and open another when this happens

[taquitosensei]

you could do something like this

 

$query  = "SELECT * FROM product_stockist WHERE $cat='Y' ORDER BY Region";

$result = mysql_query($query);

while($row = mysql_fetch_array($result, MYSQL_ASSOC))

{

$results[$region]=$row;

}

 

echo "<table>";

foreach($results as $regionname=>$region)

{

    echo "<tr><td>";

    echo "<table width=100% align=center callpadding=2 cellspacing=0>";

    echo "<tr><td colspan='5' align='center'>$regionname</td></tr>";

    foreach($region as $row)

    {

          echo "<tr>";

    echo "<td>{$row['Account_Name']} </td>".

        "<td>{$row['Address_Line_1']} </td>".

        "<td>{$row['Address_Line_2']} </td>".

        "<td>{$row['Address_Line_4']} </td>".

        "<td>{$row['Post_Code']} </td>".

        "<td>{$row['Region']} </td>";

    echo "</tr>"; 

 

    }

    echo "</td></tr></table>";

}

echo "</table>";