display records of a specific year in php mysql

Question

I have a system that shows records year wise. I have dropdown menu from which user selects year, and I am storing that value in variable $year. So now when $year = 2013-2014, it should not display records of year 2014-2015. BTW when I insert my contractNumber, I also insert the year which user has selected during the login. e.g. contractNumber = 1#2013-2014.

I tried this queries:

SELECT * FROM contract WHERE contarctNumber RLIKE "^$year";

Now when I select year 2014-2015, it still displays all the records from year 2013-2014.

Any suggestions?

Is this a PHP question or database design? Don't you have a time stamp field associated with each contract? — Mawg says reinstate Monica, Apr 06 '13 at 07:53
It is PHP question. and no I don't have time stamp field. But I do have contractDate field which is inserted manually by user. — ashah142, Apr 06 '13 at 08:02
@ashah142 you should convert your dates to int (unix timestamp) or mysql date type. Otherwise if your database grows the "LIKE" operations will take ages. Also, why not use the ISO8601 dates so you at least get them to be sortable (and better more efficient indexes for searching)? — oxygen, Apr 06 '13 at 08:08

Sumit Bijvani · Answer 1 · 2013-04-06T08:12:44.290

1

Try this query

SELECT * FROM contract WHERE contarctNumber LIKE "%$year";

SQLFiddle DEMO

Example

CREATE TABLE test (contarctNumber varchar(25));
INSERT INTO test VALUES ('1#2013-2014'), ('1#2014-2015'), ('2#2014-2015');

Select Query

SELECT * FROM test WHERE contarctNumber LIKE "%2014-2015";

Output

| CONTARCTNUMBER |
------------------
|    1#2014-2015 |
|    2#2014-2015 |

edited Apr 06 '13 at 08:12

answered Apr 06 '13 at 07:51

Sumit Bijvani

8,154
17
50
82

I tried that but, when I select year 2014-2015 it still displays records of 2013-2014 because they both have 2014 in common. – ashah142 Apr 06 '13 at 07:57
1

@ashah142 no it will not, you can check the demo here, http://www.sqlfiddle.com/#!2/45ea9/1 – Sumit Bijvani Apr 06 '13 at 08:07
it seems to be working here but with '%$year' it still show records from 2013-2014. – ashah142 Apr 06 '13 at 08:11
$query="select * ......"; echo $query;...and check output in database....to get some ideas – thumber nirmal Apr 06 '13 at 08:14
@ashah142 can you show me dropdown code, so i will help you better, or echo your code, it will help you – Sumit Bijvani Apr 06 '13 at 08:26

Jon · Answer 2 · 2013-04-06T08:12:58.723

0

If the contractNumber always end in the date range, why not use:

SELECT * FROM contract WHERE contarctNumber LIKE "%$year";

This means it will match anything before the $year selected but still match the range specified.

Option #2:

SELECT * from contract where RIGHT(contarctNumber, 9) = '$year';

edited Apr 06 '13 at 08:12

answered Apr 06 '13 at 07:52

Jon

4,746
2
24
37

I tried that but, when I select year 2014-2015 it still displays records of 2013-2014 because they both have `2014` – ashah142 Apr 06 '13 at 07:56
the `-` is not a special character in the `like` command, so the statement should work. What type of field is `contractNumber`? You might want to try `BINARY LIKE "%$year"` – Jon Apr 06 '13 at 08:01
contarctNumber is varchar. so i guess BINARY LIKE is not an option. – ashah142 Apr 06 '13 at 08:04
1

@ashah142 You can always use `SELECT * from contract where RIGHT(contarctNumber, 9) = '$year';` if nothing else is working for you. – Jon Apr 06 '13 at 08:06

display records of a specific year in php mysql

2 Answers2