...

View Full Version : sort help



dare87
08-08-2007, 10:55 PM
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['no problem']))
$num_pages = $_GET['no problem'];
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 '&nbsp;';
}
else if (isset($_SESSION['userId']) && $_SESSION['accessLevel'] == 1 && (substr($_SESSION['PHP_SELF'], -10) != 'logout.php') ) // Correspondent
{
echo '&nbsp;';
}
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) . '&no problem=' . $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))) . '&no problem=' . $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) . '&no problem=' . $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

Daemonspyre
08-09-2007, 01:45 PM
Use Querystrings to push your desired effect.



if(isset($_REQUEST['Order'])) {
$order = $_REQUEST['Order'];
} else {
$order = 'ASC';
}

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 $order 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 $order 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 $order LIMIT $start, $display";
}


And on your HREFs, just add the "&Order=DESC" or "&Order=ASC" querystring.

On a side note:


echo '<a href="ideas.php?s=' . (($display * ($i - 1))) . '&no problem=' . $num_pages . '">' . $i . '</a> ';

You need to remove the space in "no problem" or convert it to an underscore.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum