CODEWITHSUNDEEP
phpmysql
Subimal Sinha
Subimal Sinha

Reputation: 129

How to retrieve data from db in a desired format (PHP MYSQL)

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.

Upvotes: 2

Views: 1049

Answers (5)

Ahmed Jumaa Alsaket
Ahmed Jumaa Alsaket

Reputation: 1

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);
?>

Upvotes: 0

GajendraSinghParihar
GajendraSinghParihar

Reputation: 9131

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

Upvotes: 0

CE_REAL
CE_REAL

Reputation: 384

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>';

Upvotes: 2

Tigger
Tigger

Reputation: 9130

$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;
}

Upvotes: 0

Shoe
Shoe

Reputation: 76240

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++;
}

Upvotes: 1

Related Questions