| help with sorting a mysql query
|
IJMcG
msg:4396834 | 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)
<div id="videoList">
<?php
require_once('config.php');
$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=\""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>";
}
mysql_close($con);
?>
</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?
THANKS!
<div title="<b>title: </b>funny video<br />
<b>user: ijmcg</b>">
<p title="description">
<span class=""title">funny video</span><br/><br/>blah blah blah blah<br/><br/>
<a href="users/ijmcg" target=""_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>
</div>
|
Habtom
msg:4396884 | 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.
|
IJMcG
msg:4396906 | 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?
|
rocknbil
msg:4397143 | 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.
|
Matthew1980
msg:4397192 | 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.
Happy coding!
Cheers,
MRb
|
|
|
