Problems with date query

Beginners - Learning PHP
#1

I have a problem.
I programmed a query to check whether a database field containing a year value (typ varchar(4)) is within a given intervals. The script is like that:

$sqlAB=“SELECT …, year FROM …
WHERE
…
AND year >= ‘$txYearFrom’
AND year <= ‘$txYearTo’
AND …
order by …”;

$txYearFrom … To are a text strings which I read out from input fields like that:

The query result is sometimes wrong. How can I fix that and get reliable results?
Btw the database structure can not be changed…

Thanks all for a good idea…

Adriano

[email protected]
#2

I suspect that using the <= and >= operator on TEXT (which is basically what you get with a varchar) will tend to give you unexpected results. I think it actually compares hex values of the combined characters and evaluates that.

You should probably store the YEAR in the database as a number (Integer is good), that would make comparison easier and more consistent.

#3

…yes, that would be a good idea, but…the database already exists from another application and I am not allowed to change type…So, please another suggestion?

Adriano

[email protected]
#4

My first notion is to “CAST” it (see http://us2.php.net/manual/en/language.t … ggling.php ) but you want to sort it in the database and only pull out what you want. Not pull it out and then sort.

Then as I think about it, there is a cast function in MySql that you could consider.

http://dev.mysql.com/doc/refman/5.0/en/ … tions.html

Sponsor our Newsletter | Privacy Policy | Terms of Service