php mysql 查询类似（包含）

发布于 2024-10-31 12:53:39 字数 1180 浏览 5 评论 0原文

我正在尝试按 db 列中包含的单词过滤 sql 查询。

这是有效的（请不要告诉我“Having”是错误的......它不适用于“Where”）

    $query = sprintf("SELECT *, ( 3959 * acos( cos( radians('%s') ) * cos( radians( `Lat` ) ) * cos( radians( `Long` ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( `Lat` ) ) ) ) AS distance FROM Tutors HAVING distance < '%s' ORDER BY distance",
      mysql_real_escape_string($lat),
      mysql_real_escape_string($lng),
      mysql_real_escape_string($lat),
      mysql_real_escape_string($radius));
    $result = mysql_query($query, $dbConn);

我想添加以下内容：

    $query = sprintf("SELECT *, ( 3959 * acos( cos( radians('%s') ) * cos( radians( `Lat` ) ) * cos( radians( `Long` ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( `Lat` ) ) ) ) AS distance FROM Tutors HAVING distance < '%s' AND SubjectList like '%s' ORDER BY distance",
      mysql_real_escape_string($lat),
      mysql_real_escape_string($lng),
      mysql_real_escape_string($lat),
      mysql_real_escape_string($radius),
      mysql_real_escape_string($subject));
    $result = mysql_query($query, $dbConn);

原文

I'm trying to filter sql query by a word that is contained within a db column.

This is working (Please don't tell me the Having is wrong... it doesn't work with Where)

    $query = sprintf("SELECT *, ( 3959 * acos( cos( radians('%s') ) * cos( radians( `Lat` ) ) * cos( radians( `Long` ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( `Lat` ) ) ) ) AS distance FROM Tutors HAVING distance < '%s' ORDER BY distance",
      mysql_real_escape_string($lat),
      mysql_real_escape_string($lng),
      mysql_real_escape_string($lat),
      mysql_real_escape_string($radius));
    $result = mysql_query($query, $dbConn);

I'd like to add something like:

    $query = sprintf("SELECT *, ( 3959 * acos( cos( radians('%s') ) * cos( radians( `Lat` ) ) * cos( radians( `Long` ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( `Lat` ) ) ) ) AS distance FROM Tutors HAVING distance < '%s' AND SubjectList like '%s' ORDER BY distance",
      mysql_real_escape_string($lat),
      mysql_real_escape_string($lng),
      mysql_real_escape_string($lat),
      mysql_real_escape_string($radius),
      mysql_real_escape_string($subject));
    $result = mysql_query($query, $dbConn);

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

凉风有信 2024-11-07 12:53:39

这是现在有效的解决方案 - 感谢大家的帮助

$query = "SELECT *, ( 3959 * acos( cos( radians('". addslashes($lat) ."') ) * cos( radians( `Lat` ) ) * cos( radians( `Long` ) - radians('". addslashes($lng) ."') ) + sin( radians('". addslashes($lat) ."') ) * sin( radians( `Lat` ) ) ) ) AS distance FROM Tutors WHERE `SubjectList` LIKE '%". addslashes($subject) ."%' GROUP BY distance HAVING distance < '". addslashes($radius) ."'";

Here's the solution that now works - thanks for everyone's help

$query = "SELECT *, ( 3959 * acos( cos( radians('". addslashes($lat) ."') ) * cos( radians( `Lat` ) ) * cos( radians( `Long` ) - radians('". addslashes($lng) ."') ) + sin( radians('". addslashes($lat) ."') ) * sin( radians( `Lat` ) ) ) ) AS distance FROM Tutors WHERE `SubjectList` LIKE '%". addslashes($subject) ."%' GROUP BY distance HAVING distance < '". addslashes($radius) ."'";

回复收藏 0 原文

快乐很简单 2024-11-07 12:53:39

您是否尝试过：

SELECT *,(formula) AS distance FROM Tutors WHERE SubjectList LIKE '%s' ORDER BY distance HAVING distance < '%s'

WHERE 作用于所有行，而 HAVING 作用于聚合函数，因此您需要确保首先减少了聚合公式所作用的集合。这也将产生更有效的查询。

HAVING 与 WHERE 有时是一个非常棘手的区别，此链接很好地描述了它：

http://blog.sqlauthority.com/2007/07/04/sql-server-definition-comparison-and-difference- Between-having- and-where-子句/

Have you tried:

SELECT *,(formula) AS distance FROM Tutors WHERE SubjectList LIKE '%s' ORDER BY distance HAVING distance < '%s'

WHERE acts on all rows, whereas HAVING acts on aggregate functions, so you want to make sure that you have first reduced the set the aggregate formula is acting on. This will also yield a more efficient query.

HAVING vs. WHERE is sometimes a very sticky differentiation, this link describes it very well:

http://blog.sqlauthority.com/2007/07/04/sql-server-definition-comparison-and-difference-between-having-and-where-clause/

回复收藏 0 原文

~没有更多了~