[SOLVED] Query for most recent "dates" from todays date

[newbreed65]

Hey

 

I'm making a movie news site and on my home page I'm currently trying to show a short list of the next movies that are due to be released but i don't seem to be getting anywere. i either end up getting nothing back or just the list of all the movie even if they have already been release as show in this link

 

http://bulletsnoctane.co.uk/index.php

 

<h3>Coming Soon..</h3>
<?php

    $sql = "SELECT movie.id, movie.name, movie.released
           FROM movie
           WHERE released >=" . date("Y-m-d") . "
           ORDER BY released DESC
	   LIMIT 0, 10 ";
    $result = mysql_query($sql, $conn)
      or die('Could not get list of movies; ' . mysql_error());
    
    if (mysql_num_rows($result) == 0) {
      echo "  <em>No movies available</em>";
    } else {


      while ($row = mysql_fetch_array($result)) {
			$date1 = $row['released'];
		$date2 = explode("-",date("Y-m-d",strtotime($date1)));//splits up the date

        echo '<a href="/movies/movie.php?id=' .
             $row['id'] . '">' . htmlspecialchars($row['name']) .
             "</a> | ";
	echo $date2[2] . "/" . $date2[1] . "/" . $date2[0];
	echo "<br />";
        
      }
    }
    ?>

[timmah1]

Looking at your page, your dates are being showing like

 

The Avengers | 15/07/2011
date("d-m-Y");

 

But your trying to get equals or greater than

date("Y-m-d");

 

Trying changing this

WHERE released >=" . date("Y-m-d") . "

 

to this

WHERE released >=" . date("d-m-Y") . "

[newbreed65]

Not look sorry the dates are stored as date("Y-m-d") in the database then when you scroll down and explode the and change it into how it is show on the page

 

      while ($row = mysql_fetch_array($result)) {
			$date1 = $row['released'];
		$date2 = explode("-",date("Y-m-d",strtotime($date1)));//splits up the date

        echo '<a href="/movies/movie.php?id=' .
             $row['id'] . '">' . htmlspecialchars($row['name']) .
             "</a> | ";
	echo $date2[2] . "/" . $date2[1] . "/" . $date2[0];
	echo "<br />";
        
      }
    }

[xtopolis]

If you plug your query into your mysql console or PHPmyadmin, do you get the expected results?

 

It's probably something to do with your column type I'm guessing... Are you using a DATE or DATETIME column type for released?

[newbreed65]

ive just tried the below query in phpmyadmin and got the same result the data type im using for released is date

 

SELECT movie.id, movie.name, movie.released
FROM movie
WHERE released >=2009 -01 -10
ORDER BY released DESC 
LIMIT 0 , 10 

[xtopolis]

Did you use the mysql CURDATE() or an actual date value?

 

I think we'd have to see a table structure dump as well as sample insert data to diagnose further.

[newbreed65]

This is the first time i have ever tried to do this and when i have been looking for help and tutorials well i havnt found anything helpful :-/ , havnt used CURDATE()

[corbin]

ive just tried the below query in phpmyadmin and got the same result the data type im using for released is date

 

SELECT movie.id, movie.name, movie.released
FROM movie
WHERE released >=2009 -01 -10
ORDER BY released DESC 
LIMIT 0 , 10 

 

 

Chances are, MySQL would parse that as 2009-1-10.  Or 1998.  Lol.

 

 

You would need to put it in quotes.

 

 

 

Or, you could use curdate() like xtrololis said.

[newbreed65]

got it now sorry thank you both

[xtopolis]

Oh dear corbin, you've butchered my name . I don't think we can be friends anymore

@OP, glad it works