dispyaing results based on a customer search on webpage

MySQL database
#1

Hi

I have connected to mysql database and am displaying results but I want to display the results based on what the user searches for as its a property website, at the moment, the coding is returning all the results in the mysql database

Please help, been at it for days now trying to get it to work correctly

The coding is below

[php]

You have searched for a <?php echo $_POST["propertytype"]; ?>
You have searched in the location of <?php echo $_POST["location"]; ?>
You have searched for <?php echo $_POST["bedrooms"]; ?> bedrooms
You have searched for <?php echo $_POST["bathrooms"]; ?> bathrooms
You have searched for £<?php echo $_POST["minprice"]; ?>
You have searched for £<?php echo $_POST["maxprice"]; ?>

Please find your results below

<?php

$db=mysql_connect(“host”, “user”, “password”);
mysql_select_db(“database”, $db);

$db = mysql_select_db(“database”) or die (“Couldnt select database”);
?>

<?php // Query database $result = mysql_query("SELECT * FROM properties"); if (!$result) { echo "Error running query:
"; trigger_error(mysql_error()); } elseif(!mysql_num_rows($result)) { // no records found by query. echo "No records found"; } else { $i = 0; echo '
'; while($row = mysql_fetch_array($result)) { // Loop through results $i++; echo '
'; echo '
'; echo "Displaying record $i
\n
"; echo "" . $row['id'] . "
\n"; // Where 'id' is the column/field title in the database echo "Property Type: ". $row['typeProperty'] . "
\n"; // as above echo "Bedrooms: ". $row['bedrooms'] . "
\n"; // .. echo "Bathrooms: ". $row['bathrooms'] . "
\n"; // .. echo "Garden: ". $row['garden'] . "
\n"; // .. echo "Description: ". $row['description'] . "
\n"; // .. echo "Price: ". $row['price'] . "
\n"; // .. echo "Location: ". $row['location'] . "
\n"; // Where 'location' is the column/field title in the database echo '
'; echo '
'; } echo ''; } ?>[/php]
#2
hello ianhaney28,

replace below code (remove this code)

$result = mysql_query(“SELECT * FROM properties”);

with below code (use this code)

$selectsql = “SELECT * FROM properties where typeProperty=’”.$_POST[‘typeProperty’]."’ AND location=’".$_POST[‘location’]."’ AND bedrooms=’".$_POST[‘bedrooms’]."’ AND bathrooms=’".$_POST[‘bathrooms’]."’ AND (price BETWEEN “.$_POST[‘minprice’].” AND “.$_POST[‘maxprice’].”)";
$result = mysql_query($selectsql);

i hope this will helpful for you.
SR

#3

Hi Sarthak

Thank you so much for the reply, appreciate it so much

I replaced the coding with what you said and got this error showing

Error running query:

#4

hello ianhaney28,

can you show me the form where user fill the values for search record?
one more thing you can do it that echo $selectsql; query and execute directly in your database.
still you get error than post that echo $selectsql; query here.
also want to know you database table structure.

SR

#5

Hi Sarthak

Sorry for late reply

The coding for the form is below

[code]

Property Type

Location

Number of Bedrooms
1 2 3 4 5
Number of Bathrooms
1 2 3 4 5
Min Price
£0 £100,000 £125,000 £150,000 £175,000 £200,000 £225,000 £250,000 £275,000 £300,000 £325,000 £350,000 £375,000 £400,000 £425,000 £450,000 £475,000 £500,000 £550,000 £600,000 £650,000 £700,000 £800,000 £900,000 £1,000,000
Max Price
£100,000 £125,000 £150,000 £175,000 £200,000 £225,000 £250,000 £275,000 £300,000 £325,000 £350,000 £375,000 £400,000 £425,000 £450,000 £475,000 £500,000 £550,000 £600,000 £650,000 £700,000 £800,000 £900,000 £1,000,000 £1,250,000 £1,500,000 £2,000,000 £3,000,000

[/code]

The table structure I have is below

typeProperty
bedrooms
bathrooms
garden
description
price
location

Is that what you meant by the table structure

#6

hello ianhaney28,
do this for checking error
echo $selectsql = “SELECT * FROM properties where typeProperty=’”.$_POST[‘typeProperty’]."’ AND location=’".$_POST[‘location’]."’ AND bedrooms=’".$_POST[‘bedrooms’]."’ AND bathrooms=’".$_POST[‘bathrooms’]."’ AND (price BETWEEN “.$_POST[‘minprice’].” AND “.$_POST[‘maxprice’].”)";
and execute directly in your database and check what error your facing and do more thing if nay field is define as integer than remove single quote from around that field in query and reply me what happen.

SR

#7

Ok one sec and I will the script in the database directly, 2 mins and will get back to you with what it says, have you got a email address to contact you on, thinking it might be easier than on the forum

