Wilcard character problem in PHP/MySQL

[goldstein]

I have a mysql table with the field DT_DATESUB. Its a date field YYYY-MM-DD. I
have a search php page with a dropdown menu holding the YEARS by using 'SELECT
DISTINCT DATE_FORMAT(DT_DATESUB, '%Y') AS YEARS FROM tablename'. I also have a
static option in the same dropdown menu with an option 'All Years' and value
the wildcard character %. In the results page I have a recordset with the
following SQL 'SELECT * FROM tablename WHERE DATE_FORMAT(DT_DATESUB, '%Y') =
'year' '. (year = $_GET['year']) It works for the distinct values but for the %
value it returns nothing. Its something really simple about DATE_FORMAT but
what??Any ideas? Apache/2.0.53 (Win32) PHP/4.3.10

[David Powers]

goldstein wrote:

> In the results page I have a recordset with the
> following SQL 'SELECT * FROM tablename WHERE DATE_FORMAT(DT_DATESUB, '%Y') =
> 'year' '. (year = $_GET['year']) It works for the distinct values but for the %
> value it returns nothing. Its something really simple about DATE_FORMAT but
> what?

It's got nothing to do with DATE_FORMAT; it's your use of a wildcard
that's incorrect SQL. Using = '%' will search for a literal percentage
sign. To use a wildcard, your SQL needs to use LIKE. Consequently, you
need two separate SQL queries for your results page.

The most appropriate solution would be to change the value from '%' to
'all'. In your results page, you then need this:

if ($_GET['year'] == 'all') {
$sql = 'SELECT * FROM tablename';
}
else {
$sql = 'SELECT * FROM tablename
WHERE DATE_FORMAT(DT_DATESUB, '%Y') = '. $_GET['year'];
}

--
David Powers
Author, "Foundation PHP 5 for Flash" (friends of ED)
Co-author "PHP Web Development with DW MX 2004" (Apress)
[url]http://computerbookshelf.com[/url]