Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2

Thread: sort help

  1. #1
    New to the CF scene
    Join Date
    Aug 2007
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    sort help

    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.
    Code:
    <?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

  • #2
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    Use Querystrings to push your desired effect.

    PHP Code:
    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:

    PHP Code:
    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.
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •