...

View Full Version : displaying data from mysql database to a webpage



ianhaney
04-16-2012, 04:36 PM
I have got a connection now to the mysql database, how do I get the data from the database to display on the webpage

Thank you

Ian

mlseim
04-16-2012, 05:09 PM
Show us what you have so far, in regards to the PHP script.

And give us the name of the MySQL table and some column names while you're at it.


.

ianhaney
04-16-2012, 05:36 PM
Hi mlseim

I have managed to display results now to the webpage but I need it to be like the following


I want the results to be displayed by what the customer searches as its a property website

so for example, in my search form I have the following

Property Type
Location
Number of bedrooms
Number of Bathrooms
Min Price
Max Price

for example, I put in the following

House
Benfleet
4
2
100,000
300,000

but when I click on search, it returns all the results that are in mysql database and not just what I am looking for

Please help

I have included the coding below


<html>
<body>
You have searched for a&nbsp;<?php echo $_POST["propertytype"]; ?>
<br>
You have searched in the location of <?php echo $_POST["location"]; ?>
<br />
You have searched for <?php echo $_POST["bedrooms"]; ?>&nbsp;bedrooms
<br />
You have searched for <?php echo $_POST["bathrooms"]; ?>&nbsp;bathrooms
<br />
You have searched for <?php echo $_POST["minprice"]; ?>
<br />
You have searched for <?php echo $_POST["maxprice"]; ?>
<br><br>
Please find your results below
</body>
</html>
<br><br>
<?php

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

?>

<?php

$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:<br>";
trigger_error(mysql_error());
}
elseif(!mysql_num_rows($result))
{
// no records found by query.
echo "No records found";
}
else
{
$i = 0;
echo '<div class="container" style="float:left;">';
while($row = mysql_fetch_array($result)) { // Loop through results
$i++;
echo '</div>';
echo '<div class="textholder" style="font-family:helvetica; font-size:13px; float:left; padding-top:10px;">';
echo "<span style=\"color:green;\"><b>Displaying record $i<br>\n</b><br></span>";
echo "<b>" . $row['id'] . "</b><br>\n"; // Where 'id' is the column/field title in the database
echo "Property Type: ". $row['typeProperty'] . "<br>\n"; // as above
echo "Bedrooms: ". $row['bedrooms'] . "<br>\n"; // ..
echo "Bathrooms: ". $row['bathrooms'] . "<br>\n"; // ..
echo "Garden: ". $row['garden'] . "<br>\n"; // ..
echo "Description: ". $row['description'] . "<br>\n"; // ..
echo "Price: ". $row['price'] . "<br>\n"; // ..
echo "Location: ". $row['location'] . "<br>\n"; // Where 'location' is the column/field title in the database
echo '</div>';
echo '<div style="clear:both"></div>';
}
echo '</div>'; }

?>

Kind regards

Ian

ianhaney
04-17-2012, 11:32 AM
any ideas mlseim

I am well and truly stuck at the moment

Ian

mlseim
04-17-2012, 12:28 PM
First, let's see the actual search form.

It's important to know what they are allowed to search for,
and pricing searches you allow. Are you searching for prices
within a range (like you have min/max), and locations exact,
or within a range (distance)? It could get really complicated.

ianhaney
04-17-2012, 12:49 PM
Hi Mlseim

Thank you for the reply

The user would be searching for type of property, location, number of bedrooms, number of bathrooms, min price and a max price

I have included the search form coding below


