Question about ordering

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Wldrumstcs
Forum Commoner
Posts: 98
Joined: Wed Nov 26, 2003 8:41 pm

Question about ordering

Post by Wldrumstcs »

I have a link at the top of a table column that sorts the results from the DB by name. I pass 2 vars thru the URL: $_GET[orderby] (either teacher, subject, or date) and $_GET[arrangement] (ASC or DESC). Here is my code:

Code: Select all

<?
IF($orderby == "teacher" AND $arrangement == "ASC")&#123;
$teacher_link = "homework.php?orderby=teacher&arrangement=DESC";
$subject_link = "homework.php?orderby=subject&arrangement=ASC";
$date_link = "homework.php?orderby=date&arrangement=ASC";
&#125;
IF($orderby == "teacher" AND $arrangement == "DESC")&#123;
$teacher_link = "homework.php?orderby=teacher&arrangement=ASC";
$subject_link = "homework.php?orderby=subject&arrangement=ASC";
$date_link = "homework.php?orderby=date&arrangement=ASC";
&#125;

IF($_GET&#1111;orderby] != "teacher" AND $_GET&#1111;orderby] != "" AND $_GET&#1111;arrangement] != "")&#123;

mysql_connect("localhost","$username","$password") or die ("Unable to connect to MySQL server."); 
$db = mysql_select_db("$database") or die ("Unable to select requested database.");


$order = "ORDER BY $_GET&#1111;orderby] $_GET&#1111;arrangement]";

&#125;elseif($_GET&#1111;orderby] == "teacher" AND $_GET&#1111;orderby] != "" AND $_GET&#1111;arrangement] != "")&#123;

mysql_connect("localhost","$username","$password") or die ("Unable to connect to MySQL server."); 
$db = mysql_select_db("$database") or die ("Unable to select requested database.");

$order = "ORDER BY SUBSTRING_INDEX(teacher, ' ', -1),SUBSTRING_INDEX(teacher, ' ',1) $_GET&#1111;arrangement]";
&#125;
Then for where I output the PHP in the HTML:

Code: Select all

<?
$color1 = "#FF6103";  
$color2 = "#FFA54F";  
$row_count = 0; 

mysql_connect("localhost","$username","$password") or die ("Unable to connect to MySQL server."); 
$db = mysql_select_db("$database") or die ("Unable to select requested database.");

$query=mysql_query("SELECT * FROM assignments $order");

while ($row = mysql_fetch_array($query)) &#123; 

$teacher=$row&#1111;"teacher"];
$subject=$row&#1111;"subject"];
$date=$row&#1111;"date"];
$assignment=$row&#1111;"assignment"];


    $row_color = ($row_count % 2) ? $color1 : $color2; 

	echo "
		<tr onMouseOver="this.bgColor = '#E8E8E8'"    onMouseOut = "this.bgColor = '$row_color'"    bgcolor='$row_color'>
				<td align='center' width='25%'>
				$teacher</td>
				<td align='center' width='25%'>
				$subject</td>
				<td align='center' width='25%'>
				$date</td>
				<td align='center' width='25%'>
				$assignment</td>
</tr>
					
			";
			    $row_count++; 

&#125;


?>


This code will make the table order by REVERSE alphabetical order, but it won't go back to alphabetical order if I click the link again. I know my code is wrong, but what should I write otherwise?

For the second part of my question:
I store the date the teacher says the assignment is due in the DB as the fields "month","day","year". If I want to sort by date, how would I? The dates are stored as the words "January, February,etc...", "1,2,etc...", and "05,06,etc...".
Last edited by Wldrumstcs on Thu Jan 20, 2005 4:02 pm, edited 3 times in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

  1. your two initial if's are counting on register_globals being on, when they are not.
  2. I'd suggest storing the date in a DATE type, not in 3 seperate fields... Ordering can be achieved through STR_TO_DATE() if you have MySQL 4.1.1 or later.. if you don't have that function, you'll have to use php's functions and sort it yourself.
Wldrumstcs
Forum Commoner
Posts: 98
Joined: Wed Nov 26, 2003 8:41 pm

Post by Wldrumstcs »

Actually, $arrangement and $orderby are transfered via URL. I don't think they're Global vars are they? When I use those vars to order by things like "Subjects", the code works.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

well.. the $subject_link and $date_link variables are identical in both sets of if's too.. maybe that's the logic error?
Wldrumstcs
Forum Commoner
Posts: 98
Joined: Wed Nov 26, 2003 8:41 pm

Post by Wldrumstcs »

Okay, for ordering by date, I now am storing it as mm-dd-yyyy in a mysql DB. What would the code be to order the DB entries by this date format?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

why not use MySQL's date format? it sorts without having to fiddle with it..
Wldrumstcs
Forum Commoner
Posts: 98
Joined: Wed Nov 26, 2003 8:41 pm

Post by Wldrumstcs »

Well, where's a good tutorial for ordering using the date function? I'm sorry I'm such a newb. :P
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

which date function?
Wldrumstcs
Forum Commoner
Posts: 98
Joined: Wed Nov 26, 2003 8:41 pm

Post by Wldrumstcs »

I guess I mean how would I organize something in chronological order if it is stored as "mm-dd-yyyy"?? Sorry for the confusion.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

you'll have to create a custom sorting, as that won't sort on it's own well.
Wldrumstcs
Forum Commoner
Posts: 98
Joined: Wed Nov 26, 2003 8:41 pm

Post by Wldrumstcs »

Any tutorials out there that may help?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I still don't see why you aren't using MySQL's date format... you can easily change it's display when you pull it out of the database. :|

As for a "tutorial", I have no idea.. but I'd use usort() or several SUBSTRING() calls in the SQL.. kinda annoying..
Wldrumstcs
Forum Commoner
Posts: 98
Joined: Wed Nov 26, 2003 8:41 pm

Post by Wldrumstcs »

Ohhh, well, how would I store a date the user chooses using the date format? I know I'm probably getting annoying, but I am lost.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

validate the date they submit via checkdate(), then make it a MySQL compatible date string. :?
Wldrumstcs
Forum Commoner
Posts: 98
Joined: Wed Nov 26, 2003 8:41 pm

Post by Wldrumstcs »

Okay, this is wrong, but how would I correct this?

Code: Select all

$date = date('d',$_POST&#1111;day]);
$date .= '-';
$date .= date('m',$_POST&#1111;month]);
$date .= '-';
$date .= '20';
$date .= date('y',$_POST&#1111;year]);

$sql = "INSERT INTO assignments (teacher,subject,date,assignment) VALUES ('$_COOKIE&#1111;username]','$_POST&#1111;subject]','$date','$_POST&#1111;assignment]')";
$result = mysql_query($sql);
Also, how would I go about grabbing the date and outputting it on the view homework page?
Post Reply