Order by Dates

Doyley · 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!

ratcateme · February 7, 2008

that looks good to me but i always stick with unix timestamps because they are easy to work with

Scott.

aschk · 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

Doyley · February 7, 2008

Excellent, I don't think I have ever used timestamps unless messing with sombody elses scripts.

Thanks guys!

Doyley · 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!

Doyley · 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

aschk · 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.

PFMaBiSmAd · 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.

aschk · February 8, 2008

Thanks PFMaBiSmAd i should have said that.

*SLAPS FOREHEAD* ::)

Doyley · 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!

PFMaBiSmAd · 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.

Doyley · 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!

Sign In

Order by Dates

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Archived

Important Information