<form action="/saleresults.php" method="post" name="form1" id="form1" onsubmit="MM_validateForm('maxprice','','NisNum','minprice','','NisNum','section2');return document.MM_returnValue">
<div id="fieldnames">Property Type</div><input type="text" name="propertytype" size="25" maxlength="40"/>
<br>
<div id="fieldnames">Location</div><input type="text" name="location" size="25" maxlength="25"/>
<br>
<div id="fieldnames">Number of Bedrooms</div>
<select name="bedrooms" id="bedrooms">
<option value="1" selected="selected" name="bedrooms">1</option>
<option value="2" selected="selected" name="bedrooms">2</option>
<option value="3" selected="selected" name="bedrooms">3</option>
<option value="4" selected="selected" name="bedrooms">4</option>
<option value="5" selected="selected" name="bedrooms">5</option>
</select>
<br>
<div id="fieldnames">Number of Bathrooms</div>
<select name="bathrooms" id="bathrooms">
<option value="1" selected="selected" name="bathrooms">1</option>
<option value="2" selected="selected" name="bathrooms">2</option>
<option value="3" selected="selected" name="bathrooms">3</option>
<option value="4" selected="selected" name="bathrooms">4</option>
<option value="5" selected="selected" name="bathrooms">5</option>
</select>
<div id="fieldnames">Min Price</div>
<select name="minprice" id="minprice">
<option value="0" selected="selected" name="minprice">&pound;0</option>
<option value="100,000" name="minprice">&pound;100,000</option>
<option value="125,000" name="minprice">&pound;125,000</option>
<option value="150,000" name="minprice">&pound;150,000</option>
<option value="175,000" name="minprice">&pound;175,000</option>
<option value="200,000" name="minprice">&pound;200,000</option>
<option value="225,000" name="minprice">&pound;225,000</option>
<option value="250,000" name="minprice">&pound;250,000</option>
<option value="275,000" name="minprice">&pound;275,000</option>
<option value="300,000" name="minprice">&pound;300,000</option>
<option value="325,000" name="minprice">&pound;325,000</option>
<option value="350,000" name="minprice">&pound;350,000</option>
<option value="375,000" name="minprice">&pound;375,000</option>
<option value="400,000" name="minprice">&pound;400,000</option>
<option value="425,000" name="minprice">&pound;425,000</option>
<option value="450,000" name="minprice">&pound;450,000</option>
<option value="475,000" name="minprice">&pound;475,000</option>
<option value="500,000" name="minprice">&pound;500,000</option>
<option value="550,000" name="minprice">&pound;550,000</option>
<option value="600,000" name="minprice">&pound;600,000</option>
<option value="650,000" name="minprice">&pound;650,000</option>
<option value="700,000" name="minprice">&pound;700,000</option>
<option value="800,000" name="minprice">&pound;800,000</option>
<option value="900,000" name="minprice">&pound;900,000</option>
<option value="100,000,0" name="minprice">&pound;1,000,000</option>
</select>

<div id="fieldnames">Max Price</div>
<select name="maxprice" id="maxprice">
<option value="100,000" name="maxprice">&pound;100,000</option>
<option value="125,000" name="maxprice">&pound;125,000</option>
<option value="150,000" name="maxprice">&pound;150,000</option>
<option value="175,000" name="maxprice">&pound;175,000</option>
<option value="200,000" name="maxprice">&pound;200,000</option>
<option value="225,000" name="maxprice">&pound;225,000</option>
<option value="250,000" name="maxprice">&pound;250,000</option>
<option value="275,000" name="maxprice">&pound;275,000</option>
<option value="300,000" name="maxprice">&pound;300,000</option>
<option value="325,000" name="maxprice">&pound;325,000</option>
<option value="350,000" name="maxprice">&pound;350,000</option>
<option value="375,000" name="maxprice">&pound;375,000</option>
<option value="400,000" name="maxprice">&pound;400,000</option>
<option value="425,000" name="maxprice">&pound;425,000</option>
<option value="450,000" name="maxprice">&pound;450,000</option>
<option value="475,000" name="maxprice">&pound;475,000</option>
<option value="500,000" name="maxprice">&pound;500,000</option>
<option value="550,000" name="maxprice">&pound;550,000</option>
<option value="600,000" name="maxprice">&pound;600,000</option>
<option value="650,000" name="maxprice">&pound;650,000</option>
<option value="700,000" name="maxprice">&pound;700,000</option>
<option value="800,000" name="maxprice">&pound;800,000</option>
<option value="900,000" name="maxprice">&pound;900,000</option>
<option value="100,000,0" name="maxprice">&pound;1,000,000</option>
<option value="125,000,0" name="maxprice">&pound;1,250,000</option>
<option value="150,000,0" name="maxprice">&pound;1,500,000</option>
<option value="200,000,0" name="maxprice">&pound;2,000,000</option>
<option value="300,000,0" name="maxprice">&pound;3,000,000</option>
</select>
<br><br>
<input type="submit" value="Submit" />
</form>

