Split SQL query result into different categories

hi all,

I really need some help on this one :

I have a table containing info for rental properties, each record has a field called TYPE, which is the property type ( house, flat, townhouse, ect )
What I would like to do is run a query based on a user search and ‘split’ the results in hyperlinks with counts for each.

ie : Houses (5) Flats (10) Townhouses (4)

so when the user searches, the hyperlinks is displayed like above from the original SQL query, so when the user then clicks on a hyperlink
it will display the results for ‘houses’ or ‘flats’ alone.

Something like


SELECT propertyType
              , COUNT(*)
    FROM rentalProperty
  GROUP BY propertyType

hi Dave,

thanks for the quick reply, I am sure you know I am very new to php/sql by the question(s) I ask, so here is another one!

If the above code gives my the count for each property type, how would I create the hyperlinks for the user to click on and will it be based
on the first search results the user used?

ie : user searches for properties in A CITY or A SUBURB > result is displays and the hyperlinks to each property type with counts, if the user
clicks on a link for a specific property type, it will display the results based on the first search ( CITY / SUBURB ) or will I have to create a new query?

You’ll have to build the queries yourself based on the parameters from the original query, and include those along with the property type specified. What I would do is just run two queries, and when you run through the property types, include that in the query parameters…

very high level (my php is very rusty, so apologies before hand)


<php?

// Build Parameter List....
$get_parameters = array();
$whereClause = '1=1';        // 1=1 there to prevent having to check for parameters when building where clause
$typeFound = false;

if (isset($_SERVER['QUERY_STRING'])) {    
   $pairs = explode('&', $_SERVER['QUERY_STRING']);    
   foreach($pairs as $pair) {        
        $whereClause += " AND " + $part[0] + " = :" + $part[0];        
        $part = explode('=', $pair);        
        $get_parameters[$part[0]] = urldecode($part[1]);    
  }
}
// get Property Type
 counts$stmt = $pdo->prepare('SELECT propertyType, COUNT(*) FROM rentalProperty WHERE ' + $whereClause + " GROUP BY propertyType");
$stmt->execute($get_parameters));
foreach ($stmt as $row) {
       // handle counts & build link (include params from above in link)
}

// get Full Recordset
$stmt = $pdo->prepare('SELECT propertyName, address, propertyType FROM rentalProperty WHERE ' + $whereClause);
$stmt->execute($get_parameters));
foreach ($stmt as $row) {
    // handle full list
}
?php>

hi dave,

thank you, I will have to take some time to go through your code ( a bit above my knowledge base, Im only starting to learn PHP & MySQL ) :slight_smile:

I tried the code, but with errors, is there not a more ‘beginner friendly’ way of doing this? All I need is to count the property types
and then create a link on each type based on the original SQL query

What errors are you getting (SQL or PHP)?