MySQL SELECT using PHP variable

Question

I'm at a bit of a loss. Hoping there are some clever people out there :) I have a database with lots of records of current real estate listings. I want to analyze them on an area level (e.g. West Village, SOHO, etc.). At the same time, make sure I exclude outliers caused by incorrect manual entries (e.g. 0 too much/little).

I would like to do the following:

Set parameter values
- Check the average price/sq.f for rent/sales in an area and store them in variables
- Define outlier boundaries MIN (e.g. 33% of average) and MAX (e.g. 5x average)
Select relevant data fields which match:
- Area
- Price (between MIN and MAX)

To get the code to work I started 'easier' by doing price averages on entire database (although I would like them by area) and then try to apply them to the SELECT statements. But keep getting two types of errors:

syntax error, unexpected '$avg_sell_price_sqf' (T_VARIABLE)
Column not found: 1054 Unknown column '$minFactor'

I can get around the second error by replacing "$" with "@" for the variabels in the SELECT statement (e.g. @minFactor). But then it seems that they are not stored with values so my SELECT becomes 0.

The CODE:

$avg_rent_price_sqf = DB::raw ('SUM(CASE WHEN listings.Listing_type = "RENT" THEN listings.Property_AnnualRent      ELSE 0 END)') / DB::raw ('SUM(CASE WHEN listings.Listing_type = "RENT" THEN listings.Property_Area  ELSE 0 END)')

$avg_sell_price_sqf = DB::raw ('SUM(CASE WHEN listings.Listing_type = "SELL" THEN listings.Property_SellingPrice    ELSE 0 END)') / DB::raw ('SUM(CASE WHEN listings.Listing_type = "SELL" THEN listings.Property_Area  ELSE 0 END)')

$maxFactor          = 5; //5x average sq.f price

$minFactor          = 0.33; //1/3 of average sq.f price

$areas = DB::table('listings')
                      ->join('buildings', 'buildings.id'    , '=','listings.Building_id')                                                   
                      ->select( 
                                'buildings.id',
                                'buildings.Area', 
                                'buildings.Area', 
                                DB::raw ('SUM(CASE WHEN listings.Listing_type = "RENT" AND ( (listings.Property_AnnualRent/ listings.Property_Area)     BETWEEN ($minFactor * $avg_rent_price_sqf)  AND ($maxFactor * $avg_rent_price_sqf) )    THEN 1                              ELSE 0 END) as rent_count'),
                                DB::raw ('SUM(CASE WHEN listings.Listing_type = "RENT" AND ( (listings.Property_AnnualRent/ listings.Property_Area)     BETWEEN ($minFactor * $avg_rent_price_sqf)  AND ($maxFactor * $avg_rent_price_sqf) )    THEN listings.Property_Area         ELSE 0 END) as rent_sum_area'),
                                DB::raw ('SUM(CASE WHEN listings.Listing_type = "RENT" AND ( (listings.Property_AnnualRent/ listings.Property_Area)     BETWEEN ($minFactor * $avg_rent_price_sqf)  AND ($maxFactor * $avg_rent_price_sqf) )    THEN listings.Property_AnnualRent   ELSE 0 END) as rent_sum_price'),                                    
                                DB::raw ('SUM(CASE WHEN listings.Listing_type = "SELL" AND ( (listings.Property_SellingPrice/ listings.Property_Area)   BETWEEN ($minFactor * $avg_sell_price_sqf)  AND ($maxFactor * $avg_sell_price_sqf) )    THEN 1                              ELSE 0 END) as sell_count'),
                                DB::raw ('SUM(CASE WHEN listings.Listing_type = "SELL" AND ( (listings.Property_SellingPrice/ listings.Property_Area)   BETWEEN ($minFactor * $avg_sell_price_sqf)  AND ($maxFactor * $avg_sell_price_sqf) )    THEN listings.Property_Area         ELSE 0 END) as sell_sum_area'),
                                DB::raw ('SUM(CASE WHEN listings.Listing_type = "SELL" AND ( (listings.Property_SellingPrice/ listings.Property_Area)   BETWEEN ($minFactor * $avg_sell_price_sqf)  AND ($maxFactor * $avg_sell_price_sqf) )    THEN listings.Property_SellingPrice ELSE 0 END) as sell_sum_price')
                                )
                      ->groupBy('buildings.Area')
                      ->get();

I presume this is possible, as I have seen the following online:

Calculate in select: http://www.geeksengine.com/database/basic-select/arithmetic-operations.php
Variables in select: https://dba.stackexchange.com/questions/4169/how-to-use-variables-inside-a-select-sql-server
Multiple selects: Reference alias (calculated in SELECT) in WHERE clause

Any help very much appreciated as I am new to SQL (and PHP)!

Looks like a simple case of using variables in single quotes! — RMcLeod, Jul 30 '14 at 15:18
RTFM? http://php.net/manual/en/language.types.string.php#language.types.string.syntax.single — Marc B, Jul 30 '14 at 15:21
Gents - you are very right! I had a feeling it was something simple like that :) — Peder Wessel, Jul 30 '14 at 20:24

score -1 · Accepted Answer · answered Jul 30 '14 at 15:17

-1

in php, statements have to end with an ; that is the first error

see what happens if you fix that, because executions stops there

EDIT: ~~dont use @ while testing/debugging (or ever actually), it suppresses errors in that line~~ << sorry missred that

answered Jul 30 '14 at 15:17

birdspider

3,034
1
16
25

He is using @ as in a mysql variable not as in the PHP error suppressor – RMcLeod Jul 30 '14 at 15:20
I still think that the problem is that line 1 and 2 dont have a `;` – birdspider Jul 30 '14 at 15:22
Good catch. When I added `;` at the end of line 1 and 2 I got a new error message: Object of class Illuminate\Database\Query\Expression could not be converted to int – Peder Wessel Jul 30 '14 at 20:05

MySQL SELECT using PHP variable

1 Answers1