Welcome to WebmasterWorld Guest from

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

help with sorting a mysql query

1:58 am on Dec 12, 2011 (gmt 0)

New User

5+ Year Member

joined:Dec 12, 2011
votes: 0

okay so for my final project for my php class we have to build a video management system. I've got video upload, viewing, and user registration working, and the videos display themselves in my playlist using this code posted below. What I want to do is add a hyperlink above the code that when clicked by the user can sort the videos by a column in my mysql database and then display them in the format shown below (sort by views, or data posted, or alphabetically by title)

 <div id="videoList">



$con = mysql_connect(DB_HOST,DB_USER,DB_PASSWORD);
if (!$con)
die('Could not connect: ' . mysql_error());

mysql_select_db(DB_DATABASE, $con);

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

while($row = mysql_fetch_array($result))
echo "<div title=\"<b>title: </b>" . $row['title'] . "<br />";
echo "<b>user: </b>" . $row['username'] . "\">";
echo "<p title=\"description\"><span class=\"&quot;title\">" . $row['title'] . "</span><br/>";
echo "<br/>" . $row['info'] . "<br/>";
echo "<br/><a href=\"users/" . $row['username'] . "\" target=\"&quot;_blank\">visit my page</a></p>";
echo "<ul title=\"preview\"><li title=\"video/preview/" . $row['ogv_preview'] . "\">video/ogg</li>";
echo "<li title=\"video/preview/" . $row['mp4_preview'] . "\">video/mp4</li></ul>";
echo "<ul title=\"main\"><li title=\"video/main/" . $row['ogv_filename'] . "\">video/ogg</li>";
echo "<li title=\"main/preview/" . $row['mp4_filename'] . "\">video/mp4</li></ul>";
echo "<ul title=\"thumb\"><li title=\"video/thumb/" . $row['thumb'] . "\"></li></ul>";
echo "<ul title=\"poster\"><li title=\"video/poster/" . $row['poster'] . "\"></li></ul></div>";



this generates a piece of html code that looks like this. 1 block of code for each video in the mysql database. i just need to be able to sort this code, can anyone help me out?


<div title="<b>title: </b>funny video<br />
<b>user: ijmcg</b>">
<p title="description">
<span class="&quot;title">funny video</span><br/><br/>blah blah blah blah<br/><br/>
<a href="users/ijmcg" target="&quot;_blank">visit my page</a>
</p><ul title="preview">
<li title="video/preview/01.ogv">video/ogg</li>
<li title="video/preview/01.mp4">video/mp4</li></ul>
<ul title="main"><li title="video/main/01.ogv">video/ogg</li>
<li title="main/preview/01.mp4">video/mp4</li></ul>
<ul title="thumb"><li title="video/thumb/01.jpg"></li></ul>
<ul title="poster"><li title="video/poster/01.jpg"></li></ul>
5:03 am on Dec 12, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Nov 15, 2004
votes: 0

I've not seen all of your lines. But:

$result = mysql_query("SELECT * FROM video_list ORDER BY field_name");

Sorting is done on the query as shown above (replace the field_name with an actual field name on your table)

If you want to the field_name to be different based on your sorting need, you could simply replace field_name with a variable, and use a condition (perhaps if) to help you fill in the right field name in the query.
6:38 am on Dec 12, 2011 (gmt 0)

New User

5+ Year Member

joined:Dec 12, 2011
votes: 0

awesome! thank you!

One more question! what if I wanted to perform a simple search on the "title" column in my database? How would I go about doing that?
6:01 pm on Dec 12, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
votes: 0

As an exact value?
Anywhere in the string?
Starts with? Ends with?
Let the user decide any of these? (<----------- ! yes.)

You build a dynamic where clause and depending on which is chosen, use a combination or choice of = or the "like" keyword in mySQL. There are many examples on this board, but my first post in this thread [webmasterworld.com] hints at one approach, and is mentioned again here [webmasterworld.com]. Sorry don't have time to chase down any others.
7:56 pm on Dec 12, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member

joined:Feb 22, 2009
votes: 0

Nice post Rocknbil, always nice to be able to refer to archived posts on WebmasterWorld!

I shall offer a little advice here too: mysql_close(); Only use this function IF it's needed. Judging by your code, this maybe surplus to requrements as the natural last action of the script is to close the active connection - refer to mysql_close [uk3.php.net] on php.net (Second paragraph)

Also reading through some of the native mysql_ functions & how to structure queries within the sql engine will help you to find some helpful keywords that will make your code no end; may favourite one presently (though I do windows applications with vb.net) is DISTINCT & GROUP BY - adding things like this make your project customizable and tailor searches to how the user requests them, all it needs is a checkbox or radio buttons & you can do wonders with sql!

Sorry to witter on and kudos to Rocknil for pointing out his older posts.

Happy coding!