我试图使用mysql select只返回某一列"category"包含id的结果,例如中的字段中可能有"1,2,4"。我想使用一个变量来查看字段中是否存在该id。
到目前为止的代码:
function returnSQLByCategory($criteria) {
$lat = $criteria['lat'];
$lng = $criteria['lng'];
$categoryid = $criteria['categoryid'];
$page_number = $criteria['page_number'];
$nb_display = $criteria['nb_display'];
//if($page_number=='') $page_number=1;
//if($nb_display=='') $nb_display=10;
$start = ($page_number*$nb_display)-$nb_display;
$sql = "SELECT company, name, lng, lat, address, city, country, zip, link,
( 3959 * acos( cos( radians('".mysql_real_escape_string($lat)."') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('".mysql_real_escape_string($lng)."') ) + sin( radians('".mysql_real_escape_string($lat)."') ) * sin( radians( lat ) ) ) ) AS distance
FROM locations WHERE categoryid LIKE '%$categoryid%'";
$sql .= " ORDER BY distance";
return $sql;
}
这不起作用,必须返回"1",因为我只能看到类别ID为"1"的行。如果我手动放入语句"2"或示例,它将按预期工作!
非常感谢您的帮助,谢谢。
为什么不使用IN?
categoryid IN ('.$categoryid.')
SELECT
company,
name,
lng,
lat,
address,
city,
country,
zip,
link
FROM
locations
WHERE
MATCH (categoryid) AGAINST ($categoryid IN BOOLEAN MODE)
使用MATCH怎么样?不过,您的类别id字符串需要用一个空格分隔,而不是用逗号分隔。即("1 2 3 4"),并且在sql语句中用单引号括起来。
$categoryid = 1;
$categoryid = mysql_real_escape_string($categoryid);
$sql = "SELECT * FROM `locations` WHERE categoryid = '$categoryid'";
$result = mysql_query($sql);