Doyley Posted February 7, 2008 Share Posted February 7, 2008 Hi again all, When it comes to dates with PHP & MySQL I am really poor, I just can't seem to get my head around them, so please excuse me if this is a stupid question. I have a column in a MySQL table called s_date in the MySQL date format (YYYY-MM-DD). I would like to select all the rows in that table but ordered by that column. Would it simply be? $q=mysql_query("select * from table order by s_date asc"); Would that work? Thanks! Quote Link to comment Share on other sites More sharing options...
ratcateme Posted February 7, 2008 Share Posted February 7, 2008 that looks good to me but i always stick with unix timestamps because they are easy to work with Scott. Quote Link to comment Share on other sites More sharing options...
aschk Posted February 7, 2008 Share Posted February 7, 2008 I agree that that should work. And scott has a good point about timestamp (i.e. they're easily to add/subtract) but I recommend keeping your column in date format as specified by MySQL as it now has a good set of date_sub date_add functions that will help you to comparisons Quote Link to comment Share on other sites More sharing options...
Doyley Posted February 7, 2008 Author Share Posted February 7, 2008 Excellent, I don't think I have ever used timestamps unless messing with sombody elses scripts. Thanks guys! Quote Link to comment Share on other sites More sharing options...
Doyley Posted February 8, 2008 Author Share Posted February 8, 2008 Hmm it doesn't seem to work. This is the output I got.. 2008-03-02 2008-02-08 2008-02-09 2008-03-01 2008-02-15 Any ideas? Thanks! Quote Link to comment Share on other sites More sharing options...
Doyley Posted February 8, 2008 Author Share Posted February 8, 2008 Here is the sql.. select uid,full_name,s_date,make,model,h_phone,m_phone from contact_forum where uid='$sel[0]' order by s_date asc Quote Link to comment Share on other sites More sharing options...
aschk Posted February 8, 2008 Share Posted February 8, 2008 Hold on are you sure you're using the DATETIME or DATE field type? Or are you actually using a varchar field? note: on 2nd viewing even if it was ordering by string that would be wrong. Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted February 8, 2008 Share Posted February 8, 2008 Based on the posted output and the query, that query did not produce that output. Something else is going on in your code. Post your actual code and your table definition to get the quickest solution to your problem. Quote Link to comment Share on other sites More sharing options...
aschk Posted February 8, 2008 Share Posted February 8, 2008 Thanks PFMaBiSmAd i should have said that. *SLAPS FOREHEAD* Quote Link to comment Share on other sites More sharing options...
Doyley Posted February 12, 2008 Author Share Posted February 12, 2008 Please excuse the late reply. Here is the full code, please excuse the mess.. <body> <script language="Javascript"> function go() { var redirect; redirect = document.getElementById('filter').value; document.location.href = redirect; } </script> <?php include("functions.php"); $uid=checkCookie(); $filter=$_GET['filter']; $filter=addslashes($filter); if(!$filter){ $filter=1; } echo "<meta http-equiv=\"refresh\" content=\"60;url=main.php?filter=$filter\" />\n"; $selectsql="select sid,status from taxi_status"; $selectnew=mysql_query($selectsql) or die(mysql_error()); echo "<b>Filter By:</b> <form><select id=\"filter\" name=\"filter\" onChange=\"go()\">\n"; while($sel=mysql_fetch_array($selectnew)){ if($filter==$sel[0] && ($filter!='0' or $filter!='1')){ echo "<option value=\"main.php?filter=$sel[0]\" selected>$sel[1]</option>\n"; } else{ echo "<option value=\"main.php?filter=$sel[0]\">$sel[1]</option>\n"; } } echo "<option value=\"main.php?filter=all\">View All</option>\n"; echo "</select></form>\n"; echo "<input type=\"button\" onclick=\"window.open('download.php?filter=".$filter."','download','height=200,width=200');\" value=\"Download\" />\n"; echo "<input type=\"button\" onclick=\"document.location='logout.php'\" value=\"Log Off\" />\n"; echo "<br /><br />\n"; echo "<table border=\"1\" align=\"center\" width=\"100%\">\n"; echo "<tr>\n"; echo "<td align=\"center\"><b>Ref</b></td>\n"; echo "<td align=\"center\"><b>Name</b></td>\n"; echo "<td align=\"center\"><b>Date</b></td>\n"; echo "<td align=\"center\"><b>Vehicle</b></td>\n"; echo "<td align=\"center\"><b>Tel. No's</b></td>\n"; echo "<td align=\"center\"><b>Salesman</b></td>\n"; echo "<td align=\"center\"><b>Status</b></td>\n"; echo "<td align=\"center\"><b>Notes</b></td>\n"; echo "<td> </td>\n"; echo "</tr>\n"; if($filter=='all'){ $selectsql="select uid,salesman from taxi_quotes_status where salesman='$uid'"; } if($filter=='1'){ $selectsql="select uid,salesman from taxi_quotes_status where (status='0' or status='1') and salesman='$uid'"; } else{ $selectsql="select uid,salesman from taxi_quotes_status where status='$filter' and salesman='$uid'"; } $selectnew=mysql_query($selectsql) or die(mysql_error()); while($sel=mysql_fetch_array($selectnew)){ $select2sql="select uid,full_name,s_date,make,model,h_phone,m_phone from contact_forum where uid='$sel[0]' order by s_date asc"; $select2new=mysql_query($select2sql) or die(mysql_error()); while($sel2=mysql_fetch_array($select2new)){ $select3sql="select uname from taxi_logins where uid='$sel[1]'"; $select3new=mysql_query($select3sql) or die(mysql_error()); $sel3=mysql_fetch_array($select3new); $salesman=stripslashes($sel3[0]); $ref=stripslashes($sel2[0]); $name=stripslashes($sel2[1]); $date=stripslashes($sel2[2]); $make=stripslashes($sel2[3]); $model=stripslashes($sel2[4]); $home=stripslashes($sel2[5]); $mobile=stripslashes($sel2[6]); $dabits=explode("-", $date); $date="$dabits[2]-$dabits[1]-$dabits[0]"; echo "<tr>\n"; echo "<td><a href=\"copytomisys.php?ref=$ref\" target=\"_blank\">$ref</a></td>\n"; echo "<td><a href=\"open.php?ref=$ref\" target=\"_blank\">$name</a></td>\n"; echo "<td>$date</td>\n"; echo "<td>$make $model</td>\n"; echo "<td align=\"center\">\n"; echo "<select>\n"; echo "<option>$home</option>\n"; echo "<option>$mobile</option>\n"; echo "</select>\n"; echo "</td>\n"; if($sel[1]=='0'){ echo "<td>Not Assigned</td>\n"; } else{ echo "<td>$salesman</td>\n"; } if($filter=='all'){ $select4sql="select status from taxi_quotes_status where uid='$ref'"; $select4new=mysql_query($select4sql) or die(mysql_error()); $sel4=mysql_fetch_array($select4new); if($sel4[0]=='0'){ echo "<td>No Action</td>\n"; } else{ $select4sql="select status from taxi_status where sid='$sel4[0]'"; $select4new=mysql_query($select4sql) or die(mysql_error()); $sel4=mysql_fetch_array($select4new); echo "<td>$sel4[0]</td>\n"; } } elseif($filter>'1'){ $select4sql="select status from taxi_status where sid='$filter'"; $select4new=mysql_query($select4sql) or die(mysql_error()); $sel4=mysql_fetch_array($select4new); $stat=stripslashes($sel4[0]); echo "<td>$stat</td>\n"; } else{ echo "<td>No action</td>\n"; } $select5sql="select note from taxi_notes where uid='$ref'"; $select5new=mysql_query($select5sql) or die(mysql_error()); $sel5=mysql_fetch_array($select5new); $salesnotes=stripslashes($sel5[0]); if(!$salesnotes){ echo "<td> </td>\n"; } else{ echo "<td>$salesnotes</td>\n"; } echo "<td>\n"; echo "<input type=\"button\" onclick=\"document.location='refer.php?ref=$ref'\" value=\"Refer to Manager\" />\n"; echo "</td>\n"; echo "</tr>\n"; } //2nd while } //1st while echo "</table>\n"; ?> </body> And here is the table definition for contact_forum.. CREATE TABLE `contact_forum` ( `uid` bigint(20) NOT NULL auto_increment, `title` varchar(20) NOT NULL default '0', `full_name` varchar(255) NOT NULL default '', `house_no` varchar(100) NOT NULL default '', `postcode` varchar(20) NOT NULL default '', `email` varchar(200) NOT NULL default '', `h_phone` varchar(20) NOT NULL default '', `m_phone` varchar(20) NOT NULL default '', `v_reg` varchar(20) NOT NULL default '', `s_date` date NOT NULL default '0000-00-00', `cover_level` varchar(255) NOT NULL default '0', `vehicle_use` varchar(255) NOT NULL default '0', `drivers` varchar(255) NOT NULL default '0', `comp_no_claims` bigint(20) NOT NULL default '0', `priv_no_claims` bigint(20) NOT NULL default '0', `accidents` varchar(5) NOT NULL default '', `convictions` varchar(5) NOT NULL default '0', `base_postcode` varchar(20) NOT NULL default '', `dob` date NOT NULL default '0000-00-00', `licence_start` date NOT NULL default '0000-00-00', `comp_licence_start` date NOT NULL default '0000-00-00', `lic_auth` varchar(255) NOT NULL default '', `make` varchar(100) NOT NULL default '', `model` varchar(100) NOT NULL default '', `type` varchar(255) NOT NULL default '0', `year_of_man` varchar(4) NOT NULL default '', `value` varchar(10) NOT NULL default '', `prev_quote` varchar(10) NOT NULL default '', `where_from` varchar(255) NOT NULL default '', `best_contact_time` varchar(255) NOT NULL default '', `how_found` varchar(255) NOT NULL default '', `comment` text NOT NULL, `date_entered` date NOT NULL default '0000-00-00', `rand` varchar(50) NOT NULL default '0', PRIMARY KEY (`uid`) ) TYPE=MyISAM AUTO_INCREMENT=179 ; Thanks! Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted February 12, 2008 Share Posted February 12, 2008 You have two nested while() loops. For each row returned by the outer loop, you execute and display the results of the query you first showed us. Taken out of context the dates probably appear out of order, but I would bet if you consider the order of the dates for each row returned by the outer loop, they are in order. It is not entirely clear what the code is supposed to be doing. You would need to show example data from the taxi_quotes_status table for a specific salesman='$uid'" value and from the contact_forum table for the matching uid='$sel[0]' value and state what results you expect. I suspect you actually want a query that JOINs the two tables and then orders all the resultant rows by the date. Quote Link to comment Share on other sites More sharing options...
Doyley Posted February 12, 2008 Author Share Posted February 12, 2008 Balls I see what you mean. I always avoid join queries because they do my nut in lol. Looks like I'll have to use one. Thanks! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.