Working with two tables and grouping in mysql and php

Question

I have two tables like this.

    books
    =====
    b_id    a_id    book_name
    1       1       ABC
    2       1       DEF
    3       2       GHI


    authors
    =======

    a_id    author_name
    1       A1
    2       A2

I need a table like this.

        S.No    BookName
        -----------------

        A1
        ==
        1       ABC
        2       DEF


        A2
        ==

        1       GHI

What i'm planning to do is 1. Do a while loop and get the author name first and print it 2. Use the author id inside the first loop and do another one iteration to get the list of book list for each author.

A sample code is like this:

    <table cellpadding="0" cellspacing="0" border="0">
      <thead>
        <tr>
          <th>S.No</th>
          <th>Book Name</th>
        </tr>
      </thead>
      <?php
    $mysql_query1 = "my sql query to get the author name list first";
    $results = mysql_query ( $mysql_query1 ) or die ( mysql_error () );
    while($row = mysql_fetch_array($results)){
    ?>
      <tbody>
        <tr>
          <td style="background:none;"><u><?php echo $row['author_name']; ?></u></td>
        </tr>
        <?php
    $result = mysql_query ( "mysql query to get the list of books for each author with a where condition like this where a_id=$row['a_id']" ) or die ( mysql_error () );
    $book_count = mysql_num_rows($result);
    while($row1 = mysql_fetch_array($result)){
    ?>
        <tr>
          <?php for($i=1;$i<=$book_count;$i++){ ?>
          <td><?php echo $i; ?> </td>
          <td><?php echo $row1['book_name']; ?> </td>
          <?php } ?>
        </tr>
        <?php 
    }
    ?>
      </tbody>
      <?php } ?>
    </table>

My friends insisted me that the above method is a old one, and there is something to do with just few line codes. Is it?

If yes, could someone redefine my code and give me.

Thanks, Kimz

SQL in nested loops is extremely inefficient, and usually shows a lack of understanding of SQL (structured query language). You will want to do one query with a simple join. — Steve, Apr 07 '14 at 10:09
so, what are u trying to tell. i have explained clearly i'm new and still we ALL ARE UNDER LEARNING CURVE. right? — user3350885, Apr 07 '14 at 10:10
Sure, im not having a go, im saying that you should use a join to get all your data in one go. See here for good examples: http://stackoverflow.com/questions/12475850/how-can-an-sql-query-return-data-from-multiple-tables — Steve, Apr 07 '14 at 10:11

score 0 · Answer 1 · edited Apr 07 '14 at 10:33

0

Сan this be so?

$mysql_query1 = "select * from authors"

$result = mysql_query ( "select * from books where a_id=".$row['a_id']) or die ( mysql_error () );

edited Apr 07 '14 at 10:33

jmail

5,944
3
21
35

answered Apr 07 '14 at 10:21

Lexd5

56
6

This does not look like an answer. – Steve Apr 07 '14 at 10:22

score 0 · Answer 2 · edited May 25 '19 at 13:54

The PHP method would be something like:

//First let's get all the values we need and store them in array format
// so that we don't need to make expensive calls to the database
<?php
$result = mysql_query("SELECT author_name, book_name FROM books b, authors a WHERE b.a_id=a.a_id");
$arr = array();
while($row=mysql_fetch_assoc($query)){
      //storing books by author
      $arr[$row['author_name']][]=$row['book_name'];
    }
//so now we have an array like ['A1'=>'ABC','DEF'],['A2'=>'GHI']
?>
<table cellpadding="0" cellspacing="0" border="0">
  <thead>
    <tr>
      <th>S.No</th>
      <th>Book Name</th>
    </tr>
  </thead>
  <tbody>
    <?php
         //let's print out the contents of our array in table format
         foreach($arr as $author=>$val){
             echo "<tr>
                      <td style='background:none;'><u>".$author."</u></td>
                      <td></td>
                   </tr>";
             foreach($val as $ind=>$book_name)
             {
               echo "<tr>
                         <td>".$ind."</td>
                         <td>".$book_name."</td>
                    </tr>";
             }
         }
      ?>
  </tbody>
</table>

Note:

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO, or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

Working with two tables and grouping in mysql and php

2 Answers2

Note: