douzhigan1687 2014-11-30 03:58 采纳率: 0%
浏览 32
已采纳

如何正确子查询 - 或者这是怎么回事? [关闭]

I'm trying to fetch a load of database entries which include geo data that has to be fetched from another table. When i try to run a custom distance function on them it causes an error, I have tried to include it as a sub query but that does not seem to work either (The function does work).

$bsns = $this->db->select('businesses.name, businesses.about')
                            ->select('postcodes.postcode, postcodes.lat AS lat, postcodes.long AS long')
                            ->select('industries.name')
                            ->select('get_distance_in_miles_between_geo_locations('.$geo['lat'].', '.$geo['long'].', lat, long) AS distance', null, false)
                            ->from('businesses')
                            ->join('postcodes', 'postcodes.postcode = businesses.postcode', 'left')
                            ->join('industries', 'industries.IN_ID = businesses.industry', 'left')
                            ->order_by('distance', 'ASC')
                            ->get(); 

This is the code i'm using at the moment, The error:

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'long, `industries`.`name`, get_distance_in_miles_between_geo_locations(50.344178' at line 1

New error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'businesses`) LEFT JOIN `postcodes` ON `postcodes`.`postcode` = `businesses`.`pos' at line 1

SELECT `businesses`.`name`, `businesses`.`about`, `postcodes`.`postcode`, `postcodes`.`lat`, `postcodes`.`long`, `industries`.`name`, get_distance(50.344178, `-4`.`757147`, `lat`, `long`)` AS distance FROM (`businesses`) LEFT JOIN `postcodes` ON `postcodes`.`postcode` = `businesses`.`postcode` LEFT JOIN `industries` ON `industries`.`IN_ID` = `businesses`.`industry` ORDER BY `distance` ASC
  • 写回答

1条回答 默认 最新

  • doudi8298 2014-11-30 04:03
    关注

    You're mixing single quotes, the MySQL parser does not like it. It thinks you're done with your statement.

    select('get_distance_in_miles_between_geo_locations('.$geo['lat'].', '.$geo['long'].', lat, long) AS distance', null, false)
    

    should be :

    select('get_distance_in_miles_between_geo_locations('.$geo["lat"].', '.$geo["long"].', lat, long) AS distance', null, false)
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 怎么改成循环输入删除(语言-c语言)
  • ¥15 安卓C读取/dev/fastpipe屏幕像素数据
  • ¥15 pyqt5tools安装失败
  • ¥15 mmdetection
  • ¥15 nginx代理报502的错误
  • ¥100 当AWR1843发送完设置的固定帧后,如何使其再发送第一次的帧
  • ¥15 图示五个参数的模型校正是用什么方法做出来的。如何建立其他模型
  • ¥100 描述一下元器件的基本功能,pcba板的基本原理
  • ¥15 STM32无法向设备写入固件
  • ¥15 使用ESP8266连接阿里云出现问题