What's wrong with this SQL query?

[Chris Dunning]

I'm trying to install the "Events Calendar" contribution on a store running MS1 and running into many errors. I'm using the latest version of that contrib that's marked for MS1.

 

Like many others in the forum, I got errors regarding the splitPageResults function. First it was "cannot accept argument 3 for splitPageResults as reference" or something like that. I fixed that using a method posted elsewhere in the forums, that producted an error about a nonexistant argument 4 for the same function. I put in the 4th argument (number of rows per page) and got past that error, now I'm getting a syntax error on the SQL query.

 

Some suggested replacing the includes/classes/splitpageresults.php file with one from the MS2 release - that fixes the events_calendar.php file but breaks the product listing by categories - no good. Seems I can't have my cake and eat it too. So...here's the SQL query. The error is the generic 1064 syntax error and it points to the first few arguments in the query:

 

$ev_query = tep_db_query("select *, DAYOFMONTH(start_date) AS day, MONTH(start_date) AS month, YEAR(start_date) AS year from ". TABLE_EVENTS_CALENDAR ." where start_date > '". date('Y-m-d H:i:s') ."' and language_id = '". $languages_id ."' order by start_date");

 

What's wrong with that syntax?

 

I don't understand what this is for:

"select *, DAYOFMONTH(start_date) AS day, MONTH(start_date) AS month, YEAR(start_date) AS year

Can anyone explain that to me? I've never seen syntax like that used in SQL. The column names in the table are not "DAYOFMONTH(start_date)" but simply "start_date." What is that used for?

[Guest]

I copied this from the MySQL manual, I use this all of the time.

 

DAYOFMONTH(date) 
Returns the day of the month for date, in the range 1 to 31. 
mysql> SELECT DAYOFMONTH('1998-02-03');
       -> 3

 

http://www.mysql.com/doc/en/Date_and_time_functions.html

 

In case it was the second bit you were referring to "AS whatever",

 

A select_expression can be given an alias using AS alias_name. The alias is used as the expression's column name and can be used in GROUP BY, ORDER BY, or HAVING clauses. For example: 
mysql> SELECT CONCAT(last_name,', ',first_name) AS full_name
   -> FROM mytable ORDER BY full_name;

 

http://www.mysql.com/doc/en/SELECT.html

[Chris Dunning]

Right! Thanks for that. It seems as if the problem is not really with the SQL query, but with the splitPageResults function. I got around it by using both versions of split_page_results.php. I edited the MS2 version, renaming every function to xfunction and the file to xsplit_page_results.php, then changing the function calls within events_calendar.php. Yes, it's a workaround...but I spent way too much time dealing with that last night.