Jump to content

MySQL Order By PHP Help

gospabode2

By gospabode2
in PHP Coding Help

 Share

Recommended Posts

gospabode2 Newbie

gospabode2

Posted
Posted

Hello,

I am trying to get my database to show a drop down menu where the user can choose an option to order the results. I have made the drop down menu and have set it up to post to itself and receive it, and I know that when I submit it is getting the result posted, because of experiments I have done with echo, so I am out of options. No matter what, I cannot make the last ordered differently through Mysql. It just remains the same ordering as when I first visit the page.

I am using adam's pagination to paginate. Here is my code, in a nutshell:

PHP Up top:

<?php

mysql_connect("") or die (mysql_error());
mysql_select_db("") or die (mysql_error());
$order=$_POST["order"];
if(isset($_POST["order"]) and strlen($order)>0){
$order=$_POST["order"]; 
}
else{
$order="IDa";
} 
//////////////  QUERY THE MEMBER DATA INITIALLY LIKE YOU NORMALLY WOULD
$sql = mysql_query("SELECT * FROM orphans ORDER BY '$order'") or die (mysql_error()); 
//////////////////////////////////// Adam's Pagination Logic ////////////////////////////////////////////////////////////////////////
$nr = mysql_num_rows($sql); // Get total of Num rows from the database query
if (isset($_GET['pn'])) { // Get pn from URL vars if it is present
    $pn = preg_replace('#[^0-9]#i', '', $_GET['pn']); // filter everything but numbers for security(new)
    //$pn = ereg_replace("[^0-9]", "", $_GET['pn']); // filter everything but numbers for security(deprecated)
} else { // If the pn URL variable is not present force it to be value of page number 1
    $pn = 1;
} 
//This is where we set how many database items to show on each page 
$itemsPerPage = 10; 
// Get the value of the last page in the pagination result set
$lastPage = ceil($nr / $itemsPerPage);
// Be sure URL variable $pn(page number) is no lower than page 1 and no higher than $lastpage
if ($pn < 1) { // If it is less than 1
    $pn = 1; // force if to be 1
} else if ($pn > $lastPage) { // if it is greater than $lastpage
    $pn = $lastPage; // force it to be $lastpage's value
} 
// This creates the numbers to click in between the next and back buttons
// This section is explained well in the video that accompanies this script
$centerPages = "";
$sub1 = $pn - 1;
$sub2 = $pn - 2;
$add1 = $pn + 1;
$add2 = $pn + 2;
if ($pn == 1) {
    $centerPages .= '  <span class="pagNumActive">' . $pn . '</span>  ';
    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' . $add1 . '</a>  ';
} else if ($pn == $lastPage) {
    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a>  ';
    $centerPages .= '  <span class="pagNumActive">' . $pn . '</span>  ';
} else if ($pn > 2 && $pn < ($lastPage - 1)) {
    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub2 . '">' . $sub2 . '</a>  ';
    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a>  ';
    $centerPages .= '  <span class="pagNumActive">' . $pn . '</span>  ';
    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' . $add1 . '</a>  ';
    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add2 . '">' . $add2 . '</a>  ';
} else if ($pn > 1 && $pn < $lastPage) {
    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $sub1 . '">' . $sub1 . '</a>  ';
    $centerPages .= '  <span class="pagNumActive">' . $pn . '</span>  ';
    $centerPages .= '  <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $add1 . '">' . $add1 . '</a>  ';
}
// This line sets the "LIMIT" range... the 2 values we place to choose a range of rows from database in our query
$limit = 'LIMIT ' .($pn - 1) * $itemsPerPage .',' .$itemsPerPage; 
// Now we are going to run the same query as above but this time add $limit onto the end of the SQL syntax
// $sql2 is what we will use to fuel our while loop statement below
$sql2 = mysql_query("SELECT * FROM orphans ORDER BY '$order' $limit") or die(mysql_error()); 

//////////////////////////////// END Adam's Pagination Logic ////////////////////////////////////////////////////////////////////////////////

///////////////////////////////////// Adam's Pagination Display Setup /////////////////////////////////////////////////////////////////////
$paginationDisplay = ""; // Initialize the pagination output variable
// This code runs only if the last page variable is ot equal to 1, if it is only 1 page we require no paginated links to display
if ($lastPage != "1"){
    // This shows the user what page they are on, and the total number of pages
    $paginationDisplay .= 'Page <strong>' . $pn . '</strong> of ' . $lastPage. '        ';
    // If we are not on page 1 we can place the Back button
    if ($pn != 1) {
        $previous = $pn - 1;
        $paginationDisplay .=  '   <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $previous . '"> Back</a> ';
    } 
    // Lay in the clickable numbers display here between the Back and Next links
    $paginationDisplay .= '<span class="paginationNumbers">' . $centerPages . '</span>';
    // If we are not on the very last page we can place the Next button
    if ($pn != $lastPage) {
        $nextPage = $pn + 1;
        $paginationDisplay .=  '   <a href="' . $_SERVER['PHP_SELF'] . '?pn=' . $nextPage . '"> Next</a> ';
    } 
}
///////////////////////////////////// END Adam's Pagination Display Setup ///////////////////////////////////////////////////////////////////////////
// Build the Output Section Here
//Calculate Age in Years
function birthday ($birthdate)
  {
    list($year,$month,$day) = explode("-",$birthdate);
    $year_diff  = date("Y") - $year;
    $month_diff = date("m") - $month;
    $day_diff   = date("d") - $day;
    if ($month_diff < 0) $year_diff--;
    elseif (($month_diff==0) && ($day_diff < 0)) $year_diff--;
    return $year_diff;
  }
