两个mysql表的比较

Question

我有两张桌子(顾客和房地产)。在customers表中，我有客户的个人数据和他的兴趣。

表"dbc_customers"：

+----+-------+-----------------+---------+------+--------+-----------+-----------+--------+
| id | name  |      email      | bedroom | bath | garage | min_price | max_price | status |
+----+-------+-----------------+---------+------+--------+-----------+-----------+--------+
|  1 | Maria | maria@email.com |       4 |    2 |      0 | 0.00      | 0.00      |      1 |
|  2 | John  | john@email.com  |       4 |    0 |      0 | 0.00      | 0.00      |      1 |
|  3 | Julia | julia@email.com |       0 |    0 |      0 | 0.00      | 0.00      |      1 |
|  4 | Ana   | ana@email.com   |       0 |    0 |      0 | 0.00      | 0.00      |      0 |
+----+-------+-----------------+---------+------+--------+-----------+-----------+--------+

在房地产表中，我有注册房屋的各项数据。

表"dbc_posts"：

+----+------+---------+---------+------+--------+-------------+------------+--------+
| id | city | address | bedroom | bath | garage | total_price | year_built | status |
+----+------+---------+---------+------+--------+-------------+------------+--------+
|  1 |    3 | st 21   |       4 |    2 |      1 | 200.00      |       2010 |      1 |
|  2 |    3 | st 22   |       4 |    3 |      4 | 10.00       |       2000 |      1 |
|  3 |    3 | b 12    |       2 |    1 |      5 | 40.00       |       2014 |      1 |
|  4 |    2 | b 14    |       3 |    2 |      2 | 30.00       |       2013 |      1 |
+----+------+---------+---------+------+--------+-------------+------------+--------+

我需要以某种方式比较每个客户与每个家庭的兴趣，并显示与每个客户兼容的家庭数量，结果将如下所示：

Client1 || cliente1@email.com || 4 properties compatible
Client2 || cliente2@email.com || 7 properties compatible

然而，我已经尝试了各种形式，我已经打破了头，我已经得到了类似的结果，但总是有问题。

在下面的代码中，它正确地计算了与每个客户端兼容的房屋数量，但它还显示了具有空兴趣的客户，我只需要显示满足兴趣的客户，并显示与他们兼容的房屋。这段代码的工作方式是显示所有客户，即使他们没有兴趣。

我当前的代码：

<?php
#Select all active customers and order by id desc
$query = mysql_query("SELECT * FROM dbc_customers WHERE status='1' ORDER BY id DESC") or die(mysql_error());
#No customers found
if (mysql_num_rows($query) < 1){
    echo "No customers found!";
}
else {
    #Set vars
    $where="";
    $i=1;
    while ($row = mysql_fetch_object($query)) {
        #Define "where" clause according to values of the table column
        if (!empty($row->bedroom)) $where .= "bedroom='$row->bedroom' AND ";
        if (!empty($row->bath)) $where .= "bath='$row->bath' AND ";
        //if (!empty($row->garage)) $where .= "c.garage = p.garage AND ";
        #Count all posts compatibles with each customer
        $query2 = mysql_query("SELECT id FROM dbc_posts WHERE $where status='1'") or die(mysql_error());
        #If none posts found break the loop, exit and show a message error, else show number of posts found
        if (mysql_num_rows($query2) < 1){ break; exit; } else { $result = mysql_num_rows($query2); }
        #Select only one post compatible for each customer
        $query3 = mysql_query("SELECT DISTINCT id FROM dbc_posts WHERE $where status='1' LIMIT 1") or die(mysql_error());
        #Flag for where var
        if ($query2 and $query3) $where = "";
        #Loop for each result of query3 and show customers and yours compatibles posts
        while ($row3 = mysql_fetch_object($query3)) {
            #Show customers
            echo "<b>".$row->name."</b> || ".$row->email." || <a href='#'><b>".mysql_num_rows($query2)." properties compatible</b></a><br />";
        }
    }
    #If none compatibles posts with customers was found
    if ($result < 1){
        echo "No listings were found compatible with any client!";
    }
}
?>

我相信我的代码可能是从下面的query3变量完全错误的。

Answer 1

我根本不明白你为什么需要query3。此外，如果您在找到没有兼容机的客户后中断并退出，则不会看到任何其他可能仍有兼容机的客户，也不会看到任何错误消息。您需要使用'continue‘而不是’continue‘转到下一个客户。

试试这个：

#Select all active customers and order by id desc
$query = mysql_query("SELECT * FROM dbc_customers WHERE status='1' ORDER BY id DESC") or die(mysql_error());
#No customers found
if (mysql_num_rows($query) < 1) echo "No customers found!";
else {
    #Set vars
    $where = "";
    $total = 0;
    while ($row = mysql_fetch_object($query)) {
        #Define "where" clause according to values of the table column
        if ($row->bedroom > 0) $where .= "bedroom='$row->bedroom' AND ";
        if ($row->bath > 0) $where .= "bath='$row->bath' AND ";
        #Count all posts compatibles with each customer
        $query2 = mysql_query("SELECT id FROM dbc_posts WHERE $where status='1'") or die(mysql_error());
        $count = mysql_num_rows($query2);
        #If none posts found continue, else update total number of compatibles
        if(!$count) continue;
        else $total += $count;
        #Show customers
        echo "<b>".$row->name."</b> || ".$row->email." || <a href='#'><b>".$count." properties compatible</b></a><br />";
    }
    #If none compatibles posts with customers was found
    if ($total < 1) echo "No listings were found compatible with any client!";
}

顺便问一下，为什么兼容机需要'a‘标签？