output database in an ordered list

Question

I have this table in my MySQL database (just a sample)

+----+--------------+---------+--------+-----------+
| id | name         | place   | number | type      |
+----+--------------+---------+--------+-----------+
| 1  | Banana       | farm    | 100000 | fruit     |
| 2  | Apple        | park    | 100000 | fruit     |
| 3  | Eggplant     | street  | 500    | vegetable |
| 4  | Bitter Gourd | village | 2000   | vegetable | 
+----+--------------+---------+--------+-----------+

...

I fetch the data to my webpage using PHP and i want it to show in an ordered list by type like this.

fruit
- banana | farm | 100000
- apple | park | 100000
vegetable
- eggplant | street | 500
- bitter gourd | village | 2000

Can anyone help me with the code, there are many types in my database. I have been able to fetch the data from the database to my webpage as is.

Using this code, but I want to output the data the same way above.

<?php

$result = mysql_query("SELECT * FROM table;");

while($row = mysql_fetch_array($result)) {
    $id = $row['id'];   
    $name = $row['name'];
    $type = $row['type'];   

    echo "
        <tr>
            <td>$id</td>
            <td>$name</td>
            <td>$type</td>
        </tr>
"; 

?>

thanks Dagon for answering, i am working on it now. also thanks to Leushenko for editing the question — jonas, Aug 04 '14 at 02:49

Mohit S · Accepted Answer · 2014-08-04T03:20:07.467

4

May be this will help you

<?php
$result = mysql_query("SELECT DISTINCT(type) FROM table");
while($row=mysql_fetch_array($result))
{
    echo "<ul>
            <li>$row['type']
                <ul>";
    $result1 = mysql_query("SELECT * FROM table WHERE type=$row['type']");
    while($row1=mysql_fetch_array($result1))
    {
        echo "<li>$row1['name'] | $row1['place'] | $row1['number']</li>";
    }
    echo "</ul></li></ul>"
?>

Whereas MYSQL are deprecated function please use MYSQLI_* Functions

edited Aug 04 '14 at 03:20

answered Aug 04 '14 at 03:10

Mohit S

13,723
6
34
69

one more question. whats the difference between using MYSQL and MYSQLI? @Mohit – jonas Aug 04 '14 at 03:53
Basically it helps to prevent you from [SQL Injection](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). – Mohit S Aug 04 '14 at 03:58

score 2 · Answer 2 · answered Aug 04 '14 at 02:39

2

Personally I prefer to reorganize the data in the way I'm going to display it first, which makes the code to loop over the results and display them more readable, for example:

<?php
$result = mysql_query("SELECT * FROM table ORDER BY number;");

$produceByType = array();

while ($row = mysql_fetch_array($result)) {
    $produceByType[ $row['type'] ][] = $row;
}
?>

<table>
<?php
foreach ($produceByType as $type => $produce):
?>
    <tr>
        <th colspan="3"><?= $type ?></th>
    </tr>
    <?php
    foreach ($produce as $row): ?>
        <tr>
            <td><?= $row['name'] ?></td>
            <td><?= $row['place'] ?></td>
            <td><?= $row['number'] ?></td>
        </tr>
    <?php
    endforeach;
endforeach; ?>
</table>

answered Aug 04 '14 at 02:39

Matt Browne

12,169
4
59
75

thanks Matt will working on it now. do you have other simpler way to do it? – jonas Aug 04 '14 at 02:50
@sbjumani's answer is arguably a simpler way to do it. I just edited what looked like a bug in that answer so try it again and see if it works for you. I still prefer my way of doing it but then again I'm a more experienced PHP developer so I can see how the greater total amount of code might make it less desirable to someone newer to PHP. Note that for more complex situations my approach would actually lessen the total amount of code, and make it more readable. – Matt Browne Aug 04 '14 at 02:56
ok thanks Matt. will try this now, will give you feedback after. – jonas Aug 04 '14 at 02:58

score 1 · Answer 3 · edited Aug 04 '14 at 02:54

1

Add an order by clause

select * from table order by type, name;

Next, take the type into a variable. Check the type against the new type of the new row in the result. If it is same, then render the new row, else close the row and insert new.

<?php
$result = mysql_query("SELECT * FROM table order by type, name;");
$oldtype = "";
while($row=mysql_fetch_array($result))
{

    $id = $row['id'];   
    $name = $row['name'];
    $type = $row['type'];   

    if ($type != $oldtype)
    {
        echo "
            <tr>
                <td>$type</td>
            </tr>
        ";
        $oldtype = $type;
    }

    echo "
            <tr>
                <td>$name</td>
            </tr>
    ";
    } ?>

I did not run the code but the logic is correct.

Please use css to apply the style to the values for indentation.

edited Aug 04 '14 at 02:54

Matt Browne

12,169
4
59
75

answered Aug 04 '14 at 02:26

sbjumani

124
8

thanks sbjumani. I got your logic. But when I tried the code the output looks like this. *fruit banana | farm | 100000 apple | park | 100000 eggplant | street | 500 bitter gourd | village | 2000 – jonas Aug 04 '14 at 02:53
the vegetable word does not output – jonas Aug 04 '14 at 02:55
Thanks for edit. The answer that was accepted, wouldn't that lead to multiple queries rather than one call for the data? Which approach would cause slower response? – sbjumani Aug 04 '14 at 03:46

Sunil Khiatani · Answer 4 · 2014-08-04T03:24:36.933

SQL allows you to sort your data using the 'ORDER BY' clause

For example to order your data by the number/quantity you do

SELECT * FROM table ORDER BY number;

This will sort the results by quantity from smallest or largest. Also,

SELECT * FROM table ORDER BY number DESC;

will sort the results by quantity from largest to smallest.

In your case it seems you want to sort by them then the quantity. In that case you can do

SELECT * FROM table ORDER BY type ASC, number DESC;

Depending on what you choose, modify the SQL query in the line accordingly. E.g.:

$result = mysql_query("SELECT * FROM table;");

to

$result = mysql_query("SELECT * FROM table ORDER BY type ASC, number DESC;");

thanks Sunil for answering. I know the order by method in SQL. what I wanted to know is the code on how i can output the data in my webpage using an orderd list using PHP. — jonas, Aug 04 '14 at 02:51

output database in an ordered list

4 Answers4