MySQL and HTML: Ordering a row based on a specific value

Question

I'm working on a little downloads area of a website where people can submit different themes, and the community can rate them with a + or - 1 (very similar to Stack Overflow).

I currently am pulling data from a MySQL database - much of that isn't terribly important to the question - but here is what my final code and table looks like:

while($record = mysql_fetch_array($myData)){
    echo '<tr>';
    echo '<td>' . $record['votes'] . '</td>';
    echo '<td>' . $record['name'] . '</td>';
    echo '<td>' . $record['author'] . '</td>';
    echo '<td>' . $record['description'] . '</td>';
    echo '</tr>';
}

I would like to order each table row based on the value of the <td> called votes. I understand this is a bit broad since I don't have code supplied, but I've searched the web and found nothing about what I'm attempting to do. I'm sure it can be done by modifying the while function, but I'm not sure how.

If it proves to be any help, my full PHP code is below:

<?php
$con = mysql_connect('host','name','password');
mysql_select_db('database',$con);
$sql = "SELECT * FROM themer";
$myData = mysql_query($sql,$con);

echo '<table>';

while($record = mysql_fetch_array($myData)){
    echo '<tr>';
    echo '<td>' . $record['votes'] . '</td>';
    echo '<td>' . $record['name'] . '</td>';
    echo '<td>' . $record['author'] . '</td>';
    echo '<td>' . $record['description'] . '</td>';
    echo '</tr>';
}

echo '</table>';

?>

EDIT: I apparently was searching for the wrong stuff when I researched this - I didn't know mySQL had the functionality described in the answers below. Now that I know what was up, this might be a possible duplicate - mysql query order by multiple items

@ICanHasCheezburger, can't believe it was that easy. Thanks. — Alexander Lozada, Feb 24 '14 at 04:24
Its not your "while function" any way learn about ORDER BY clause in manuals of MySQL — Asim, Feb 24 '14 at 04:25

score 4 · Accepted Answer · edited May 23 '17 at 12:20

4

The simplest option is to pass the responsibility down to the database level. This is also most likely the best option since you can then add an index; database engines are quite good at querying and ordering results. Add an ORDER BY clause to your query, making your query:

SELECT *
FROM themer
ORDER BY votes

To order this from highest to lowest, you'll need to reverse the sort order. Do this by adding a DESC keyword:

SELECT *
FROM themer
ORDER BY votes DESC

Note: new code should really not be using the mysql_ PHP functions anymore. They are deprecated, and for good reason. Good alternatives include mysqli and PDO.

edited May 23 '17 at 12:20

Community

1
1

answered Feb 24 '14 at 04:20

lc.

113,939
20
158
187

Awesome, this did it. I'll also do a duplicate search since I think I was searching for the wrong thing. – Alexander Lozada Feb 24 '14 at 04:25
Konnichiwa! +1 for explaning about deprecated mysql_ maybe you should also suggest what to use. – AyB Feb 24 '14 at 04:27
Possible there's a dupe somewhere; I didn't look. I've linked to the MySQL documentation, it has a couple examples. You might want to pick up a book on SQL and database design/queries at some point as well. – lc. Feb 24 '14 at 04:28
@ICanHasCheezburger Good suggestion. Added a couple of links. – lc. Feb 24 '14 at 04:33

score 0 · Answer 2 · edited Feb 24 '14 at 04:35

0

$sql = 'SELECT * FROM themer 
        ORDER BY ABS(votes) DESC';

edited Feb 24 '14 at 04:35

AyB

11,609
4
32
47

answered Feb 24 '14 at 04:20

Mark Eriksson

1,455
10
19

MySQL and HTML: Ordering a row based on a specific value

2 Answers2