mlseim
04-17-2012, 01:58 PM
For all values that get sent to the script ...

value="100,000"

Remove the commas .... you can keep the commas on the values that people can see.

<option value="100000" name="minprice">&pound;100,000</option>

and remove the names from <options> ... only <select> has a name.

<option value="100000">&pound;100,000</option>

===============

When you process the form using PHP, you'll be doing a query to match the
form variables to various columns in the MySQL table.

The query will look sort of like this ...



<?php

$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";

?>

ianhaney
04-17-2012, 03:06 PM
Hi mlseim

I have removed the commas from the values and the names as well

So now I need to put in below into the saleresults.php page, is that right

<?php

$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";

?>

ianhaney
04-17-2012, 03:13 PM
I have gone wrong somwhere as now am getting server error page

The code I have got is below


<html>
<body>
You have searched for a&nbsp;<?php echo $_POST["propertytype"]; ?>
<br>
You have searched in the location of <?php echo $_POST["location"]; ?>
<br />
You have searched for <?php echo $_POST["bedrooms"]; ?>&nbsp;bedrooms
<br />
You have searched for <?php echo $_POST["bathrooms"]; ?>&nbsp;bathrooms
<br />
You have searched for <?php echo $_POST["minprice"]; ?>
<br />
You have searched for <?php echo $_POST["maxprice"]; ?>
<br><br>
Please find your results below
</body>
</html>
<br><br>
<?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";

/*$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);

if (!$result)
{
echo "Error running query:<br>";
trigger_error(mysql_error());
}
elseif(!mysql_num_rows($result))
{
// no records found by query.
echo "No records found";
}
else
{*/
$i = 0;
echo '<div class="container" style="float:left;">';
while($row = mysql_fetch_array($result)) { // Loop through results
$i++;
echo '</div>';
echo '<div class="textholder" style="font-family:helvetica; font-size:13px; float:left; padding-top:10px;">';
echo "<span style=\"color:green;\"><b>Displaying record $i<br>\n</b><br></span>";
echo "<b>" . $row['id'] . "</b><br>\n"; // Where 'id' is the column/field title in the database
echo "Property Type: ". $row['typeProperty'] . "<br>\n"; // as above
echo "Bedrooms: ". $row['bedrooms'] . "<br>\n"; // ..
echo "Bathrooms: ". $row['bathrooms'] . "<br>\n"; // ..
echo "Garden: ". $row['garden'] . "<br>\n"; // ..
echo "Description: ". $row['description'] . "<br>\n"; // ..
echo "Price: ". $row['price'] . "<br>\n"; // ..
echo "Location: ". $row['location'] . "<br>\n"; // Where 'location' is the column/field title in the database
echo '</div>';
echo '<div style="clear:both"></div>';
}
echo '</div>'; }

?>

Sorry to be a pain

ianhaney
04-17-2012, 03:22 PM
Hi mlseim

See in the code below where you have put