$outputList = '';
while($row = mysql_fetch_array($sql2)){ 

    $ida = $row['IDa'];
    $firstname = $row['FirstName'];
    $lastname = $row['LastName'];
    $birthdate = $row['BirthDate'];
    $gender = $row['Gender'];
    $sponsorcount = $row['SponsorCount'];
    $createdon = $row['Created_On'];
    $changedon = $row['Changed_On'];
    $active = $row['active'];
    $picone = $row['PictureOne'];


   $outputList .= '<div class="child"><div class="picbox"><div class="imgchild"><a href="/abode/childbio.php?id=' . $ida . '?iframe=true&width=780&height=600" rel="prettyPhoto"><img title="' . $firstname . ' ' . $lastname . '" style="display: block; margin: 0 auto;" alt=" No Photo Available" class="picture" src="/orphans/pic1/'. $picone . '" /></a></div></div>' . 
   '<div class="descrip"><a href="/abode/childbio.php?id='.$ida.'?iframe=true&width=780&height=600" rel="prettyPhoto"><b>'.$firstname.' '.$lastname.'</b></a><br />' .
   'Age: <b>' . birthday ("$birthdate.") .'</b><br />' .
   'Birthday: <b>' . $birthdate . '</b><br /> ' .
   'Gender: <b>' . $gender . '</b><br />' . 
   '</div></div>';

} // close while loop
?>

Html

<div class="contentheader"></div>
		<p class="textheader">The Children</p>
			<div class="pagdisplay">
				<?php echo $paginationDisplay; ?>
				<form method="post" action="/abode/children.php">
					Sort Orphans
					<select name="order">
						<option value="LastName ASC">A-Z</option>
						<option value="Gender DESC">Boys First</option>
						<option value="Gender ASC">Girls First</option>
						<option value="BirthDate ASC">Youngest First</option>
						<option value="BirthDate DESC">Oldest First</option>
					</select>
					<input type="submit">
				</form>
				<div class="totalorphs">Total Orphans:<?php echo $order;?></div>
			</div>

			<div style="width: 100%;"  class="children">
					<?php print "$outputList"; ?>
			</div>

 

Any Help would be greatly appreciated. Thanks!

 

** Edited to Fix Stupid Mistake

Link to comment
Share on other sites
Psycho Prolific Member

Psycho

Posted
Posted 

I'm not going to go through all of your code and fix it since I don't have your db to test against and I'm not willing to invest the time. But, I'll provide some suggestions.

First off - not related to your question:
[code=php:0]$order=$_POST["order"];
if(isset($_POST["order"]) and strlen($order)>0){
$order=$_POST["order"]; 
}

That if() statement serves no purpose. The previous line you already defined $order from the POST value, so if the if condition is true you only redefine it!

 

Anyway:

 

1. You do not need to use ORDER BY in the first query since it is only used to get the total record count

$sql = mysql_query("SELECT * FROM orphans ORDER BY '$order'") or die (mysql_error());

 

2. I'm surprised your query isn't failing on the 1st page load. Since you did not submit a sort option your query would be invalid

$sql2 = mysql_query("SELECT * FROM orphans ORDER BY '$order' $limit") or die(mysql_error());

When $order is not defined that query would look something like

[coed]SELECT * FROM orphans ORDER BY LIMIT 10[/code]

 

3. OK, the main problem I see is that you are using a form to submit the ORDER BY value through POST data. That's not a problem in itself, and it should work - but only on the first page! The problem is that once you select a new page, you reload the page and there is no POST value to define the ORDER BY value. So, when the user submits an ORDER BY via the POST form, you need to store that value to use on subsequent page loads. You can do this one of three ways: 1: Append to the URL of the page links, 2: store in a cookie, 3: store in a session value (my preference). Then on subsequent page loads you would first check if the ORDER BY was sent in POST data (if so use it). If not, then you have to check the secondary value from the option you chose from the three above. If not set there, then use the default.

 

Here is a sample of how you might do that last part:

if(isset($_POST["order"]))
{
    $order = mysql_real_esacpe_string($_POST["order"]));
    $_SESSION['order'] = $order;
}
elseif(isset($_SESSION['order']))
{
    $order = $_SESSION['order'];
}
else
{
    $order = 'defaultField ASC'; //change as needed
}

Link to comment
Share on other sites
gospabode2 Newbie

gospabode2

Posted
  • Author
Posted

Okay I see what you are saying. Thanks a lot. I haven't worked with sessions much yet, so that is still new to me.

