PHP、MySQL联合查询错误 - PHP, MySQL UNION Query Error

PHP, MySQL UNION Query Error

本文关键字：查询错误 MySQL PHP | 更新日期: 2023-09-27

我有一个问题，试图调试这个查询使用PHP/MySQl-AJAX:变量$param是AJAX调用表单文本框的结果。从本质上讲，我试图在三个数据库表上生成一个动态搜索，不幸的是这些表有不同的字段(因此有concat)。数据是使用不同方法生成的具有空间位置(对于前两个表)的作业的地址，最后一个表是非空间数据。

            $fetch = "(SELECT JobNo AS JobNo, CONCAT(Title1, '-', Title2, '-', Title3) AS Description, 'Hurricane' as type FROM Hurricanev2 WHERE Title1 REGEXP '$param' OR Title2 REGEXP '$param' OR Title3 REGEXP '$param') 
       UNION ALL
       (SELECT jobNo AS JobNo, description As Description, address As Geocoded_address, 'geocoded' as type FROM jr WHERE description REGEXP '$param' OR address REGEXP '$param')
       UNION ALL
       (SELECT job As JobNo, description As Description, 'plan' as type FROM register WHERE description REGEXP '$param')";

    while ( $row = mysql_fetch_object( $fetch ) ) {
        $sResults .= '<tr>';
        $sResults .= '<td>' . $row['JobNo'] . '</td>';
        $sResults .= '<td>' . $row['Description'] . '</td></tr>';
    }

thanks in advance

不能对列数不同的结果集进行合并。

试试下面的;

$fetch = "(SELECT JobNo AS JobNo, CONCAT(Title1, '-', Title2, '-', Title3) AS Description, 'Hurricane' as type, 'extra' FROM Hurricanev2 WHERE Title1 REGEXP '$param' OR Title2 REGEXP '$param' OR Title3 REGEXP '$param') 
   UNION ALL
   (SELECT jobNo AS JobNo, description As Description, address As Geocoded_address, 'geocoded' as type FROM jr WHERE description REGEXP '$param' OR address REGEXP '$param')
   UNION ALL
   (SELECT job As JobNo, description As Description, 'plan' as type, 'extra' FROM register WHERE description REGEXP '$param')";

对于UNION，每个SELECT需要相同数量的列。尝试在第一次和最后一次SELECT上添加额外的null以均匀输入。