Problem with using "While / LIKE" with parameters

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
michlcamp
Forum Commoner
Posts: 78
Joined: Mon Jul 18, 2005 11:06 pm

Problem with using "While / LIKE" with parameters

Post by michlcamp »

I've got a good script that sorts orders based on date - using this query :

Code: Select all

SELECT * FROM orders WHERE (date like '01-%')
But now I need to sort them by year as well -
I tried using

Code: Select all

SELECT * FROM orders WHERE (date like '01-%-2007')
but get zero values returned.

anyone?

my question is: How to sort orders by month and year

the date field posted to my orders table uses this format: 01-01-2007

thanks in advance.
mc
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

[url=http://forums.devnetwork.net/viewtopic.php?t=30037]Forum Rules[/url] Section 1.1 wrote:1. Select the correct board for your query. Take some time to read the guidelines in the sticky topic.
Is there a reason you're not using a native date format? The reason I ask this is because it makes it somewhat difficult to sort and select in date ranges like this.

SUBSTRING_INDEX() can be effectively used if you're using MySQL.
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Post by jayshields »

% is a wildcard character. You can only use it at the start or end of a string, not in the middle.

So to return all dates in Janurary 2007 you could change your query to this:

Code: Select all

SELECT * FROM `orders` WHERE `date` LIKE '01-%' AND `date` LIKE '%-2007'
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

jayshields wrote:% is a wildcard character. You can only use it at the start or end of a string, not in the middle.

Code: Select all

mysql> select '10-12-11' like '10-%-11';
+---------------------------+
| '10-12-11' like '10-%-11' |
+---------------------------+
|                         1 |
+---------------------------+
1 row in set (0.03 sec)
:)
michlcamp
Forum Commoner
Posts: 78
Joined: Mon Jul 18, 2005 11:06 pm

Post by michlcamp »

jayshields wrote:% is a wildcard character. You can only use it at the start or end of a string, not in the middle.

So to return all dates in Janurary 2007 you could change your query to this:

Code: Select all

SELECT * FROM `orders` WHERE `date` LIKE '01-%' AND `date` LIKE '%-2007'

I tried changing my query that way but still got zero values -
Post Reply