CODEWITHSUNDEEP
phphtmlmysqlwhile-loop
user3350885
user3350885

Reputation: 749

Handling while loop and grouping MYSQL PHP Values

I have a table like this.

    author_id   author_book     rating  
    1           ABC             5
    1           DEF             6
    2           PPP             8
    3           FFF             9

Here is my PHP Code

    <table>
    <tr><td>Author ID</td><td>Author Book</td><td>Rating</td></tr>
    <?php
    $row_data = mysql_query("select * from author_master" );
    while($row = mysql_fetch_array($row_data) ) {
    ?>
    <tr>
    <td><?php echo $row['author_id']; ?></td>
    <td><?php echo $row['author_book']; ?></td>
    <td><?php echo $row['rating']; ?></td>
    </tr>
    <?php } ?>
    </table>

What I would like to do is group the similar author in the same row.

The output should look like this

    Author ID               Author Book         Rating
    1                       ABC                 5
                            DEF                 6

    2                       PPP                 8

    3                       FFF                 9

Upvotes: 1

Views: 1834

Answers (5)

Kickstart
Kickstart

Reputation: 21533

Just check for a change of value:-

<?php

$prev_author_id = null;

?>
<table>
<tr><td>Author ID</td><td>Author Book</td><td>Rating</td></tr>
<?php
$row_data = mysql_query("SELECT * FROM author_master ORDER BY author_id   author_book" );
while($row = mysql_fetch_array($row_data) ) 
{
    echo "<tr>";
    echo "<td>".(($prev_author_id != $row['author_id']) ? $row['author_id'] : '&nbsp;'."</td>";
    echo "<td>".$row['author_book']."</td>";
    echo "<td>".$row['rating']."</td>";
    echo "</tr>";
    $prev_author_id = $row['author_id'];
} 
?>
</table>

As long as the rows returned are ordered this saves storing a potentially large amount of data before outputting it.

Upvotes: 0

hindmost
hindmost

Reputation: 7205

You have to split your code into two separate parts: DB fetching and output of fetched data. Furthemore you have to group fetched data by 'author_id' key.

Somehow like this:

<table>
<tr><td>Author ID</td><td>Author Book</td><td>Rating</td></tr>
<?php
$result = array();
$row_data = mysql_query("select * from author_master" );
while($row = mysql_fetch_array($row_data) ) {
    $id = $row['author_id'];
    if (!isset($result[$id])) $result[$id] = array('author_book' => array(), 'rating' => array());
    array_push($result[$id]['author_book'], $row['author_book']);
    array_push($result[$id]['rating'], $row['rating']);
}

foreach ($result as $id => $data) {
?>
    <tr>
    <td><?php echo $id; ?></td>
    <td><?php echo implode('<br>', $data['author_book']); ?></td>
    <td><?php echo implode('<br>', $data['rating']); ?></td>
    </tr>
<?php
}
?>
</table>

Upvotes: 0

Sid M
Sid M

Reputation: 4364

Try this

<table>
    <tr><td>Author ID</td><td>Author Book</td><td>Rating</td></tr>
    <?php
    $row_data = mysql_query("select * from author_master group by author_id" );
    $temp=0;
    while($row = mysql_fetch_array($row_data) ) {
    if($temp==0)
    {
      $temp = $row['author_id'];
    }
    ?>
    <tr>
    <td><?
    if($temp != $row['author_id'])
    {
      $temp = $row['author_id'];
      php echo $temp; 
    }
    ?></td>
    <td><?php echo $row['author_book']; ?></td>
    <td><?php echo $row['rating']; ?></td>
    </tr>
    <?php } ?>
    </table>

Upvotes: 1

user1978142
user1978142

Reputation: 7948

Try to avoid mysql_* functions. Use mysqli_*. You just need to group them first. Consider this example:

<?php

$link = new mysqli('localhost', 'test', 'test', 'test');
$query = mysqli_query($link, 'SELECT * FROM author_master ORDER BY author_id');
$books = array();
while($row = $query->fetch_assoc()) {
    $books[$row['author_id']][] = $row;
}
?>

<table border="0" cellpadding="10">
    <thead>
        <tr>
            <th>Author ID</th>
            <th>Author Book</th>
            <th>Rating</th>
        </tr>
    </thead>
    <tbody>
        <?php foreach($books as $author_id => $values): ?>
            <tr>
                <td><?php echo $author_id; ?></td>
                <td><?php foreach($values as $author_book) {echo $author_book['author_book'] . '<br/>';} ?></td>
                <td><?php foreach($values as $author_book) {echo $author_book['rating'] . '<br/>';} ?></td>
            </tr>
        <?php endforeach; ?>
    </tbody>
</table>

Upvotes: 3

Dipak Kumar Pusti
Dipak Kumar Pusti

Reputation: 1723

<?php
$author = 0;
$row_data = mysql_query("select * from author_master" );
while($row = mysql_fetch_array($row_data) ) {
    if($author = $row['author_id']) {
        $display = "";
    } else {
        $display = $row['author_id'];
    }
    ?>
    <tr>
        <td><?php echo $display; ?></td>
        <td><?php echo $row['author_book']; ?></td>
        <td><?php echo $row['rating']; ?></td>
    </tr>
    <?php 
    $author = $row['author_id'];
} 
?>

Upvotes: 0

Related Questions