Welcome to WebmasterWorld Guest from 54.166.37.177

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
posts:2
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">

<?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=\"&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>";
}

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="&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>
</div>
5:03 am on Dec 12, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Nov 15, 2004
posts:1867
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
posts:2
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
posts:7999
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
posts:1396
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!

Cheers,
MRb
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members