php - SQL自定义搜索函数: unable to join two tables

Question

  // grab the search types.
  $searchSQL = "SELECT  * FROM jsprofile WHERE ";

  $types = array();
  $types[] = isset($_GET['sicno'])?"`name` LIKE '%{$searchTermDB}%'":'';
  $types[] = isset($_GET['sname'])?"`icno` LIKE '%{$searchTermDB1}%'":'';
  $types[] = isset($_GET['sgender'])?"`gender` LIKE '%{$searchTermDB2}%'":'';

  $types = array_filter($types, "removeEmpty"); // removes any item that was empty (not checked)

  if (count($types) < 1)
     $types[] = "`icno` LIKE '%{$searchTermDB}%'"; // use the body as a default search if none are checked

      $andOr = isset($_GET['matchall'])?'AND':'AND';
  $searchSQL .= implode(" {$andOr} ", $types) . "  ORDER BY `icno`"; // order by title.

  $searchResult = mysql_query($searchSQL) or trigger_error("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$searchSQL}");

//  if (mysql_num_rows($searchResult) < 1) {
  ///   $error[] = "The search term provided {$searchTerms} yielded no results.";

从现在开始一切正常。我能够搜索...但是当我尝试连接两个表时..

  // grab the search types.
  $searchSQL = "SELECT  * FROM jsprofile INNER JOIN medi WHERE ";

  $types = array();
  $types[] = isset($_GET['sicno'])?"`name` LIKE '%{$searchTermDB}%'":'';
  $types[] = isset($_GET['sname'])?"`icno` LIKE '%{$searchTermDB1}%'":'';
  $types[] = isset($_GET['sgender'])?"`gender` LIKE '%{$searchTermDB2}%'":'';
  $types[] = isset($_GET['medical'])?"`medical` LIKE '%{$searchTermDB2}%'":'';

  $types = array_filter($types, "removeEmpty"); // removes any item that was empty (not checked)

  if (count($types) < 1)
     $types[] = "`icno` LIKE '%{$searchTermDB}%'"; // use the body as a default search if none are checked

      $andOr = isset($_GET['matchall'])?'AND':'AND';
  $searchSQL .= implode(" {$andOr} ", $types) . "  ORDER BY `icno`"; // order by title.

  $searchResult = mysql_query($searchSQL) or trigger_error("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$searchSQL}");

//  if (mysql_num_rows($searchResult) < 1) {
  ///   $error[] = "The search term provided {$searchTerms} yielded no results.";

当我这样做时..似乎没有结果，查询有什么问题吗？

Answer 1

您应该检查 MySQL JOIN 语法 ( http://dev.mysql.com/doc/refman/5.0/en/join.html )

您需要在其中放置一个ON子句

这一行:

$searchSQL = "SELECT  * FROM jsprofile INNER JOIN medi WHERE ";

应该是这样的:

$searchSQL = "SELECT  * FROM jsprofile INNER JOIN medi On jsprofile.[key] = medi.[key] WHERE ";

显然，将您的键名替换为该查询