#8

Hi Sarthak

I got the following error when I ran that script in the database directly

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘echo $selectsql = "SELECT * FROM properties where typeProperty=’".$_POST[‘typePr’ at line 1

#9

Hi

I now have got this error

hate coding at times lol

the code I am using is below

[php]

You have searched for a <?php echo $_POST["propertytype"]; ?>
You have searched in the location of <?php echo $_POST["location"]; ?>
You have searched for <?php echo $_POST["bedrooms"]; ?> bedrooms
You have searched for <?php echo $_POST["bathrooms"]; ?> bathrooms
You have searched for £<?php echo $_POST["minprice"]; ?>
You have searched for £<?php echo $_POST["maxprice"]; ?>

Please find your results below

<?php

$db=mysql_connect(“host”, “user”, “password”);
mysql_select_db(“database”, $db);

$db = mysql_select_db(“database”) or die (“Couldnt select database”);

?>

<?php // Query database $bedrooms=mysql_real_escape_string($_POST['bedrooms']; $bathrooms=mysql_real_escape_string($_POST['bathrooms']; $minprice=mysql_real_escape_string($_POST['minprice']; $maxprice=mysql_real_escape_string($_POST['maxprice']; $query="SELECT * FROM properties WHERE bedrooms='$bedrooms' AND bathrooms='$bathrooms' AND price >= '$minprice' AND price <= '$maxprice' ORDER BY price DESC"; //$result = mysql_query($selectsql); $result = mysql_query($query); ?> <?php /*$selectsql = "SELECT * FROM properties where typeProperty='".$_POST['typeProperty']."' AND location='".$_POST['location']."' AND bedrooms='".$_POST['bedrooms']."' AND bathrooms='".$_POST['bathrooms']."' AND (price BETWEEN ".$_POST['minprice']." AND ".$_POST['maxprice'].")";*/ //$result = mysql_query($selectsql); //$result = mysql_query($query); /* if (!$result) { echo "Error running query:
"; trigger_error(mysql_error()); } elseif(!mysql_num_rows($result)) { // no records found by query. echo "No records found"; } else {*/ /* $i = 0; echo '
'; while($row = mysql_fetch_array($result)) { // Loop through results $i++; echo '
'; echo '
'; echo "Displaying record $i
\n
"; echo "" . $row['id'] . "
\n"; // Where 'id' is the column/field title in the database echo "Property Type: ". $row['typeProperty'] . "
\n"; // as above echo "Bedrooms: ". $row['bedrooms'] . "
\n"; // .. echo "Bathrooms: ". $row['bathrooms'] . "
\n"; // .. echo "Garden: ". $row['garden'] . "
\n"; // .. echo "Description: ". $row['description'] . "
\n"; // .. echo "Price: ". $row['price'] . "
\n"; // .. echo "Location: ". $row['location'] . "
\n"; // Where 'location' is the column/field title in the database echo '
'; echo '
'; } echo ''; } */ ?>[/php]
#10

Hi I just thought, I could do a xml file using excel and connect to that and hopefully get data from the xml file dependent on the user search

I created a xml file using excel and think I have connected to it but is not displaying any results

have I done the coding right

[php]

You have searched for a <?php echo $_POST['propertytype']; ?>
You have searched in the location of <?php echo $_POST['location']; ?>
You have searched for <?php echo $_POST['bedrooms']; ?> bedrooms
You have searched for <?php echo $_POST['bathrooms']; ?> bathrooms
You have searched for £<?php echo $_POST['minprice']; ?>
You have searched for £<?php echo $_POST['maxprice']; ?>

Please find your results below 

    <?php

$xml = simplexml_load_file(‘properties.xml’);
foreach($xml->PROPERTY as $row) {
if(strval($row->$REF) == $_POST[‘form1’]) {
header(“Location: {$row->URL}”);
die(“Header redirect.”);
}
}
// If we’re still here, then display search results below. I leave this task up to you.
?>

<?php $query = 'PROPERTIES'; if(isset($_POST['ID']) && strlen($_POST['ID']) > 0) $query .= '[ID="'.$_POST['ID'].'"]'; if(isset($_POST['PROPERTYTYPE']) && strlen($_POST['PROPERTYTYPE']) > 0) $query .= '[PROPERTYTYPE="'.$_POST['PROPERTYTYPE'].'"]'; if(isset($_POST['LOCATION']) && strlen($_POST['LOCATION']) > 0) $query .= '[LOCATION="'.$_POST['LOCATION'].'"]'; if(isset($_POST['BEDROOMS']) && strlen($_POST['BEDROOMS']) > 0) $query .= '[BEDROOMS="'.$_POST['BEDROOMS'].'"]'; if(isset($_POST['BATHROOMS']) && strlen($_POST['BATHROOMS']) > 0) $query .= '[BATHROOMS="'.$_POST['BATHROOMS'].'"]'; if(isset($_POST['DESCRIPTION']) && strlen($_POST['DESCRIPTION']) > 0) $query .= '[DESCRIPTION="'.$_POST['DESCRIPTION'].'"]'; if(isset($_POST['GARDEN']) && strlen($_POST['GARDEN']) > 0) $query .= '[GARDEN="'.$_POST['GARDEN'].'"]'; if(isset($_POST['PRICE']) && strlen($_POST['PRICE']) > 0) $query .= '[PRICE="'.$_POST['PRICE'].'"]'; $results = $xml->xpath($query); echo '

Results ('.((is_array($results) && count($results) > 0) ? count($results) : 0).')

'; if(is_array($results) && count($results) > 0) { //This is the number of results to display per page $limit=4; //Here we figure out how many pages there are going to be //Size is the size of the array $size=count($results); //Pages is calculated by dividing size by the limit per page //We use ceil in case it does not divide evenly $pages=ceil($size/$limit); $search=""; if(isset($_POST['PROPERTYTYPE']) && strlen($_POST['PROPERTYTYPE']) > 0) $search .= "&PROPERTYTYPE=" . $_POST['PROPERTYTYPE']; if(isset($_POST['LOCATION']) && strlen($_POST['LOCATION']) > 0) $search .= "&LOCATION=" . $_POST['LOCATION']; if(isset($_POST['BEDROOMS']) && strlen($_POST['BEDROOMS']) > 0) $search .= "&BEDROOMS=" . $_POST['BEDROOMS']; if(isset($_POST['BEDROOMS']) && strlen($_POST['BEDROOMS']) > 0) $search .= "&BEDROOMS=" . $_POST['BEDROOMS']; if(isset($_POST['BATHROOMS']) && strlen($_POST['BATHROOMS']) > 0) $search .= "&BATHROOMS=" . $_POST['BATHROOMS']; if(isset($_POST['DESCRIPTION']) && strlen($_POST['DESCRIPTION']) > 0) $search .= "&DESCRIPTION=" . $_POST['DESCRIPTION']; if(isset($_POST['GARDEN']) && strlen($_POST['GARDEN']) > 0) $search .= "&GARDEN=" . $_POST['GARDEN']; if(isset($_POST['PRICE']) && strlen($_POST['PRICE']) > 0) $search .= "&PRICE=" . $_POST['PRICE']; if(isset($_POST['page'])) $i=$_POST['page']; else $i=1; if(isset($_POST['page'])) $page=$_POST['page']; else $page=1; $start=($page*$limit)-$limit; $display=array(); while($limit!=0) { if($start<$size) $display[]=$results[$start]; $start++; $limit--; } foreach($display as $property) { echo '
' . $property->PRICE .'
'. $property->BEDROOMS.$property->PLUS .' ' . $property->STUDIO .' ' .$property->TYPE.' in '.$property->AREA .'
Property

'. $property->DATAPROPERTY . '
Features

'. $property->DATAFEATURE . '

' ; } echo "
"; if($i!=1) echo "Previous Page "; echo "Page " . $i . " of " . $pages . " "; if($i!=$pages) echo "Next Page "; echo "
"; } else echo '

No Results

'; ?> <?php /* $db=mysql_connect("host", "user", "password"); mysql_select_db("database", $db); $db = mysql_select_db("database") or die ("Couldnt select database"); ?> <?php // Query database $bedrooms=mysql_real_escape_string($_POST['bedrooms']; $bathrooms=mysql_real_escape_string($_POST['bathrooms']; $minprice=mysql_real_escape_string($_POST['minprice']; $maxprice=mysql_real_escape_string($_POST['maxprice']; $query="SELECT * FROM properties WHERE bedrooms='$bedrooms' AND bathrooms='$bathrooms' AND price >= '$minprice' AND price <= '$maxprice' ORDER BY price DESC"; $result = mysql_query($query); */ ?>[/php]
#11

Sarthak must be having dinner… I will jump in, but, leaving shortly…

3 posts ago, you posted errors that were caused by a missing bracket ] , on a $_POST[‘variable’]

2 posts ago, you posted code with a ton of it commented out near the bottom. Not sure what that was all about.

This post, you talk about switching to XML and changing all the code Sarthak has helped you with???

So, Perhaps dinner and a movie would calm you down a bit and then it will all fall into place… LOL…

Anyway, I see one big problem… In your form code you use lower-case letters for “bedrooms”, “bathrooms”, etc. And, in your code you use CAPS. Capitals make a difference. If you use them for one, use them for the other! Sarthak has been helpful to you. I will be back in 5 or 6 hours and should be able to help more if Sarthak isn’t back yet. Please post your code again with details on where it is failing and I will look at it when I get back. Good luck!

Sponsor our Newsletter | Privacy Policy | Terms of Service