homepage Welcome to WebmasterWorld Guest from 54.161.166.171
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

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




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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved