Welcome to WebmasterWorld Guest from 54.147.44.93

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

help with sorting a mysql query

     

IJMcG

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

Habtom

5:03 am on Dec 12, 2011 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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

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

6:01 pm on Dec 12, 2011 (gmt 0)

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



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

7:56 pm on Dec 12, 2011 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



^^
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
 

Featured Threads

Hot Threads This Week

Hot Threads This Month