CODEWITHSUNDEEP
phpmysqlsql
user1104854
user1104854

Reputation: 2167

If statement in MySQL query with PHP

Is it possible to use an if statement in a MySQL query in a similar what I'm showing? I want to grab some information for upcoming events(not ones with previous dates).

ini_set('date.timezone', 'America/New_York');
    echo $timestamp = date('Y-m-d'); 
    $sql = "select eventID,eventTitle,eventDate from events where eventLocationID = $locationID AND (eventDate > $timestamp) ORDER BY eventDate ASC";

edit - the sql statement isn't filtering out dates prior to today (it's displaying everything).

For example, I'm echoing the timestamp just to be sure it's setting properly. In my table I entered two dates, one in january and one today.

So here is the output

  2012-05-31 //timestamp, not part of results. just for reference
  2012-01-10 //first result, however it shouldn't display
  2012-06-22 //this should be the only result that displays

Upvotes: 0

Views: 291

Answers (1)

Corbin
Corbin

Reputation: 33437

Just use a where clause:

$date = date('Y-m-d');
$sql = "
    SELECT 
        foo 
    FROM 
        bar 
    WHERE
        eventLocationId = ... 
        AND eventDate > '$date' 
    ORDER BY 
        eventDate DESC
";

Also, m/d/Y is not a valid date format for MySQL. It expects Y-m-d.

On a note not directly related to your question:

I really want to avoid doing post-query if statements

If you filter a resultset in PHP, it's going to many times slower than if you use a where clause. Databases are made to filter records quickly and efficiently. And, filtering in PHP requires you to actually transfer all of the results from MySQL to PHP (another slow down).

Upvotes: 6

Related Questions