php - 使用自然连接运行查询时出现问题

Question

我是否正确地进行了这种自然连接？每次运行查询时我都得到 NULL

 $con = mysqli_connect("localhost:3307","root","");

    mysqli_select_db($con, 'project');

    // Check connection
    if (!$con){
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
    };

    mysqli_query ($con,"set character_set_results='utf8'");
$inv_id= "Please enter an invoice ID";
$cus_name = "";
$cus_phone = "";
$inv_total = "" ;
$ship_date = "";

if(isset($_POST['submit'])){
    $inv_id = $_POST['inv_id'];
    //echo $inv_id;
    $sql = 'SELECT inv_total, ship_date, cus_name, cus_phone
            from invoiceheader natural join shipment natural join customer
            where inv_id = \'' . $inv_id . '\' '; 
            //echo $sql;

    $result = mysqli_query($con, $sql);
        if(!$result)
           {
                echo "SQL Select query failed!";
        } 
        $row = $result->fetch_assoc();  
        //var_dump($row);   
        $cus_name = $row["cus_name"];
        $cus_phone = $row["cus_phone"];
        $inv_total = $row["inv_total"];
        $ship_date = $row["ship_date"];
}

?>

我在 var_dump 上得到 null，在 sql 命令行上得到空集，但没有错误。

mysql> select * from invoiceheader;
+--------+---------------------+--------+-----------+---------------------+
| inv_id | inv_date            | cus_id | inv_total | last_modified_date  |
+--------+---------------------+--------+-----------+---------------------+
| 111111 | 2016-11-30 23:35:22 |      1 |        53 | 2016-11-30 23:35:22 |
| 111112 | 2016-11-30 23:35:22 |      2 |      41.5 | 2016-11-30 23:35:22 |
| 111113 | 2016-11-30 23:35:22 |      3 |        26 | 2016-11-30 23:35:22 |
| 111114 | 2016-11-30 23:35:22 |      4 |        51 | 2016-11-30 23:35:22 |
| 111115 | 2016-11-30 23:35:22 |      5 |     38.75 | 2016-11-30 23:35:22 |
| 111116 | 2016-11-30 23:35:22 |      6 |        55 | 2016-11-30 23:35:22 |
| 111117 | 2016-11-30 23:35:22 |      7 |     41.25 | 2016-11-30 23:35:22 |
| 111118 | 2016-11-30 23:35:22 |      8 |     71.25 | 2016-11-30 23:35:22 |
| 111119 | 2016-11-30 23:35:22 |      9 |     28.25 | 2016-11-30 23:35:22 |
| 111120 | 2016-11-30 23:35:22 |     10 |     69.25 | 2016-11-30 23:35:22 |
| 111121 | 2016-11-30 23:35:22 |      6 |      65.5 | 2016-11-30 23:35:22 |
| 111122 | 2016-11-30 23:35:22 |      3 |        58 | 2016-11-30 23:35:22 |
+--------+---------------------+--------+-----------+---------------------+
12 rows in set (0.00 sec)

mysql> select * from customer;
+--------+--------------------+------------+-----------------+--------------------+---------------+-------+-------+---------------+---------------------+---------------------+
| cus_id | cus_name           | cus_phone  | customer_email  | street             | city          | state | zip   | lastOrderDate | creation_date       | last_modified_date  |
+--------+--------------------+------------+-----------------+--------------------+---------------+-------+-------+---------------+---------------------+---------------------+
|      1 | John Murray        | 1111111111 | cus1@email.com  | 123 first street   | Egan          | MN    | 51224 | 2017-01-10    | 2017-04-05 19:16:07 | 2017-04-05 19:16:07 |
|      2 | Rachael Smith      | 2222222222 | cus2@email.com  | 123 second street  | Minnetonka    | MN    | 55266 | 2017-01-10    | 2016-12-30 23:35:22 | 2016-12-30 23:35:22 |
|      3 | Addison Brown      | 3333333333 | cus3@email.com  | 123 third street   | Bloominton    | MN    | 55757 | 2017-01-10    | 2016-12-30 23:35:22 | 2016-12-30 23:35:22 |
|      4 | Mike Lewis         | 4444444444 | cus4@email.com  | 123 fourth street  | Egan          | MN    | 51224 | 2017-01-10    | 2017-04-05 19:16:08 | 2017-04-05 19:16:08 |
|      5 | Matt Collins       | 5555555555 | cus5@email.com  | 123 fifth street   | New Hope      | MN    | 55336 | 2017-01-10    | 2016-12-30 23:35:22 | 2016-12-30 23:35:22 |
|      6 | Jonathan Elliot    | 6666666666 | cus6@email.com  | 123 sixth street   | Watertown     | MN    | 50221 | 2017-01-10    | 2016-12-30 23:35:22 | 2016-12-30 23:35:22 |
|      7 | Jessica Stratmeyer | 7777777777 | cus7@email.com  | 123 seventh street | Egan          | MN    | 51224 | 2017-01-10    | 2016-12-30 23:35:22 | 2016-12-30 23:35:22 |
|      8 | David Wilson       | 8888888888 | cus8@email.com  | 123 eighth street  | Minneapolis   | MN    | 55346 | 2017-01-10    | 2016-12-30 23:35:22 | 2016-12-30 23:35:22 |
|      9 | Macklin Johnson    | 9999999999 | cus9@email.com  | 123 ninth street   | St louis park | MN    | 55348 | 2017-01-10    | 2016-12-30 23:35:22 | 2016-12-30 23:35:22 |
|     10 | Tony Bui           | 1231231230 | cus10@email.com | 123 tenth street   | Chanhassen    | MN    | 55341 | 2017-01-10    | 2016-12-30 23:35:22 | 2016-12-30 23:35:22 |
+--------+--------------------+------------+-----------------+--------------------+---------------+-------+-------+---------------+---------------------+---------------------+
10 rows in set (0.00 sec)

