CODEWITHSUNDEEP
phpmysqlhtml-table
bbidadi
bbidadi

Reputation: 85

How to group head by state and 3 city per row in PHP mysql

I have a mysql table with state, city.

My query goes like this:

$result = mysqli_query($con, "select *  from " . $loc_tbl . "  order by city ASC");

echo "<html><head></head><body> 
<table>"; 

$num_columns = 3;
$num_rows = mysqli_num_rows($result);
$i=0;
while($row = mysqli_fetch_assoc($result)){
    $results[$i] = $row['city'];
    $i++;
}
unset($i);
$k=0;
for ($i=0;$i<=($num_rows/($num_columns+1));$i++){
    echo '<tr>';

for($j=1;$j<=$num_columns;$j++){
    echo '<td>'.$results[$k].'</td>';
    $k++;
}

echo '</tr>';
$k++;
}
echo "</table></body>
</html>";

Would like to display state as header

              California
---------------------------------------
San Jose | Santa Clara | Palo Alto
---------------------------------------
Los Angeles | Orange County | Los Gatos
---------------------------------------

              New Jersey
---------------------------------------
Morristown | Union | Summit
---------------------------------------
Newark | Parsipenny | Atlantic City

Am able to spread cities across 3 columns each rows

I have problem with STATE as ROW HEADER

Any help is appreciated

Upvotes: 1

Views: 119

Answers (1)

Shekhar Bhise
Shekhar Bhise

Reputation: 145

Please try this -

Steps:

1) Convert row data into array such that state will be key having value as array of cities

2) Display with respect to value defined for $num_columns

$result = mysqli_query($con, "select *  from " . $loc_tbl . "  order by city ASC");
$num_columns = 3;

/* Step 1 :
 * $rows will be array having
 * State as key and array of cities as value
 */
$rows = array();
while($row = mysqli_fetch_assoc($result)){
    if(!isset($rows[$row['state']])){
        $rows[$row['state']] = array();
    }
    $rows[$row['state']][] = $row['city'];
}


/* Step 2 :
 * Following table will have columns with respect to value defined for  $num_columns
 */
echo "<table>";
foreach($rows as $state => $cities){
    echo '<tr><th colspan="'. $num_columns .'">'. $state .'</th></tr>';
    $cityChunks = array_chunk ($cities, $num_columns);   // split array into chunk of $num_columns cities per array
    foreach($cityChunks as $row){
        echo "<tr>";
        for($i=0; $i<$num_columns; $i++){
            $city = isset($row[$i]) ? $row[$i] : "";
            echo "<td>$city</td>";
        }
        echo "</tr>";
    }
}
echo "</table>";

Upvotes: 2

Related Questions