Using two variables from PHP on the same WHERE in MySQL

Question

I'm trying to extract data from a MySQL server with PHP. The information has a date field like this YYYYMM (fx. 201108). My question is how would an SQL query look if I wanted to return the first or last half of a specific year? I have tired something like this, however the lessthan seems to be ignored:

SELECT * FROM `poster` WHERE mdr LIKE '2011%' AND mdr < '%06'

I'm afraid google havnt been much help for me.

what u exact want?? 20 and 11 ?? and what is the datatype of field where u store that date?? — xkeshav
– xkeshav, Commented Aug 29, 2011 at 12:43
If you are storing date as a string, bad for you. You should use the DATETIME data type. Then you can simply use < and > to compare dates. — Madara's Ghost
– Madara's Ghost, Commented Aug 29, 2011 at 12:43
u may b applying STRING function on datetime datatype field — xkeshav
– xkeshav, Commented Aug 29, 2011 at 12:43

MaXo · Accepted Answer · 2011-08-29 12:47:06Z

1

you can do this, but not with VARCHAR or TEXT:

SELECT * FROM `poster` WHERE mdr < '201106' AND mdr > '201100'

answered Aug 29, 2011 at 12:47

MaXo

813 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Johan Over a year ago

+1, this will also allow MySQL to keep using an index on field mdr.

Johan Over a year ago

In this case you can also do: SELECT * FROM poster WHERE mdr BETWEEN '201101' and '201105'

Johan · Accepted Answer · 2011-08-29 12:50:15Z

1

You can only use the % and _ wildcards with the LIKE statement.
This will work, but using any function on a field will kill any opportunity to use indexes, slowing things down.

SELECT * FROM poster WHERE mdr LIKE '2011%' AND right(mdr,2) > '06'

See:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

answered Aug 29, 2011 at 12:50

Johan

77.4k28 gold badges204 silver badges346 bronze badges

2 Comments

Lars Over a year ago

This worked perfectly, thanks. When i start making my own databases ill sure think about what datatype to use :)

Lars Over a year ago

Alright, however this one seems to be easier to use with PHP? with the year as a variable.

Benjamin Crouzier · Accepted Answer · 2011-08-29 12:53:02Z

1

Try th folowing:

SELECT * FROM `poster` WHERE mdr LIKE '2011%' AND MONTH(STR_TO_DATE(mdr, '%Y%m')) < 06

edited Aug 29, 2011 at 12:53

answered Aug 29, 2011 at 12:46

Benjamin Crouzier

42.2k48 gold badges179 silver badges240 bronze badges

1 Comment

Benjamin Crouzier Over a year ago

Indeed, i added the str_to_date

xkeshav · Accepted Answer · 2011-08-29 12:54:59Z

0

TRY ( assuming that datatype is varchar)

SELECT SUBSTRING('YYYYDDMM',FROM 0 FOR 2) AS firstHalf, 
       SUBSTRING('YYYYDDMM',FROM 2 FOR 2) AS SecondHalf
FROM yourTable

References

edited Aug 29, 2011 at 12:54

answered Aug 29, 2011 at 12:49

xkeshav

54.2k47 gold badges183 silver badges255 bronze badges

Collectives™ on Stack Overflow

Using two variables from PHP on the same WHERE in MySQL

4 Answers 4

2 Comments

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related