MYSQL使用LIKE搜索具有不同列的多个表 [英] MYSQL Searching multiple tables with different columns using LIKE

问题描述

我需要能够在多个表上进行搜索，并返回与搜索到的词相似的所有部分.我的问题是表格具有不同数量的列.他们确实有名称相似的列.首先，PHP检查"productnumber"表，以找到具有相似零件编号的所有零件.然后，它在每个表中搜索以找到匹配的数字.

I need to be able to do a search on multiple tables and return all parts that are similar to the searched term. My problem is that the tables have varying amounts of columns. They do have columns with similar names though. First the PHP checks the "productnumber" table to find all parts with the similar part number. Then it searches through each table to find a matching number.

现在，如果在组件"表中找到相似的零件，它将不会显示从适配器"或连接器"中找到的任何零件.与其选择所有列，而不是搜索所有表，我想搜索在所有三个表中找到的这三个列，并返回在所有表中找到的结果:

Right now if a similar part is found in the Components table, it will not display any parts found from Adapters or Connectors. Instead of selecting all columns and neglecting to search all tables I'd like to search for these three columns which are found in all three tables and return results found in all tables:

part_num

图片

页面

if(isset($_GET['num'])) {
$num = $_GET['num'];
$numresult = mysql_query("SELECT * FROM productnumber WHERE part_num LIKE '%$num%'");

  if ($numresult) {

    while ($row = mysql_fetch_array($numresult)) {

        if ($row["title"] == "connectors") {
            $numtitle = "connectors";
            $result = mysql_query("SELECT * FROM connectors WHERE part_num LIKE '%$num%'");
        }

        if ($row["title"] == "adapters") {
            $numtitle = "adapters";
            $result = mysql_query("SELECT * FROM adapters WHERE part_num LIKE '%$num%'");
        }

        if ($row["title"] == "components") {
            $numtitle = "components";
            $result = mysql_query("SELECT * FROM components WHERE part_num LIKE '%$num%'");
        }

    }

  }

}

任何有关我的问题的帮助将不胜感激:]

Any help regarding my question would be greatly appreciated :]

推荐答案

 while ($row = mysql_fetch_array($numresult)) {

        if ($row["title"] == "connectors") {
            $numtitle = "connectors";
            $result[] = mysql_query("SELECT * FROM connectors WHERE part_num LIKE '%$num%'");
        }

        if ($row["title"] == "adapters") {
            $numtitle = "adapters";
            $result[] = mysql_query("SELECT * FROM adapters WHERE part_num LIKE '%$num%'");
        }

        if ($row["title"] == "components") {
            $numtitle = "components";
            $result[] = mysql_query("SELECT * FROM components WHERE part_num LIKE '%$num%'");
        }

    }

您必须进行另一个循环才能从结果数组

you have to make another loop to retrieve data from result array

这篇关于MYSQL使用LIKE搜索具有不同列的多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！