mysql> select * from shipment;
+---------+--------+--------+------------+---------------------+---------------------+
| ship_id | inv_id | cus_id | ship_date  | creation_date       | last_modified_date  |
+---------+--------+--------+------------+---------------------+---------------------+
|      21 | 111111 |      1 | 2016-12-01 | 2016-11-30 23:35:22 | 2016-11-30 23:35:22 |
|      22 | 111112 |      2 | 2016-12-03 | 2016-11-30 23:35:22 | 2016-11-30 23:35:22 |
|      23 | 111113 |      3 | 2016-12-01 | 2016-11-30 23:35:22 | 2016-11-30 23:35:22 |
|      24 | 111114 |      4 | 2016-12-05 | 2016-11-30 23:35:22 | 2016-11-30 23:35:22 |
|      25 | 111115 |      5 | 2016-12-01 | 2016-11-30 23:35:22 | 2016-11-30 23:35:22 |
|      26 | 111116 |      6 | 2016-12-02 | 2016-11-30 23:35:22 | 2016-11-30 23:35:22 |
|      27 | 111117 |      7 | 2016-12-01 | 2016-11-30 23:35:22 | 2016-11-30 23:35:22 |
|      28 | 111118 |      8 | 2016-12-03 | 2016-11-30 23:35:22 | 2016-11-30 23:35:22 |
|      29 | 111119 |      9 | 2016-12-05 | 2016-11-30 23:35:22 | 2016-11-30 23:35:22 |
|      30 | 111120 |     10 | 2016-12-02 | 2016-11-30 23:35:22 | 2016-11-30 23:35:22 |
|      31 | 111121 |      6 | 2016-12-04 | 2016-11-30 23:35:22 | 2016-11-30 23:35:22 |
|      32 | 111122 |      3 | 2016-12-01 | 2016-11-30 23:35:22 | 2016-11-30 23:35:22 |
+---------+--------+--------+------------+---------------------+---------------------+
12 rows in set (0.00 sec)

mysql> SELECT cus_name, cus_phone, inv_id, inv_total, ship_date
    ->             from customer natural join shipment natural join invoiceheader
    ->             where inv_id =111111;
Empty set (0.00 sec)

如果有帮助，我附上了我的数据库架构。我尝试删除 run_sql 函数，但仍然没有成功。当我只做一个自然连接时，它似乎起作用了。

Answer 1

回应您的评论:

1. 通过隔离执行的查询，将数据库逻辑与应用程序逻辑分开:在您的应用程序中打印它或自己重新创建它。
2. 在 MySQL 控制台或通过 phpMyAdmin 或您为此使用的任何软件运行查询。

它返回什么？如果查询未返回任何内容，请确保应该连接表的字段(cus_id、inv_id)在整个表中相同他们出现在其中。即，inv_id 必须在 invoiceheader 和 shipment 中具有相同的 daratype。如果数据类型不相同，则它们也无法连接。

如果一切正常，请逐步重建您的查询。从一个简单的 SELECT ... FROM ... 开始，然后将 JOIN 附加到它，然后是另一个，同时确保问题不会发生那里。

最后，问题也可能出在您的 WHERE 子句中。确保此列的数据类型与您查询它的方式相匹配。 (注意:像这样构建查询很容易出错，最好使用 prepared statements 准备它们)

这有点笼统，但当我不知道您的数据库架构时很难具体说明。