How to display data from MySQL

Question

I created an Online Time Table and it's working fine and user or me can add new time-table row by using some data and my end result is like.

This is my SQL query (Please don't warn me to use MySQLi or PDO, I know about them, but this is a private application and I can write unsafe codes thought.)

 $date = date("d/m/y");
    $sql = "SELECT * FROM `s_timetable` WHERE `date` = '$date'";
    $res = mysql_query($sql) or die(mysql_error());

     while ($fetch_data = mysql_fetch_array($res)) {
        echo "<tr>";
        echo "<td>" .$fetch_data['batch_info']. "</td>";
        echo "<td>" .$fetch_data['class_info']. "</td>";
        echo "<td>" .$fetch_data['subject_info']. "</td>";
        echo "<td>" .$fetch_data['teacher_info'].  "</td>";
        echo "</tr>";
     }
   ?>

but after querying this to database, I got lots of results with same value, how can i show them as group.
Example:
LDC batch - 08:09 - science - Ashok B.
LDC batch - 09-10 - English - Jaky
LDC batch - 10-11 - Maths - Bella

Now how i can display these data as table, with title of LDC batch as that repeat in all and then time subject and teacher info with table body.

@HimanshuPatel Yaa but there will be several different batches, for example if there is 10 different batch with 5-10 row in each about class, subject, teacher. SO in this example there will be total 50 rows and in all batch name will be repeated, i just want to show batch name as title or head of table and results specific to that row under table body, but problem is how could i do that. — Jaky
– Jaky, Commented Sep 13, 2016 at 11:18
learn about mysql group by and you will get the values already grouped — user2399035
– user2399035, Commented Sep 13, 2016 at 11:22
@LelioFaieta I tried this query earlier but I don't know how to display that value. SELECT * FROM s_timetable` WHERE date = '$date' GROUP BY batch_info; — Jaky
– Jaky, Commented Sep 13, 2016 at 11:28

Tim Biegeleisen · Accepted Answer · 2016-09-13 11:24:38Z

4

One option here is to present a 3 column HTML table containing the time, subject, and teacher data for each batch. When the batch type changes in the result set, a new row can be printed as a header.

$header = "";
while ($fetch_data = mysql_fetch_array($res)) {
    if ($header = "" || $header != $fetch_data['batch_info']) {
        echo "<tr><td colspan=\"3\">".$fetch_data['batch_info']."</td></tr>";
        $header = $fetch_data['batch_info'];
    }
    echo "<tr>";
    echo "<td>" .$fetch_data['class_info']. "</td>";
    echo "<td>" .$fetch_data['subject_info']. "</td>";
    echo "<td>" .$fetch_data['teacher_info'].  "</td>";
    echo "</tr>";
 }

Update:

As @apokryfos pointed out, you should order your result set by the batch_info column to ensure that records belonging to the same batch are always adjacent. So your query might become:

SELECT *
FROM s_timetable
WHERE date = '$date'
ORDER BY batch_info

edited Sep 13, 2016 at 11:24

answered Sep 13, 2016 at 11:17

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

apokryfos Over a year ago

I'd also order by batch_info to be on the safe side

Jaky Over a year ago

@apokryfos I already tried that but it looks like not working in this problem, can you share codes of your approach.

user2399035 Over a year ago

@Jaky what do you mean as "non working"? it's just a order by batch_info at the end of your query

Jaky Over a year ago

@LelioFaieta I mean when i added order by batch_info i just got 2-2 rows in each instead of all rows in single batch.

Jaky Over a year ago

@apokryfos Why I'm only getting 2-2 rows instead of all (in some batch around 8-10) rows in a single batch table.

|

Collectives™ on Stack Overflow

How to display data from MySQL

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related