MYSQL : BETWEEN operator loses condition value on JOIN in (:) used

[TatwiraT]

Hei,
When I tried to execute a join query
SELECT `db_adverts`.* FROM `db_adverts` INNER JOIN `db_adverts` AS `c` ON `db_adverts`.`views` <= `c`.`limit` OR **'TIMESTAMP_VALUE_HERE'** BETWEEN `c`.`start_at` AND `c`.`end_at` WHERE `db_adverts`.`desktop` = :db_adverts.desktop: AND `db_adverts`.`enable` = :db_adverts.enable: GROUP BY `db_adverts`.`id`
the query keeps only the portion before the lase occurrence of colon ":" of the value TIMESTAMP_VALUE_HERE.
I manage to bypass this issue using UNIX TIME TRANSFORMATION
thanks

[lonnieezell]

Can you provide the code you use to create that query, please.

[TatwiraT]

here it is

public function get_ads( $where = array() )
	{
		$rows = $this->select(array( db_table('adverts').'.*' ))
		->where($where)
		->join(db_table('adverts') .' AS c' , db_table('adverts').'.views <= c.limit OR '.time().' < UNIX_TIMESTAMP('.db_table('adverts').'.end_at)', 'INNER')
		->orderBy('ord','ASC')
		->groupBy(db_table('adverts').'.id')
		//->getCompiledSelect();
		->findAll();
		return $rows;
	}

[lonnieezell]

Looking at your query you're not quoting the time value so it would expect that to be a keyword. Also - there wouldn't be a colon in there since time() returns an integer. However, if you were using something like "04:23:58" you'd have to provide a quote around it.

This does not appear to be a bug in the framework.

[TatwiraT]

actualy there is only two login on join, one is time() int, and the other is table column , not a value
thank