Selecting only a substring in MySQL and then returning it

Question

Possible Duplicate:
mysql_fetch_array() expects parameter 1 to be resource, boolean given in select

In MySQL I am trying to select only the distinct years from over a thousand rows using a textual field called 'edate' that is formatted "1-Jan-2010":

SELECT DISTINCT DATE_FORMAT(STR_TO_DATE(edate, '%e-%b-%Y'), %Y) 
  FROM addnews 
 WHERE status = 1 
   AND archives = 1 
   AND delete1 = 0

MySQL returns

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\WAMP\www\baycrossings\web\archives.php on line 28"

In MS SQL this works as follows:

select distinct(year(edate)) 
  from addnews 
 where status = 1 
   and archives = 1 
   and delete1 = 0

Does MySQL not allow substring selection? I am trying to avoid selecting hundreds of times more data than I need per query.

is there a special reason you are not using the date time column type in mysql? — Madara's Ghost, Aug 28 '11 at 04:38

score 1 · Accepted Answer · edited Aug 28 '11 at 04:25

1

Did you forget to quote the %Y second argument to DATE_FORMAT()?

Try changing your query call to look like this:

$result = mysql_query($your_query) or trigger_error(mysql_error());

That way you can see the actual error that MySQL is returning, as opposed to just an error about mysql_query() returning FALSE (which is what the error you pasted is saying).

Beyond that, you should not be storing dates as strings in the database. The native date field types should be used, as they allow date 'math' to be peformed without having to do some very expensive string-to-date conversion.

Since you're just trying to extract a year from a string date, assuming the date format is constant, try using:

SELECT ..., RIGHT(edate, 4)

which will be a far faster operation than a string-to-date-to-string round trip operation.

edited Aug 28 '11 at 04:25

Marc B

348,685
41
398
480

answered Aug 28 '11 at 03:14

Amber

477,764
81
611
541

The database was given to me this way, with thousands of dates formatted as varchar(50). Perhaps there is a MySQL command to reformat them into a date format... Thank you. – limedrop Aug 28 '11 at 19:44
If this (or another) answer allowed you to solve your problem, consider clicking the green outline of a checkmark to the left of it to mark it as Accepted. – Amber Sep 07 '11 at 07:20

score 0 · Answer 2 · answered Aug 28 '11 at 03:16

0

Is there a reason you're not just using the YEAR function in MySQL as well?

Anyway, the problem with your current query is that you need to put the %Y in single quotes, like this:

SELECT DISTINCT DATE_FORMAT(STR_TO_DATE(edate, '%e-%b-%Y'), '%Y') 
FROM addnews WHERE status=1 AND archives=1 AND delete1=0

answered Aug 28 '11 at 03:16

Derek Kromm

20,426
6
50
61

Wow, the single quotation mark wins! Thank you. P.S. The way the date is formatted is not the way MySQL understands dates if I understand correctly. Anyway YEAR(edate) did not work for me. – limedrop Aug 28 '11 at 19:40
i meant year(str_to_date()) instead of dateformat(str_to_date(),'%Y'). no biggie, glad we could help – Derek Kromm Aug 28 '11 at 19:43

score 0 · Answer 3 · answered Aug 28 '11 at 04:23

If you get that "boolean" error with a MySQL operation, it generally means your query failed, and the PHP query function has returned a boolean false. Your code should look like this:

$sql = "...";
$result = mysql_query($sql) or die(mysql_error());

If your query is invalid (syntax error, fails constraints, etc...), this construct will kill your script and output the exact reason for the error. Without the or die(), your could would take the boolean false that msyql_query() returns, and attempt to do another operation (fetch?), which now also fails, because you can't fetch a row from a "FALSE".

Selecting only a substring in MySQL and then returning it

3 Answers3