The problem that I originally had still seems to be there: It doesn't reorder the results. I really can't figure out why. Any help would be appreciated! Here is updated code:

 

<?php
session_start();
mysql_connect("") or die (mysql_error());
mysql_select_db("") or die (mysql_error());
if(isset($_POST["order"]))
{
$order = mysql_real_escape_string($_POST["order"]); 
$_SESSION['order'] = $order;
}
elseif(isset($_SESSION['order']))
{
$order = $_SESSION['order'];
}
else{
$order = "IDa";
} 

$sql = mysql_query("SELECT * FROM orphans ORDER BY '$order'") or die (mysql_error()); 

// This line sets the "LIMIT" range
$limit = 'LIMIT ' .($pn - 1) * $itemsPerPage .',' .$itemsPerPage; 

//Pagination Stuff
// $sql2 is what we will use to fuel our while loop statement below
$sql2 = mysql_query("SELECT * FROM orphans ORDER BY '$order' $limit") or die(mysql_error()); 
//Pagination Stuff
$outputList = '';
while($row = mysql_fetch_array($sql2)){ 

    $ida = $row['IDa'];
    $firstname = $row['FirstName'];
    $lastname = $row['LastName'];
    $birthdate = $row['BirthDate'];
    $gender = $row['Gender'];
    $sponsorcount = $row['SponsorCount'];
    $createdon = $row['Created_On'];
    $changedon = $row['Changed_On'];
    $active = $row['active'];
    $picone = $row['PictureOne'];


   $outputList .= '<div class="child"><div class="picbox"><div class="imgchild"><a href="/abode/childbio.php?id=' . $ida . '?iframe=true&width=780&height=600" rel="prettyPhoto"><img title="' . $firstname . ' ' . $lastname . '" style="display: block; margin: 0 auto;" alt=" No Photo Available" class="picture" src="/orphans/pic1/'. $picone . '" /></a></div></div>' . 
   '<div class="descrip"><a href="/abode/childbio.php?id='.$ida.'?iframe=true&width=780&height=600" rel="prettyPhoto"><b>'.$firstname.' '.$lastname.'</b></a><br />' .
   'Age: <b>' . birthday ("$birthdate.") .'</b><br />' .
   'Birthday: <b>' . $birthdate . '</b><br /> ' .
   'Gender: <b>' . $gender . '</b><br />' . 
   '</div></div>';

} // close while loop
?>


<div class="pagdisplay">
				<?php echo $paginationDisplay; ?>
				<form method="post" action="/abode/children.php">
					Sort Orphans
					<select name="order">
						<option value="LastName ASC">A-Z</option>
						<option value="Gender DESC">Boys First</option>
						<option value="Gender ASC">Girls First</option>
						<option value="BirthDate ASC">Youngest First</option>
						<option value="BirthDate DESC">Oldest First</option>
					</select>
					<input type="submit">
				</form>
				<div class="totalorphs">Total Orphans:<?php echo $order;?></div>
			</div>

			<div style="width: 100%;"  class="children">
					<?php print "$outputList"; ?>
			</div>

Link to comment
Share on other sites
Psycho Prolific Member

Psycho

Posted
Posted

If it is not working, then debug your code. Just determine the decision points in your logic and echo out the pertinent data to see if what you expect to happen is happening. I was working on adding some debugging code to get you started when I found the problem. Your query to get the data for the page has single quote marks around the $order variable:

$sql2 = mysql_query("SELECT * FROM orphans ORDER BY '$order' $limit") or die(mysql_error());

You only put quotes around values in a query.

 

User this

$sql2 = mysql_query("SELECT * FROM orphans ORDER BY $order $limit") or die(mysql_error());

Link to comment
Share on other sites
gospabode2 Newbie

gospabode2

Posted
  • Author
Posted

Thanks for the advice. I had already echoed out all of the information, everything with POST is working. And I had already tried with and without those single quotes and when I had single quotes I did not get an error message, but when I didn't have them I did get an error message. Right now, I am not at my work computer, so I cannot retry and try to get the same error message, but I really have tried to debug. I have echoed out everything I possibly could. Everything seems to be working except the most important thing: the ordering. When I am at work tomorrow, I will retry removing those quotes, maybe the error message was for something else, but I am pretty sure I already tried that. Thanks for helping out. I really try not to be a pain to all of you, but you guys, all people who have helped me thus far, have been an immeasurable help. I will retry at work tomorrow. Thanks!

Link to comment
Share on other sites
Psycho Prolific Member

Psycho

Posted
Posted

If you have done some debugging, please share what your findings have been so we don't have to ask about things that you have already ruled out. Go ahead and remove those single quote marks as they are not correct. If you are getting an error it is likely that error is telling you what is wrong. please share what it is.

Link to comment
Share on other sites
gospabode2 Newbie

gospabode2

Posted
  • Author
Posted

Thank you SO Much. I must have been getting that error because as I was deleting those quotes, I was adding something else on accident or something of that sort of thing. I have been known to not notice typos. Sorry I was such a pain. Thanks for the advice for future posting. I hope that if I have errors in the future, I can explain them better so you don't have to ask for more information.  :D

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.