Page 1 of 2

Question about ordering

Posted: Tue Jan 18, 2005 10:16 pm
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...".

Posted: Tue Jan 18, 2005 11:17 pm
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.

Posted: Wed Jan 19, 2005 6:30 am
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.

Posted: Wed Jan 19, 2005 10:02 am
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?

Posted: Wed Jan 19, 2005 9:53 pm
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?

Posted: Wed Jan 19, 2005 9:56 pm
by feyd
why not use MySQL's date format? it sorts without having to fiddle with it..

Posted: Wed Jan 19, 2005 9:58 pm
by Wldrumstcs
Well, where's a good tutorial for ordering using the date function? I'm sorry I'm such a newb. :P

Posted: Thu Jan 20, 2005 3:47 pm
by feyd
which date function?

Posted: Thu Jan 20, 2005 3:49 pm
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.

Posted: Thu Jan 20, 2005 3:54 pm
by feyd
you'll have to create a custom sorting, as that won't sort on it's own well.

Posted: Thu Jan 20, 2005 3:55 pm
by Wldrumstcs
Any tutorials out there that may help?

Posted: Thu Jan 20, 2005 3:58 pm
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..

Posted: Thu Jan 20, 2005 3:59 pm
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.

Posted: Thu Jan 20, 2005 4:01 pm
by feyd
validate the date they submit via checkdate(), then make it a MySQL compatible date string. :?

Posted: Thu Jan 20, 2005 4:19 pm
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?