Allow user to sort data?

wikedawsum · January 29, 2008

I have created a blog with an Editor's section to allow editors to view, edit, and publish blogs. I am looking for a way to allow the editor to sort stories by section, or published date. Currently, my query orders the results by published date.

This is the code for the page I would like to be able to sort:

<?php

// include function files for this application
require_once('include_fns.php'); 

session_start();

if (isset($_SESSION['auth_user'])){

$conn = mysql_connect("*****", "*****", "*****") 
   or die($msg_no_connect);
   mysql_select_db("*****") 
   or die(mysql_error());
   
   check_valid_user('');

  // Run query
$sql = "SELECT * FROM stories order by published";
    $res = mysql_query($sql);
    if(!$res){
     $err=mysql_error();
     echo $err;
     exit();
    }
}
else {
echo 'You are not authorized to view this page.';
}

?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>The Pink Compass</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link rel="stylesheet" type="text/css" href="../../css/style.css">
<!---if IE---->
<link rel="stylesheet" type="text/css" href="../../css/ie.css">
<!---end IF---->

<script type="text/javascript"><!--//--><![CDATA[//><!--

startList = function() {
if (document.all&&document.getElementById) {
	navRoot = document.getElementById("nav");
	for (i=0; i<navRoot.childNodes.length; i++) {
		node = navRoot.childNodes[i];
		if (node.nodeName=="LI") {
			node.onmouseover=function() {
				this.className+=" over";
			}
			node.onmouseout=function() {
				this.className=this.className.replace(" over", "");
			}
		}
	}
}
}
window.onload=startList;

//--><!]]></script>

</head>

<body>

<div id="top_bar"></div>

<div id="wrapper">

<div id="container">

	<div id="header">

		<div id="menu">
			<ul id="nav">
				<li><div><a href=""><img src="../../images/menu_top.png"></a></div>
					<ul>
						<li><a href="../../index.php">Home Page</a></li>
						<li><a href="../../pages/about.php">About Us</a></li>
						<li><a href="../../pages/charity.php">Charity Corner</a></li>
						<li><a href="../../pages/events.php">Upcoming Events</a></li>
						<img src="../../images/menu_hr.png">
						<li><a href="../../pages/blogs/beauty.php">Beauty</a></li>
						<li><a href="../../pages/blogs/business.php">Business & Finance</a></li>
						<li><a href="../../pages/blogs/destinations.php">Destinations</a></li>
						<li><a href="../../pages/blogs/fashion.php">Fashion</a></li>
						<li><a href="../../pages/blogs/health.php">Health & Fitness</a></li>
						<li><a href="../../pages/blogs/home.php">Home & Gourmet</a></li>
						<li><a href="../../pages/blogs/women.php">Women of the World</a></li>
						<li><a href="../../pages/blogs/relationships.php">Relationships</a></li>
						<li><a href="../../pages/blogs/entrepreneur.php">Entrepreneur Experts</a></li>
						<li><a href="../../pages/blogs/spirit.php">Spirit & Balance</a></li>
						<li><a href="../../pages/blogs/workplace.php">The Workplace</a></li>
					</ul>
				</li>
			</ul>
		</div>

	</div>

	<div id="content">

		<div id="content_bottom">
		<h1>Welcome <?php echo $_SESSION['auth_user']; ?></h1>
		<h2>Editor Administration</h2>
			<p><?php include("adminmenu.php"); ?></p>
			<p><?php
    			if (mysql_num_rows($res) > 0) {
    			echo '<table border="0" width="755" cellpadding="5">';
    			echo '<tr><th><h2>Headline</h2></th><th><h2>Page</h2></th>';
      			echo '<th><h2>Author</h2></th><th><h2>Created</h2></th><th><h2>Last modified</h2></th><th><h2>Publish Date</h2></th></tr>';
			while ($row = mysql_fetch_assoc($res)) {
			echo '<tr><td>';
  			echo "{$row['headline']}";
			echo '</td><td>';
        		echo "{$row['page']}";
			echo '</td><td>';
			echo "{$row['writer']}";
			echo '</td><td>';
        		echo date('M d, h:i a', $row['created']);
			echo '</td><td>';
        		echo date('M d, h:i a', $row['modified']);
			echo '</td><td>';
			if ($row['published'])
        		{
          		echo ' [Published '.date('M d, h:i a', $row['published']).'] ';
			echo '[<a href="unpublish.php?story='.$row['id'].'">unpublish</a>]<br />';
        		}
        		else 
        		{
			echo "{$row['month']} {$row['year']}<br />";
          		echo ' [<a href="publish.php?story='.$row['id'].'">publish</a>] ';          		
        		}
			echo ' [<a href="edit_story.php?story='.$row['id'].'">edit</a>] ';
			echo '[<a href="delete_story.php?story='.$row['id'].'">delete</a>] ';
        		echo '[<a href="view_story.php?story='.$row['id'].'">view</a>]';
        		echo '</td></tr>';
   				}
   				echo '</table>';
			}
			else
   				echo 'You have not written any stories.';
			?></p>
		</div>

	</div>

	<div id="right">

		<div id="top_right">

		</div>

	</div>

</div>

<div id="footer">
	<?php include("../../footertext.php"); ?>
</div>

</div>



</body>
</html>

What I would like to do is add two links under the adminmenu that say "Sort by Section" and "Sort by Published Date". Is there a way to do this so that when the editor clicks on one of those links, it resorts the page? I have found several examples of sorting data, but cannot figure out how to modify them in order to accomplish this. Any help or suggestions is greatly appreciated.

Thank you,

wikedawsum

revraz · January 29, 2008

What I do is in your Table headers

echo '<tr><th><h2>Headline</h2></th><th><h2>Page</h2></th>';

echo '<th><h2>Author</h2></th><th><h2>Created</h2></th><th><h2>Last modified</h2></th><th><h2>Publish Date</h2></th></tr>';

I create links there like page.php?sortby=author and in my page I use $_GET to get a sortby order if one is set.

wikedawsum · January 29, 2008

Hi, revraz. Thanks for your reply.

Could you explain using $_GET and a sortby order? I have never done a sort before so I'm not sure how to implement that.

Thanks again!

revraz · January 29, 2008

Well a sort is just the way you pull data from the database. Have you used the ORDER BY function for mysql?

Philip · January 29, 2008

You would use the GET to find out how to sort:

<?php

$sortby = $_GET['sortby'];
//Don't forget to clean the variable!
$okay_values = array("publisher", "title", "author", "copyrightdate");
//Check to make sure value is okay & legal.
if (in_array($sortby, $okay_values)) {
//if its in the array, great! we can continue
$sql = "SELECT * FROM `stories` ORDER BY ".$sortby;
//You could also do more with ASC/DESC keys
}
?>

Just a rough outline of what you would do

wikedawsum · January 29, 2008

revrav,

I have used the order by function in queries. Currently my query is using 'order by published'.

KingPhillip,

I will work with the code you provided and hopefully I can figure out the rest!

Thank you both!

Sign In

Allow user to sort data?

Recommended Posts

wikedawsum

Link to comment

Share on other sites

revraz

Link to comment

Share on other sites

wikedawsum

Link to comment

Share on other sites

revraz

Link to comment

Share on other sites

Philip

Link to comment

Share on other sites

wikedawsum

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information