[SOLVED] order by date

[rishiraj]

I am using field "date" type "datetime" in format YYYY-MM-DD HH:MM:SS in MySQL db.

My fields contains date, name, description.

I want to create a dropdown menu that can query db and show field with Today, Yesterday, last month, all time.

I also need to put a form having Fields FROM: dd/mm/yy  TO : dd/mm/yy that can display all fields ranging between To and From values.

 

I will be really thankful for any help in logic or code.

 

Note. All this thing will be displayed on password protected page accessible only to admin.

[Wildbug]

See "Date and Time Functions" in the MySQL manual.  You can use the "...WHERE date BETWEEN start AND end" construct to find ranges.

 

today = NOW()

yesterday = NOW() - INTERVAL 1 DAY

[rishiraj]

i am newbie to mysql and i am not aware how to work with date functions and syntaxes.

If possible please explain me in more detail.

[Wildbug]

Date and time functions

A MySQL tutorial

 

The manual explains it in detail.  There's even a tutorial.  Come back when you've read it and worked out the examples for a few days.

[rishiraj]

Thanks friend,

But i don't have that much time,

I am giving 15 hrs a day to make it work,

I have read the whole manual regarding where clause and date function. I just cant find a way to pass values from php variables to sql where clause. All I manage to do it is made a from having fields  "from_date" and "to_date" stored in php variables. But i am not able to pass values in sql query.

what i am using is

$result = mysql_query('SELECT * FROM `table_name` WHERE 'date' >= $from_date AND 'date' <= $to_date ORDER BY `date` DESC');

 

so i just have to pass values of variables $to_date and $from _date.

but getting error, so just help me with these code.

sorry to bother you but please help me if you can.

[Illusion]

$result = mysql_query("SELECT * FROM `table_name` WHERE `date` >= '$from_date' AND `date` <= '$to_date' ORDER BY `date` DESC');

DATE is a keyword so u can't use it as a column name ,change it.

[rishiraj]

i have changed the name of date field from "date" to "rishidate" as suggested but still getting the error.

 

<?php
$from_date= $_POST['firstdate'];  // values from form
$to_date= $_POST['lastdate'];     // values from form
echo $from_date."<br/>".$to_date;
$con = mysql_connect("localhost", "mar_db", "password);
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("indiamar_rishi", $con);
// rishidate is table field of type datetime
$result = mysql_query('SELECT * FROM `mytablename` WHERE `rishidate` >= $from_date ORDER BY `rishidate` DESC');

echo "<table border='1'>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
    echo "<td>" . $row['rishidate'] . "</td>";
  echo "<td>" . $row['txtprojectname'] . "</td>";
  echo "<td>" . $row['txtdescription'] . "</td>";
echo "<td>" . $row['txtname'] . "</td>";
echo "<td>" . $row['txtemail'] . "</td>";
echo "<td>" . $row['txtphone1'] . "</td>";
echo "<td>" . $row['txtcompany'] . "</td>";
echo "<td>" . $row['txtcountry'] . "</td>";
  echo "</tr>";
  }

echo "</table>";

mysql_close($con);
?>

 

it gives me error

 

2007-02-03

2007-08-03

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/indiamar/public_html/contact/dateresults.php on line 18

 

what can be possible reason?

[akitchin]

you're using single quotes to delimit something that contains a variable.  rather than having the value of $from_date in that query, you'll end up with, literally, $from_date:

 

$result = mysql_query("SELECT * FROM `mytablename` WHERE `rishidate` >= $from_date ORDER BY `rishidate` DESC");

 

if you want to select something in between the from and to dates, you can use the "BETWEEN" operator:

 

$result = mysql_query("SELECT * FROM `mytablename` WHERE `rishidate` BETWEEN $from_date AND $to_date ORDER BY `rishidate` DESC");

 

if you ever want to see why a query might fail (the most common reason for invalid resource errors), use an or die(mysql_error()) clause at the end of your mysql_query() statement.

[Illusion]

and also make sure that the data format u r getting from drop-down is same as that of the mysql date column if not make use of DATE_FORMAT() function.

[rishiraj]

$result = mysql_query("SELECT * FROM `mytablename` WHERE `rishidate` >= $from_date ORDER BY `rishidate` DESC");


$result = mysql_query("SELECT * FROM `mytablename` WHERE `rishidate` BETWEEN $from_date AND $to_date ORDER BY `rishidate` DESC");

 

both gives me result

2007-07-20

2007-08-01

but i have values between these dates.

my 'rishidate' column is of datetime type and I am getting values from form in format YYYY-MM-DD,

is it causing problem?

if yes then how i can rectify that.

Thanks for help.

 

[akitchin]

since you're not strictly defining the datatype of the comparison, it probably would be best to reduce your datetime format to date:

 

$result = mysql_query("SELECT * FROM `mytablename` WHERE DATE(`rishidate`) BETWEEN '$from_date' AND '$to_date' ORDER BY `rishidate` DESC");

 

you'll usually want to add single quotes around values to ensure their delimitation, despite potential user input (even though it can be circumvented).  if DATE() doesn't work (only as of 4.1.1), you can use DATE_FORMAT().  see the MySQL manual, section for "Functions and Operators"

[rishiraj]

Thanks a lot friend,

you were right it was because of different datatype comparison.

its solved and working fine now.