I am facing a problem in retrieving data from db in a desired format. I have used PHP MYSQL. I am able to retrieve the records using while($row=mysql_fetch_array($result)); and so on... but not in a format as I want.
suppose, from db I am getting 10 records as 1 to 10 vertically, but I want the following format using table.
1 2 3 4
5 6 7 8
9 10
Note: No of records are dynamic in nature, and I want 4 columns in each row.
Please help me out. Thanks in advance.
You can't do this with MySQL, MySQL isn't used to nicely display your data, it's only used for storing and retrieving your data.
What you need is the following, whereas the $array can be replaced with your data from the database:
$array = array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
echo '<table>';
echo '<tr>';
for ($i = 0; $i < sizeof($array); $i++) {
if(($i % 4) == 0 && $i != 0) {
echo '</tr><tr>';
}
echo '<td>';
echo $array[$i];
echo '</td>';
}
echo '</tr>';
echo '</table>';
Having 4 records per row in the database is not changing anything. There's no such a feature in database because it serves no purpose and can be achieved using PHP instead. As stated in the comments databases are not designed for data representation.
You can instead retrieve those data and store them as group of fours later using a simple PHP script:
$i = 0;
$in = 0;
$array = array();
while($row = mysql_fetch_array($result)) {
if ($i%4) {
$in++;
}
$array[$in][] = $result;
$i++;
}
$data = ... your mysqli call that returns the data from mysql
$i = 0;
if ($data) {
$HTML = '<table><tr>';
foreach($data as $k => $v) {
$HTML .= '<td>'.$v.'</td>';
if ($i == 3) {
$i = 0;
$HTML .= '</tr><tr>';
}
$i++;
}
$HTML .= '</tr></table>';
echo $HTML;
}
As juergen d said SQL is not designed for data representation. Handle that in your logic
So you can
$array = array(1,2,3,4,5,6,7,8,9,10);
$i=0;
while($i < sizeof($array ) )
{
if($i%4 ==0 && $i>0) echo "<br>";
echo $array[$i++] ;
}
NOTE Replace $array with your array from DataBase
This is full example :) .. You need to create table "Category" in MYSQL with "Cname" column
<?php
require 'ConnDB.php';
$sql = "Select * From Category";
$ResShowCateg = mysqli_query($conn_link, $sql);
echo '<table>';
echo '<tr>';
for ($i = 0; $i < mysqli_num_rows($ResShowCateg); $i++) {
$row = mysqli_fetch_assoc($ResShowCateg);
if(($i % 3) == 0 && $i != 0){
echo '</tr><tr>';
}
echo '<td>';
echo $row["Cname"];
echo '</td>';
}
echo '</tr>';
echo '</table>';
mysqli_close($conn_link);
?>