$minprice=mysql_real_escape_string($_POST['minprice'];
$maxprice=mysql_real_escape_string($_POST['maxprice'];

I have not got minprice and maxprice in my database, only price, could that be why I have got server error

if I added it into the database, could I leave it blank or do I need to add in the vales for example from 100,000 to 3000,000,0

Kind regards

Ian

mlseim
04-17-2012, 04:01 PM
minprice and maxprice are variables from your form.

you will compare them to the values in column named 'price' ... so what's there is correct.

This line is commented-out:
$result = mysql_query($selectsql);

Uncomment that line and change it to:
$result = mysql_query($query);

You didn't perform the query ... that's why you got an error.

ianhaney
04-17-2012, 04:15 PM
Hi mlseim

I have still got the error after changing the line of code

The coding is below again


<html>
<body>
You have searched for a&nbsp;<?php echo $_POST["propertytype"]; ?>
<br>
You have searched in the location of <?php echo $_POST["location"]; ?>
<br />
You have searched for <?php echo $_POST["bedrooms"]; ?>&nbsp;bedrooms
<br />
You have searched for <?php echo $_POST["bathrooms"]; ?>&nbsp;bathrooms
<br />
You have searched for <?php echo $_POST["minprice"]; ?>
<br />
You have searched for <?php echo $_POST["maxprice"]; ?>
<br><br>
Please find your results below
</body>
</html>
<br><br>
<?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";

/*$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:<br>";
trigger_error(mysql_error());
}
elseif(!mysql_num_rows($result))
{
// no records found by query.
echo "No records found";
}
else
{*/
$i = 0;
echo '<div class="container" style="float:left;">';
while($row = mysql_fetch_array($result)) { // Loop through results
$i++;
echo '</div>';
echo '<div class="textholder" style="font-family:helvetica; font-size:13px; float:left; padding-top:10px;">';
echo "<span style=\"color:green;\"><b>Displaying record $i<br>\n</b><br></span>";
echo "<b>" . $row['id'] . "</b><br>\n"; // Where 'id' is the column/field title in the database
echo "Property Type: ". $row['typeProperty'] . "<br>\n"; // as above
echo "Bedrooms: ". $row['bedrooms'] . "<br>\n"; // ..
echo "Bathrooms: ". $row['bathrooms'] . "<br>\n"; // ..
echo "Garden: ". $row['garden'] . "<br>\n"; // ..
echo "Description: ". $row['description'] . "<br>\n"; // ..
echo "Price: ". $row['price'] . "<br>\n"; // ..
echo "Location: ". $row['location'] . "<br>\n"; // Where 'location' is the column/field title in the database
echo '</div>';
echo '<div style="clear:both"></div>';
}
echo '</div>'; }

?>

mlseim
04-17-2012, 04:53 PM
Tell me what the error says ... and if it provides a line number of error.

ianhaney
04-17-2012, 04:59 PM
I am getting the following error

Server error
The website encountered an error while retrieving http://www.2up2downhomes.com/saleresults.php. It may be down for maintenance or configured incorrectly.
Here are some suggestions:
Reload this web page later.
HTTP Error 500 (Internal Server Error): An unexpected condition was encountered while the server was attempting to fulfil the request.

ianhaney
04-17-2012, 06:29 PM
any ideas what it could be Mlseim

mlseim
04-17-2012, 08:46 PM
You have error reporting turned off ... that needs to be reporting.

Put this at the top of your script ...

<?php
error_reporting(E_ALL ^ E_NOTICE);

see if the error is different.

ianhaney
04-17-2012, 10:43 PM
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


<html>
<body>
You have searched for a&nbsp;<?php echo $_POST['propertytype']; ?>
<br>
You have searched in the location of <?php echo $_POST['location']; ?>
<br />
You have searched for <?php echo $_POST['bedrooms']; ?>&nbsp;bedrooms
<br />
You have searched for <?php echo $_POST['bathrooms']; ?>&nbsp;bathrooms
<br />
You have searched for <?php echo $_POST['minprice']; ?>
<br />
You have searched for <?php echo $_POST['maxprice']; ?>
<br><br>
Please find your results below
</body>
</html>
<br><br>

<?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 '<h1>Results ('.((is_array($results) && count($results) > 0) ? count($results) : 0).')</h1>';
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 '<div class="price t_color1" > ' . $property->PRICE .' </div> <div class = "t_color1 t_bold"> '. $property->BEDROOMS.$property->PLUS .' ' . $property->STUDIO .' ' .$property->TYPE.' in '.$property->AREA .' </div> <div class = "content1_img float_l">
<a href ="'. $property->URL .'" target="_blank"><img src="'. $property->IMAGE .'"></a> </div> <div class = "content1_text float_l"> <b class="t_color5">Property</b><br /><br /> '. $property->DATAPROPERTY . '</div> <div class = "content2_text float_2"><b class="t_color5">Features</b><br /> <br /> '. $property->DATAFEATURE . '</div> <div class = "left_col float_l"><p><img src = "http://www.for-rent-nerja.com/images/spacer2.jpg" alt = "" width="500"/></p></div>' ;
}
echo "<div align=\"right\">";
if($i!=1)
echo "<a href=\"?page=" . ($i-1) . "$search\" class=\"t_color1\">Previous Page</a> ";
echo "Page " . $i . " of " . $pages . " ";
if($i!=$pages)
echo "<a href=\"?page=" . ($i+1) . "$search\" class=\"t_color1\">Next Page</a> ";

echo "</div>";
}
else
echo '<h1>No Results</h1>';

?>

<?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);
*/
?>



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum