hey guys,
I've been trying to figure a solution to this problem for quite some time now and just can't seem to figure it out.
I have a search form with a bunch of fields that are used to query a database.
the query is of the following format. It generates all the records in the database for the matching search criteria in a form with fields type of house, location, size:
$set = FALSE;
$query = "SELECT * FROM table_name";
if (!empty($type))
{
$query .= " WHERE type= '$type'";
$set = TRUE;
}
if (!empty($location))
{
$query .= ($set===TRUE ? " AND" : " WHERE") . " location= '$location";
$set = TRUE;
}
if (!empty($size))
{
$query .= ($set===TRUE ? " AND" : " WHERE") . " size = '$size'";
}
$results = mysql_query($query)
or die(mysql_error());
while ($row = mysql_fetch_array($results)) {
extract($row);
}
I have two other tables in the database. 1) users and 2) pricelink.
In the pricelink table there are three fields: userid, houseid, and price.
In another form I let users give a price that they would pay for the associated houseid.
I managed to get a table output for the search criteria above but I would like to join a field to the output table that would be the "average price" of all the suggested prices from the users (corresponding to the house ids of the search output records).
I tried a query based on results from the search of the user that looks like the following to get the right data I need:
$query2 = "SELECT houseid, AVG(price) FROM pricelink GROUP BY houseid";
but i need to limit the query to the searched house ids.
I think it isnt possible to add a WHERE houseid = "$houseid" clause in query2 but i need something of that nature.
I know this sounds confusing and I apologize in advance, let me know if clarifications are needed.
Thank you!