Page 1 of 1

search a SUBSTRING of table entry

Posted: Wed May 14, 2008 3:33 am
by deejay
Hi

what I want to do is retrieve some information from my database matching the first 10 digits of the field .

ie i have a date in this format '2008-05-14' and in the database it's as such '2008-05-14 23:38:37'


i though that something like this would work

Code: Select all

 
$selectquery = "SELECT * FROM mynews WHERE SUBSTRING('newsdate',0,9) = '$todaysDate' ";
 
 
but doesn't seem to work.


Thanks in advance for any help.

Re: search a SUBSTRING of table entry

Posted: Wed May 14, 2008 4:52 am
by freeformer
If that is the exact SQL you are using, then it won't work as you've quoted the field name to use, and so it will be treated as a string.

Also, MySQL's SUBSTRING function starts at offset 1 (in contrast to PHP where it starts at 0).

So, what you'd actually need to use is something like:

Code: Select all

$selectquery = "SELECT * FROM mynews WHERE SUBSTRING(newsdate, 1, 10) = '$todaysDate' ";

Re: search a SUBSTRING of table entry

Posted: Wed May 14, 2008 6:18 am
by VladSun

Re: search a SUBSTRING of table entry

Posted: Wed May 14, 2008 9:18 am
by deejay
cheers guys

:) sorted that now.