Why WHERE/IN statement results in blank page

[Pennimus]

I'm constructing a blog for my site. After I got nowhere trying to automatically configure dd-mm-yyyy dates on the fly, I decided just to write the dates manually and order the posts by automatically incremented id instead.

However, in constructing my monthly archive pages I've come up against a problem. Here is the PHP I'm using to get the info from the database:


$blog = mysql_query ("SELECT title, content, date FROM blog WHERE date IN ('August 2005') ORDER BY id DESC LIMIT 10") or die(mysql_error());


The date column in my table is VARCHAR and all the entries so far contain the characters August 2005 but all I get is a blank page. I'm using this while loop to display the results:


while ($entry = mysql_fetch_array($blog)){
echo '
<p class="blogdate">
'. $entry['date'] .'
</p>
<h2>'. $entry['title'] .'</h2>
<div class="blog">
'. $entry['content'] .'
</div>
';
}


Both elements work fine when I remove the WHERE/IN part of the query. Can anybody see where I've gone wrong?

- Adam

[Kid Charming]

IN is for matching values with a number of options:

WHERE 1 IN (1,2,3) returns TRUE; WHERE 1 IN (2,3,4) returns FALSE.

It's still an exact match, though -- WHERE 1 IN (12,2,5) will also return FALSE, even though 12 contains a 1.

Which is a long way of saying that for inexact matching, you need to use LIKE instead of IN:


SELECT
title
,content
,date
FROM
blog
WHERE
date LIKE '%August 2005%'
ORDER BY
id
DESC
LIMIT 10


Though I would strongly suggest revisiting your date configuring so that you can store them properly as DATE or DATETIME types. You'll be much better off than trying to work with your dates as VARCHAR.

[Pennimus]

Thanks for your help. I know it's good advice to try and get the date thing sorted out but for this project it's too late -- unfortunately I have already entered all my archived blog entries with this format.

For my next project, I will do it correctly :thumbsup: