Joined query with averages

[colby07]

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!

 

[Ken2k7]

Try changing:

$query  = "SELECT * FROM table_name";]

 

to

$query  = "SELECT AVG(`price`), * FROM table_name";