|help with sorting a mysql query|
| 1:58 am on Dec 12, 2011 (gmt 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)
$con = mysql_connect(DB_HOST,DB_USER,DB_PASSWORD);
die('Could not connect: ' . mysql_error());
$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=\""title\">" . $row['title'] . "</span><br/>";
echo "<br/>" . $row['info'] . "<br/>";
echo "<br/><a href=\"users/" . $row['username'] . "\" target=\""_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 />
<span class=""title">funny video</span><br/><br/>blah blah blah blah<br/><br/>
<a href="users/ijmcg" target=""_blank">visit my page</a>
<ul title="main"><li title="video/main/01.ogv">video/ogg</li>
<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)|
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)|
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)|
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)|
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.