[SOLVED] Sort Help

[dare87]

I am trying to make a sort button with my rating field... I am able to do that if I make additional pages and change the query sort on them but I am sure there is an easier way.. I just don't know it. Here is my code so far.

<?php
	// Connect to the database.
	require_once ('../../datemysql_connect.php');

	// The number of pages to display per page.
	$display = 35;

	// Calculate how many pages will be needed.
	// If the number of pages has not been calculated, then it will need to calculated.

	if (isset($_GET['np']))
		$num_pages = $_GET['np'];
	else // Needs to be calculated.
	{
		// Count the number of records in the database.
		$query = "SELECT COUNT(*) FROM datedb ORDER BY title ASC";

		$result = mysql_query ($query);

		$row = mysql_fetch_array ($result, MYSQL_NUM);

		$num_records = $row[0];

		// Calculate the number of pages to use.
		if ($num_records > $display)
			$num_pages = ceil ($num_records / $display);
		else
			$num_pages = 1;
	}

	// Determine in the database to start returning results.
	if (isset($_GET['s']))
		$start = $_GET['s'];
	else
		$start = 0;
	// Make the query.
	if(isset($_GET['start_letter']))
	{
		if($_GET['start_letter'] == "1")
		{
			$query = "SELECT user_rating AS userrating, title, type1, type2, type3, type4, date AS article, supplies, cost, rating, name, DATE_FORMAT(date_entered, '%M %d, %Y') AS date FROM datedb WHERE approved=1 and title NOT REGEXP '^[A-Za-z]+' ORDER BY title ASC LIMIT $start, $display";	
		}else{

		$query = "SELECT user_rating AS userrating, title, type1, type2, type3, type4, date AS article, supplies, cost, rating, name, DATE_FORMAT(date_entered, '%M %d, %Y') AS date FROM datedb WHERE approved=1 and title like '" . $_GET['start_letter'] . "%' ORDER BY title ASC LIMIT $start, $display";
		}
	}
	else
	{
	$query = "SELECT user_rating AS userrating, title, type1, type2, type3, type4, date AS article, supplies, cost, rating, name, DATE_FORMAT(date_entered, '%M %d, %Y') AS date FROM datedb WHERE approved=1 ORDER BY title ASC LIMIT $start, $display";
	}
	// Run the query.
	$result = @mysql_query ($query);

	// If the query ran w/o error, print out the results.
	if ($result)
	{

		if (isset($_SESSION['userId']) && $_SESSION['accessLevel'] == 0 && (substr($_SESSION['PHP_SELF'], -10) != 'logout.php') ) // Administrator
		{	
		echo ' ';
		}
		else if (isset($_SESSION['userId']) && $_SESSION['accessLevel'] == 1 && (substr($_SESSION['PHP_SELF'], -10) != 'logout.php') ) // Correspondent
		{
		echo ' ';
		}
		else
		{
		echo('<center><script type="text/javascript"><!--
			google_ad_client = "pub-4070400178120955";
			google_ad_width = 468;
			google_ad_height = 15;
			google_ad_format = "468x15_0ads_al_s";
			google_ad_channel = "";
			google_color_border = "FFFFFF";
			google_color_bg = "FFFFFF";
			google_color_link = "425B31";
			google_color_text = "000000";
			google_color_url = "425B31";
			//-->
			</script>
			<script type="text/javascript"
			  src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
			</script></center>');
			}
			// Print ABC index.
		echo ' <table width="100%" align="center" cellpadding="5"><tr>';
		echo('<td><a href="ideas.php">ALL</a></td>');
		echo('<td><a href="ideas.php?start_letter=1">#</a></td>');
		for($a = 65; $a <= 65+25; $a++)
		{
			echo('<td><a href="ideas.php?start_letter=' . chr($a) . '">' . chr($a) . "</a></td>");
		}
		echo '</tr></table>';
		//Print Table Head. would like sort by rating here
		echo '  <table width="100%" align="center" cellpadding="5">
				<tr>
					<td>

				<table width="100%" align="center" cellspacing="0" cellpadding="5">
				<tr>
					<td align="left">Title</td>
					<td align="left">Last Updated</td>
		<td align="left">Rating</td>
				</tr>';

		// Fetch and print all the records.

		$bg = '#dee4ed'; // Set the background color.

		while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
		{
			// Alternate the background color.
			$bg = ($bg == '#e6f5e4' ? '#ffffff' : '#e6f5e4');

			echo '  <tr bgcolor="' . $bg . '">
						<td align="left"><a href="date_view.php?userrating=' . $row['userrating'] . '&title=' . $row['title'] . '">' . $row['title'] . '</a></td>
						<td align="left">' . $row['date'] . '</td>
						<td align="left"><img src="images/rate' . $row['rating']. '.gif"</td>
					</tr>';							
		}

		// Close the table.
		echo '</table>';

		// Free up the resources.
		mysql_free_result ($result);

		// Make links to other pages, if necessary.
		if ($num_pages > 1)
		{
			echo '<br /><p>';
			// Determine what page the script is on.
			$current_page = ($start / $display) + 1;

			// If it's not the first page, create a previous button.
			if ($current_page != 1)
				echo '<a href="ideas.php?s=' . ($start - $display) . '&np=' . $num_pages . '">Previous</a> ';

			// Make all the numbered pages.
			for ($i = 1; $i <= $num_pages; $i++)
				if ($i != $current_page)
					echo '<a href="ideas.php?s=' . (($display * ($i - 1))) . '&np=' . $num_pages . '">' . $i . '</a> ';
				else
					echo $i . ' ';

			// If it's not the last page, make a Next button.
			if ($current_page != $num_pages)
				echo '<a href="ideas.php?s=' . ($start + $display) . '&np=' . $num_pages . '">Next</a> ';

			echo '</p>';
		} 
	}
	else
	{
		// If the query did not run successfully, print out an error message.
		echo 'The date database could not be retrieved.';
	}

	// Close the database connection.
	mysql_close();
	?>

I would like to be able to click the rating and have it sort DESC then when that loads click it again and have it load ASC... Thanks for your help

[dcp]

Something like this could work. You could use the same approach to sort by title and last updated, but, with the pagination you'll have to insert those variables into the links as well.

 

(I didn't read through all your code, so some of this may be off.)

 

----------------------------------------------

 

$query = "SELECT user_rating AS userrating, ";
$query .= "title, type1, type2, type3, type4, date AS article, ";
$query .= "supplies, cost, rating, name, DATE_FORMAT(date_entered, '%M %d, %Y') AS date ";
$query .= "FROM datedb WHERE approved=1 and title NOT REGEXP '^[A-Za-z]+' ";
if (isset($_GET['sort'])) {
    $query .= "ORDER BY ".$_GET['sort']." ";
     } else { 
    $query .= "ORDER BY title ";
    }
if (isset($_GET['order'])) {
     $query .= $_GET['order']." ";
} else { $query .= "ASC ";}
$query .=" LIMIT $start, $display";	

 

-----------------------------------------------

			//Print Table Head. would like sort by rating here
		echo '  <table width="100%" align="center" cellpadding="5">
				<tr>
					<td>

				<table width="100%" align="center" cellspacing="0" cellpadding="5">
				<tr>
					<td align="left">Title</td>
					<td align="left">Last Updated</td>
		<td align="left">';
                       echo '<a href="?sort=userrating';
                       if ($_GET['order'] == "ASC") { echo "&order=DESC"; }
                            else {echo "&order=ASC"; }
                       echo '>Rating</a></td>
				</tr>';

[dcp]

Sorry if that's difficult to read.

[dare87]

in the first set of code there is a "box" at the end... what is that?

[dcp]

Oh. I don't know. Weird, I guess. I pasted the code in from Notepad2. (It's difficult to read long bits of code here.)

 

Must be an artifact of something. . . carriage return maybe.

 

It doesn't show up in Firefox.