I have two tables (customers and real estates). In the customers table I have the personal data of the customer and the interests he has.
Table "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 |
+----+-------+-----------------+---------+------+--------+-----------+-----------+--------+
In the table real estates I have the each data of registered home.
Table "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 |
+----+------+---------+---------+------+--------+-------------+------------+--------+
I need to from somehow compare the interests of each customer with each home and show the number of homes compatible with each customer, the result would be something like this:
Client1 || cliente1@email.com || 4 properties compatible
Client2 || cliente2@email.com || 7 properties compatible
However already tried various forms, i already broke the head, i already got similar results but something's always wrong.
In this code below it correctly counts how many homes are compatible with each client, but it also displays customers with empty interests and I need to show only customers who have filled the interests and show the compatible homes with them. This code works however it displays all the Customers, even if they are with empty interests.
My current code:
<?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!";
}
}
?>
I believe that my code could be totally wrong from the following query3 variable.
