How to create search filters?

[usman07]

I get this error?

 

Fatal error: Call to a member function rowCount() on a non-object in /home/a2221438/public_html/forsale.php on line 102

 

This is line 102:

$result = $dbh->query($sql);

 

and below this is the row count:

$resultcount = $result->rowCount();
//check count for no results
if ($resultcount < 1){

[Drummin]

Double check each of these fields against your table field names.  The query is failing because something doesn't match.

p.id, p.location_id, p.catagory_id, p.type, p.bedrooms, p.bathrooms, p.receptions, p.parking, p.garden, p.market_type, p.asking_price, p.pay_interval, p.url, p.summary, p.full_description, c.image_path, c.area_name

I suspect it's `area_name` from `categorys` but that's just a guess.

[usman07]

the area_name is not in the catagorys table but in the locations table. I actually don't want any of these entries in anyways, from the database I just want images to appear depended on if the property is detached, semi detached, etc.

[Drummin]

... OK, you said the `image_path` is in categorys table.  Now if you don't need the `area_name`, then just remove that one field from the query.

 

$sql= "SELECT p.id, p.location_id, p.catagory_id, p.type, p.bedrooms, p.bathrooms, p.receptions, p.parking, p.garden, p.market_type, p.asking_price, p.pay_interval, p.url, p.summary, p.full_description, c.image_path FROM property as p ";
$sql .= "LEFT JOIN catagorys AS c ";
$sql .= "ON ";
$sql .= "(c.property_id = p.id) ";
$sql .= "WHERE p.market_type='sale'";

[usman07]

Hello Drummin, Basically what I want is for the search filter to check information from the properties table for and check the ID's to see what type of property it is e.g. detached, semi detached etc, then output an image from that? I don't need any other fields, like the bedroom bathrooms etc and all of them.

[Drummin]

Hmm, well then remove fields you don't need.  I'll leave the id and image_path in this example.

$sql= "SELECT p.id, c.image_path FROM property as p ";
$sql .= "LEFT JOIN catagorys AS c ";
$sql .= "ON ";
$sql .= "(c.property_id = p.id) ";
$sql .= "WHERE p.market_type='sale'";

[usman07]

I'm still getting the same error?

 

Fatal error: Call to a member function rowCount() on a non-object in /home/a2221438/public_html/forsale.php on line 101

[Drummin]

Can you post current page code?  I see a mixture of old and new on your forsale.php page.

[usman07]

Yeah mate, sorry here is the php code:

<?php
$host = ""; 
//MySQL Database user name.
$login = "";
//Password for MySQL.
$dbpass = "";
//MySQL Database name.
$db = "";

//Make connection to DB
try {
$dbh = new PDO("mysql:host=mysql10.000webhost.com;dbname=$db", $login, $dbpass);
} catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "<br/>";
die();
}

//Make query.  Adjust table/field names as needed.
//Basic query for properies for sale	
$sql= "SELECT p.id, c.image_path FROM property as p ";
$sql .= "LEFT JOIN catagorys AS c ";
$sql .= "ON ";
$sql .= "(c.property_id = p.id) ";
$sql .= "WHERE p.market_type='sale'";	
//Make array of filter allowed types
$types=array("detached","semi-detached","terraced","flats");

//Check for GET and if allowed type  
if(isset($_GET['housetype']) && in_array($_GET['housetype'], $types)){
//Add filter type to our query
$sql .="p.housetype='{$_GET['housetype']}'";
}
//Add order by
$sql .=" ORDER BY p.id";
//execute query
$result = $dbh->query($sql);
//get result count
$resultcount = $result->rowCount();
//check count for no results
if ($resultcount < 1){
echo '<div class="error">Sorry, No Results Match Your Search.</div>';
}
while($row = $result->fetch(PDO::FETCH_BOTH)){
echo '<div class="container" style="float:left;">';
echo '<div class="imageholder" style="float:left;">';
echo "<a href='{$row['url']}'><img class='image1' src='{$row['image_path']}' alt='{$row['summary']}' /></a> <br />";
echo '</div>';
echo '<div class="textholder" style="font-family:helvetica; font-size:14px; float:left; padding-top:10px;">';
echo "{$row['summary']}";
echo "<span style=\"color:#63be21;\"><br><br><b>{$row['bedrooms']} bedroom(s) {$row['bathrooms']} bathroom(s) {$row['receptions']} reception room(s)</b></span>";
if($row['parking'] != null){
echo "<span style=\"color:#63be21;\"><b> {$row['parking']} parking space(s)</b></span>";
echo '<div class="sline"><img src="cutouts/search/sline.png" alt=""/></div>';
}

echo '</div>';
echo '<div style="clear:both"></div>';
}
?>

[Drummin]

I see you've modified the field `type`, changing it to `housetype`.  Is that reflected in your DB table as well?

 

I've seen many changes to your DB tables through this process.  You should always consider how these changes will affect "work" or coding already done, for example your insert.php page.  Make sure you stay consistent with table field names and page coding so if you make a change, update all pages/DB table that use this field.

[usman07]

Yeah it is mate.

[Drummin]

Well the only fields we're using are

TABLE: property

Fields:

id

market_type

housetype

 

TABLE: catagorys

Fields:

property_id

image_path

 

Do these all look correct?

[usman07]

It should be:

 

TABLE: property

Fields:

id

market_type

housetype

 

TABLE: images

Fields:

property_id

images2

 

 

I know iv changed image_path to images2

[Drummin]

Based on what you've said I believe this is correct.

<?php
$host = ""; 
//MySQL Database user name.
$login = "";
//Password for MySQL.
$dbpass = "";
//MySQL Database name.
$db = "";

//Make connection to DB
try {
$dbh = new PDO("mysql:host=mysql10.000webhost.com;dbname=$db", $login, $dbpass);
} catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "<br/>";
die();
}

//Make query.  Adjust table/field names as needed.
//Basic query for properies for sale	
$sql= "SELECT p.id, i.images2 FROM property as p ";
$sql .= "LEFT JOIN images AS i ";
$sql .= "ON ";
$sql .= "(i.property_id = p.id) ";
$sql .= "WHERE p.market_type='sale' ";	
//Make array of filter allowed types
$types=array("detached","semi-detached","terraced","flats");

//Check for GET and if allowed type  
if(isset($_GET['housetype']) && in_array($_GET['housetype'], $types)){
//Add filter type to our query
$sql .="AND p.housetype='{$_GET['housetype']}'";
}
//Add order by
$sql .=" ORDER BY p.id";
//execute query
$result = $dbh->query($sql);
//get result count
$resultcount = $result->rowCount();
//check count for no results
if ($resultcount < 1){
echo '<div class="error">Sorry, No Results Match Your Search.</div>';
}
while($row = $result->fetch(PDO::FETCH_BOTH)){
echo '<div class="container" style="float:left;">';
echo '<div class="imageholder" style="float:left;">';
echo "<img class='image1' src='{$row['images2']}' alt='' /><br />";
echo '</div>';
echo '</div>';
echo '<div style="clear:both"></div>';
}
?>

 

MAN I see you're now using a different table name.  YOU'VE got to stop doing that!!!  Or at least make sure you adjust code accordingly.

[usman07]

Im sorry mate, iv not explained it to you properly i know. Instead of 'categories' shall i put 'images'

[Drummin]

I believe I did these changes in the updated post above where it says:

$sql= "SELECT p.id, i.images2 FROM property as p ";
$sql .= "LEFT JOIN images AS i ";

[usman07]

Ok mate, done that, now the images come up, but how do I get it to work when selecting the links which are filters, at the moment when I select the link e.g. detached houses, the page doesn't exist. Check the link here: http://www.mumtazproperties.hostei.com/forsale.php#

 

Its the bottom 3 images, the top 3 are coded using HTML, which Im going to remove

 

Thanks

[Drummin]

Your links still say "type" but you updated your code to use $_GET['housetype'].  Make sure any changes you make are updated any place it is used.  Also make sure you're using the php extension on those links not htm

 

Are you really going to be making images for all properties instead of using your DB?  I know this has been frustrating but querying your different fields to describe the property is a better option.

[usman07]

It works mate, thats brilliant! thanks so much. 1 last thing, I want the images to be links just like they was in my other page Iv added it in the echo, is it somehow wrong? Thanks.

echo "<a href='{$row['url']}'><img class='image1' src='{$row['images2']}' alt='' /></a><br />";

[usman07]

I added it in the SELECT and it works:

$sql= "SELECT p.id, p.url, i.images2 FROM property as p ";

[Drummin]

By Golly, I think you're starting to get this!  Best of luck finishing your project.

[usman07]

Thanks so much mate! One last thing, but its just about styling? If you check the link: http://mumtazproperties.hostei.com/forsale.php

 

When you access the page, you see the filter being below, but I want it inline with the first image, then when you select 'detached houses' and the error message comes up, then the filter is where its meant to be?

[Drummin]

Try adding valign to both your links and content tags.  This should bring both to the top of the cell.

<td valign="top">

Also, that "All" link should be the full url not just # sign.

<a href="forsale.php"><b><u>All</u></b></a>

Be sure to update all those top links to php extension.