mysql variable in where clause

[Destramic]

hey guys here is my query and my only row in my database...i'm having a small problem when i try adding

AND @distance < 5

in my where clause...it doesn't display the row...does anyone have any idea why not please?

SELECT @latitude := :latitude,
				         @longitude := :longitude,
				         @distance_unit := :distance_unit,
				         @category_id := :category_id,
				         @item := :item,
				         CASE WHEN (@latitude IS NOT NULL AND 
				                    @longitude IS NOT NULL AND
				                    u.latitude IS NOT NULL AND
				                    u.longitude IS NOT NULL)
				         THEN @distance := (SELECT (IF(@distance_unit = 'Kilometers', 6371, 3959) *
                                                    2 * ASIN(SQRT(POWER(SIN((@latitude- u.latitude) *
                                                    pi()/180 / 2), 2) + COS(@latitude * 
		                                            pi()/180) * COS(u.latitude * pi()/180) * 
		                                            POWER(SIN((@longitude - u.longitude) * 
		                                            pi()/180 / 2), 2)))))
				         END,
		                 @distance_unit := IF (@distance = 1, REPLACE (@distance_unit, 's', ''), @distance_unit),
				         IF (@distance, CONCAT(TRUNCATE(@distance, 2), @distance_unit), 'Unknown') AS `distance`,
				         (SELECT amount FROM user_bids ORDER BY created_timestamp DESC) AS `amount`,
				         i.item_id, 
				         i.cover_image_source,
				         i.buy_now,
		                 i.title, 
				         i.quanity,
		                 i.price,
		                 i.p_and_p,
		                 (i.price + i.p_and_p) AS `total_price`,
		                 i.auction,
		                 i.condition,
		                 CONVERT_TZ(DATE_ADD(i.start_timestamp, INTERVAL concat(i.listing_duration) DAY), '+00:00', '+00:00') AS `end_timestamp`,
                         cu.code AS `seller_currency_code`
		          FROM items i
				  LEFT JOIN sub_categories sc ON sc.sub_category_id = i.sub_category_id
				  LEFT JOIN categories c ON c.category_id = sc.category_id AND c.category_id = :category_id
				  LEFT JOIN users u ON u.user_id = i.user_id
				  LEFT JOIN currencies cu ON cu.currency_id = u.currency_id
				  WHERE MATCH (i.title, i.description) AGAINST (:item IN BOOLEAN MODE)
		          AND @distance < 5
				  AND i.start_timestamp < NOW() 
		          AND DATE_ADD(i.start_timestamp, INTERVAL concat(i.listing_duration) DAY) >= NOW()
		          GROUP BY i.item_id
				  ORDER BY end_timestamp ASC

Array
(
    [0] => Array
        (
            [@latitude := 51.7328] => 51.7328
            [@longitude := -3.0658] => -3.0658
            [@distance_unit := ' Miles'] =>  Miles
            [@category_id := ''] => 
            [@item := 'xbox'] => xbox
            [CASE WHEN (@latitude IS NOT NULL AND 
				                    @longitude IS NOT NULL AND
				                    u.latitude IS NOT NULL AND
				                    u.longitude IS NOT NULL)
				         THEN @distance := (SELECT (IF(@distance_unit = 'Ki] => 0.000032539954191771116
            [@distance_unit := IF (@distance = 1, REPLACE (@distance_unit, 's', ''), @distance_unit)] =>  Miles
            [distance] => 0.00 Miles
            [amount] => 
            [item_id] => 1
            [cover_image_source] => xboxone.jpg
            [buy_now] => 0
            [title] => XBOX One
            [quanity] => 0
            [price] => 99.00
            [p_and_p] => 0.00
            [total_price] => 99.00
            [auction] => 1
            [condition] => New-Other
            [end_timestamp] => 2015-07-17 10:51:45
            [seller_currency_code] => GBP
        )

)

[Muddy_Funster]

I have never known anyone truncate anything other than a table in mysql sql...are you trying to round off the number?

[Destramic]

yeah i used TRUNCATE to round and to display only 2 numbers ending the decimal place...although that isnt the problem...

 

using

AND @distance < 5

in the where clause is...basically i want to display items which are withing 5 mile distance only

[Destramic]

http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_truncate

[Barand]

Items in the WHERE clause need to be columns in the table rows (or expressions/functions using those values).

 

You need either to duplicate the calculation in the WHERE

WHERE 2 * ASIN(SQRT(POWER(SIN((@latitude- u.latitude) * pi().... < 5

or use a HAVING clause

[Destramic]

SELECT @latitude := :latitude,
				         @longitude := :longitude,
				         @distance_unit := :distance_unit,
				         @category_id := :category_id,
				         @item := :item,
				         CASE WHEN (@latitude IS NOT NULL AND 
				                    @longitude IS NOT NULL AND
				                    u.latitude IS NOT NULL AND
				                    u.longitude IS NOT NULL)
				         THEN @distance := (SELECT (IF(@distance_unit = 'Kilometers', 6371, 3959) *
                                                    2 * ASIN(SQRT(POWER(SIN((@latitude- u.latitude) *
                                                    pi()/180 / 2), 2) + COS(@latitude * 
		                                            pi()/180) * COS(u.latitude * pi()/180) * 
		                                            POWER(SIN((@longitude - u.longitude) * 
		                                            pi()/180 / 2), 2)))))
				         END,
		                 @distance_unit := IF (@distance = 1, REPLACE (@distance_unit, 's', ''), @distance_unit),
				         IF (@distance, CONCAT(TRUNCATE(@distance, 2), @distance_unit), 'Unknown') AS `distance`,
				         (SELECT amount FROM user_bids ORDER BY created_timestamp DESC) AS `amount`,
				         i.item_id, 
				         i.cover_image_source,
				         i.buy_now,
		                 i.title, 
				         i.quanity,
		                 i.price,
		                 i.p_and_p,
		                 i.auction,
		                 i.condition,
		                 (i.price + i.p_and_p) AS `total_price`,
		                 CONVERT_TZ(DATE_ADD(i.start_timestamp, INTERVAL concat(i.listing_duration) DAY), '+00:00', '+00:00') AS `end_timestamp`,
                         cu.code AS `seller_currency_code`
		          FROM items i
				  LEFT JOIN sub_categories sc ON sc.sub_category_id = i.sub_category_id
				  LEFT JOIN categories c ON c.category_id = sc.category_id AND c.category_id = :category_id
				  LEFT JOIN users u ON u.user_id = i.user_id
				  LEFT JOIN currencies cu ON cu.currency_id = u.currency_id
				  WHERE MATCH (i.title, i.description) AGAINST (:item IN BOOLEAN MODE)
		  
				  AND i.start_timestamp < NOW() 
		          
		          GROUP BY i.item_id
		    HAVING end_timestamp >= NOW()
		    AND @distance < 50
				  ORDER BY end_timestamp ASC
		          LIMIT 10 OFFSET 0

having worked thank you