php - MySQL SELECT 使用 PHP 变量

Question

我有点不知所措。希望那里有一些聪明人 :) 我有一个数据库，其中包含大量当前房地产列表的记录。我想在区域层面上分析它们(例如西村、SOHO 等)。同时，确保我排除了由不正确的手动输入引起的异常值(例如 0 太多/太少)。

我想做以下事情:

1. 设置参数值
   • 检查区域的租金/销售额的平均价格/sq.f，并将它们存储在变量中
   • 定义异常值边界 MIN(例如平均值的 33%)和 MAX(例如平均值的 5 倍)
2. 选择匹配的相关数据字段:
   • 地区
   • 价格(介于 MIN 和 MAX 之间)

为了让代码正常工作，我开始“更简单”地对整个数据库进行价格平均(尽管我希望按区域计算)，然后尝试将它们应用于 SELECT 语句。但是不断出现两种类型的错误:

• syntax error, unexpected '$avg_sell_price_sqf' (T_VARIABLE)

• Column not found: 1054 Unknown column '$minFactor'

我可以通过将 SELECT 语句中的变量替换为“@”(例如@minFactor)的“$”来解决第二个错误。但似乎它们没有存储值，所以我的 SELECT 变为 0。

代码:

$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();

我认为这是可能的，因为我在网上看到了以下内容:

• 在选择中计算:http://www.geeksengine.com/database/basic-select/arithmetic-operations.php
• 选择中的变量:https://dba.stackexchange.com/questions/4169/how-to-use-variables-inside-a-select-sql-server
• 多选:Reference alias (calculated in SELECT) in WHERE clause

非常感谢任何帮助，因为我是 SQL(和 PHP)的新手!

Answer 1

在 php 中，语句必须以 ; 结束，这是第一个错误

看看如果你解决这个问题会发生什么，因为执行停止了

编辑: 在测试/调试(或实际上)时不要使用 @，它会抑制该行中的错误 <<抱歉错过了