Recent Topics

Changing date queries to EXTRACT - status and a trouble

Started by on Feb 10, 2007 – Contents updated: Feb 10, 2007

Feb 10, 2007 14:51    

Hi b2evo developers!

I've began to to 2 things: change double quotes to single ones when necessary, and changing the MySQL YEAR(date), MONTH(date), DAYOFMONTH(date) to EXTRACT(YEAR FROM date), EXTRACT(MONTH FROM date) and EXTRACT (DAY from date), respectively.

The objective of these changes is to make b2evo SQL queries more standart compliants, and make b2evo easir to port.

It's working ok. But i've come to a silly trouble :)

When i go to archives, I get a query (#20), like that:


SELECT SQL_CALC_FOUND_ROWS EXTRACT(YEAR from post_datestart) AS year, EXTRACT(MONTH
FROM post_datestart) AS month, COUNT(DISTINCT postcat_post_ID) AS count
FROM evo_posts INNER JOIN evo_postcats ON post_ID = postcat_post_ID INNER JOIN evo_categories ON postcat_cat_ID = cat_ID
WHERE (cat_blog_ID = 1)
  AND ( ( ( post_status = 'private'
  AND post_creator_user_ID = 1 )
  OR post_status IN ('published','protected') ) )
  AND (post_datestart <= '2007-02-10 12:41:22')
GROUP BY year, month
ORDER BY year DESC, month DESC

(as you can see, it's transformed from YEAR() and MONTH() to EXTRACT).

But, that query is processed by the result class, by the function query. Is where the question arises!

That function has a line:


if( !preg_match( '#SELECT \s+ (.+?) \s+ FROM#six', $this->sql, $matches ) )

The trouble is: the FROM keyword is used in EXTRACT syntax too.

With that, i get in the matches array:


Array ( [0] => SELECT SQL_CALC_FOUND_ROWS EXTRACT(YEAR from [1] => SQL_CALC_FOUND_ROWS EXTRACT(YEAR )

So, it falls here:
[php]
debug_die( 'No columns selected!' );
[php]

Changing the query to:


SELECT SQL_CALC_FOUND_ROWS YEAR(post_datestart) AS year, MONTH(post_  ...

I get the right answer.
and the array:


Array ( [0] => SELECT SQL_CALC_FOUND_ROWS YEAR(post_datestart) AS year, EXTRACT(MONTH FROM [1] => SQL_CALC_FOUND_ROWS YEAR(post_datestart) AS year, EXTRACT(MONTH )

What shall I do?

The easier (and not clean way) is to make the regex case-sensitive, and I write the FROM from extract in lower case.

BTW, why is that verification on results class necessary?

Thanks on advance.

[]'s
- Walter

Feb 10, 2007 16:30

Hi guys. I think that I found it.

Just replace the regex:


#SELECT \s+ (.+?) \s+ FROM#six

with:


#SELECT \s+ (.+) \s+ FROM#six

But I think that it could break something if that class were used with user input, and in the user input does exists a FROM.

[]'s
- Walter


Form is loading...

Content Mangement System – This forum is powered by b2evolution CMS, a complete engine for your website.