Interval part of MYSQL Query not working

Databases
21

Ok. As you suggested, I got it to work first with just putting the date as shown below

$result = mysql_query(“SELECT destination, departurepoint, departureDate, from entries WHERE departureDate >= ‘2010-05-02’ AND departureDate < ‘2010-05-02’ + INTERVAL ‘7’ DAY”);

I know that the query works now, but I need to substitute the date with the variable ‘$dep’ as the date is stored in dd-mm-yyyy format, but not sure how to do it? also need to display it to the user in the uk format

can you please advise how this can be achieved

thanks again

22

What I would do is rewrite the $dep to yyyy-mm-dd format in PHP.

Use the following


list($day,$month,$year)=explode('-', $dep);
$depMysql=$year.'-'.$month.'-'.$day;

Now you can use $depMysql in your query, which has the correct format.

To give the UK date back to the user, rewrite your query as follows:


SELECT destination, departurepoint, DATE_FORMAT("&#37;d-%m-%Y", departureDate) AS departureDateUK, from entries WHERE departureDate >= '2010-05-02' AND departureDate < '2010-05-02' + INTERVAL '7' DAY

Note that you can’t do AS departureDate, because then MySQL will use the rewritten departureDate in your WHERE clause, rendering the query useless.

So, to sum up:


list($day,$month,$year)=explode('-', $dep);
$depMysql=mysql_real_escape_string($year.'-'.$month.'-'.$day);
$result = mysql_query("SELECT destination, departurepoint, departureDate, from entries WHERE departureDate >= '".$depMysql."' AND departureDate < '".$depMysql."' + INTERVAL '7' DAY");

I added the mysql_real_escape_string() here to prevent [url=http://en.wikipedia.org/wiki/SQL_Injection]SQL Injection.

Key thing to note here:

  • Keep every value that MySQL uses in the format MySQL expects (e.g. don’t use DATE_FORMAT() in the WHERE clause)
  • Only use functions like DATE_FORMAT() in the SELECT clause if you want to output data to users in another format than MySQL gives it to you
23

Thanks. I got it work by slightly modifying your code as i had a few problems with it but this works now apart from one thing:

list($day, $month, $year) = split( ‘[/.-]’, $dep);
$dep3 = "$year-$month-$day<br />
";
echo $dep3 ;// to test it works

$result = mysql_query("SELECT destination,departureDate, DATE_FORMAT(‘%d-%m-%Y’, departureDate) as departureDateUK from entries WHERE departureDate >= ‘$dep3’ and departureDate <= ‘$dep3’ + INTERVAL ‘7’ DAY
");

The only problem left is displaying it to the user. It shows the correct entries but the departureDate field is not displayed so cant see the dates. If i print departureDateUK, it shows nothing in the field. if i print departureDate, it shows the date in the mysql format yyyy-mm-dd.

print “<td>” . $package[“destination”] . “</td>” // this is what i use to print the destination and it works
;
print “<td>” . $package[“departureDateUK”] . “</td>” // this si what i use to print the date but it does not show the date
;

Can you spot where the problem is ?is it to do with the printing bit or formatting part? the latter looks ok to me.

24
  1. $dep3 = "$year-$month-$day<br />
    ";

Remove the "<br />
", MySQL doesn’t know what that would mean.

  1. Make the SQL as follows

SELECT
  destination,DATE_FORMAT(departureDate, '&#37;d-%m-%Y')
FROM
  entries
WHERE
  departureDate >= '$dep3' AND departureDate <= '$dep3' + INTERVAL '7'

i.e.,
2a) The parameters for DATE_FORMAT are the other way around, so (date, format) instead of (format, date) <– my bad
2b) don’t select both departureDate as well as DATE_FORMAT(departureDate, ‘%d-%m-%Y’), since you’re never using departureDate (but departureDateUK), this makes query slightly confusing (at least for me :)).

25

Ok I removed the <br> and tried it as you suggested:

$result = mysql_query("SELECT destination, DATE_FORMAT(departureDate, ‘%d/%m/%Y’) FROM entires WHERE departureDate >= ‘$dep3’ AND departureDate <= ‘$dep3’ + INTERVAL ‘7’
");

It doesnt like it. It doesnt return anyhting

when I echo ‘$dep3’ It prints the date in the mysql format as expected yyyy-mm-dd but It doesnt seem to like the format part. does the format part needs AS after it?

26

Ok Got it sorted now :slight_smile:
It just needed the AS bit; below is the full query:

$result = mysql_query("SELECT destination, DATE_FORMAT(departureDate, ‘%d/%m/%Y’) as formattedDate FROM entries WHERE departureDate >= ‘$dep3’ and DepartureDate <= ‘$dep3’ + INTERVAL ‘7’ DAY

");

Thanks for your help and advise :slight_smile:

27

I got it sorted now :slight_smile:

It just needed the AS part

this is the query:

$result = mysql_query("SELECT destination, DATE_FORMAT(departureDate, ‘%d/%m/%Y’) as formattedDate FROM entries WHERE departureDate >= ‘$dep3’ and DepartureDate <= ‘$dep3’ + INTERVAL ‘7’ DAY

");

Many thanks for your help and advice :slight_smile:

28

You don’t need the AS part, you can also do


$result = mysql_query("SELECT destination, DATE_FORMAT(departureDate, '&#37;d/%m/%Y') FROM entires WHERE departureDate >= '$dep3' AND departureDate <= '$dep3' + INTERVAL '7' DAY");
while($row = mysql_fetch_assoc($result))
{
  echo $row['DATE_FORMAT(departureDate, \\'%d/%m/%Y\\')'];
}

But as you can see, that’s rather hard to read, and you have to change the PHP if you ever change the date format.
So it’s easier to use “AS formattedDate” and then use $row[‘formattedDate’]

:slight_smile:

29

ok thanks

I ll bear that in mind :slight_smile: