 |
| |
| Author |
Thread |
timtom1
Obsessive-Compulsive (I) InmateFrom:
Insane since: Mar 2005
|
posted 03-06-2005 22:14
I think this belongs in here not the general chit chat sorry for posting twice.
This is a modification of this post http://www.ozoneasylum.com/21781 however can anyone see what is wrong with $sql_2 query it works when I comment it out, but I am trying to bring in infomration from another table.
[code]
<?php
////////////////////// DB-Access /////////////////////////
/*Generates the connection with the database and returns a
valid connection for the one calling. */
function hookUpDb(){
Global $connection;
$host = 'localhost';
$user = '';
$password = '';
$db = 'test';
if(mysql_connect($host,$user,$password)){
$connection = mysql_connect($host,$user,$password);
mysql_select_db($db,$connection);
return $connection;
}else{
print("<b>Sorry, cannot connect to the database.</b>");
}
}
/////////////////////////////////////////////////////
////////////////////// Generic QueryAskers///////////
/*Use when no returned result is excepted.
returns true or mysql_error. */
function doQuery($sql){
Global $connection;
hookUpDb();
if(!mysql_query($sql,$connection)){
return mysql_error();
}
return true;
}
/*Use this when you need to use custom made SQL-queries on the database.
Returns a 2 dimensional array with the result. */
function doQueryResult($sql){
Global $connection;
hookUpDb();
if($sql != ""){
$result = mysql_query($sql,$connection);
$item = array();
while($item = mysql_fetch_array($result)){
$items[] = $item;
}
return $items;
mysql_free_result($result);
} else {
print("Need correctly formed SQL that returns a resultset.");
}
}
/////////////////////////////////////////////
//Calculator...
if(isset($_POST['Find']) && !empty($_POST['userPcode']) && !empty($_POST['usermiles'])){
$userPcode = $_POST['userPcode'];
$usermiles = $_POST['usermiles'];
$sql_1 = "SELECT * FROM preston_data WHERE pcode = '".$userPcode."' OR place_name = '".$userPcode."'";
$result = doQueryResult($sql_1);
/////////////////////////////////////
// $sql_2 is the problem statement //
/////////////////////////////////////
$sql_2 = "SELECT p.property_name FROM property AS p, preston_data AS pd WHERE ( ( pd.pcode = '".$userPcode."' ) AND ( p.pcode = ".$userPcode."') )";
$result = doQueryResult($sql_2);
$userLat = $result[0]['latitude'];
$userLong = $result[0]['longitude'];
$userLocation = $result[0]['place_name'];
$userLocation = $result[0]['property_name'];
$sql_3 = "SELECT * FROM preston_data as p WHERE (SQRT( (69.1 * (".$userLat." - p.latitude)) * (69.1 * (".$userLat." - p.latitude)) + (53.0 *(".$userLong." - p.longitude)) * (53.0 *(".$userLong." - p.longitude))) <= ".$usermiles."";
$finalResult = doQueryResult($sql_3);
}
?>
<!doctype html public "-//W3C//DTD HTML 4.0 //EN">
<html>
<head>
<title></title>
</head>
<body>
<form action="#" method="post">
Enter full Postcode or Place Name:
<input type="text" name="userPcode" size="20" value="<? echo $_POST['userPcode'] ?>"><br>
Search within
<SELECT id=usermiles size=1 name="usermiles">
<OPTION value=11 selected>[ no preference]</OPTION>
<OPTION value=0.5>½ mile of this area</OPTION>
<OPTION value=1>1 mile of this area</OPTION>
<OPTION value=3>3 usermiles of this area</OPTION>
<OPTION value=5>5 usermiles of this area</OPTION>
<OPTION value=10>10 usermiles of this area</OPTION>
</SELECT><br>
<input type="submit" name="Find" value="Search">
</form>
<?php
if(is_array($finalResult)){
//reminder
//use "include" to get $finalResult on a seperate page
//fieldset; puts a boarder and splits each search result up
//legend; places Location within ... mile/s in the fieldset
foreach($finalResult as $location){
print("<fieldset><legend>Location within ".$_POST['usermiles']." mile/s from ".$userLocation."</legend>n");
print("Place: ".$location['place_name']."<br>Postcode: ".$location['pcode']);
print("none: ".$location['property_name']);
print("</fieldset>");
}
}
?>
When I add $sql_2 I get nothing no results showing however when I comment out $sql_2 statement, $result = doQueryResult($sql_2);, $userLocation = $result[0]['place_name'];
and print("none: ".$location['property_name']);
It works again what can anyone see what is wrong with my $sql_2 statement as it works when i do a query in PHPmyAdmin?
Also posted here http://www.phpaddict.com/forum2/1081.html
|
poi
Paranoid (IV) InmateFrom: France
Insane since: Jun 2002
|
posted 03-06-2005 22:32
|
timtom1
Obsessive-Compulsive (I) InmateFrom:
Insane since: Mar 2005
|
posted 03-06-2005 22:57
Hi poi I have added credit on phpaddict sorry forgot
without $sql_2 statement it displays the postcode and place name on screen so I thought to get property name to display I would have to add it with latitude, longitude, place_name .
I am still new to PHP.
the SQL statement that is $sql_2 displays the property name in PHPmyAdmin. Well however where ".$userPcode."' is I put an actual postcode there so it displays the property name.
$sql_2 = "SELECT p.property_name FROM property AS p, preston_data AS pd WHERE ( ( pd.pcode = '".$userPcode."' ) AND ( p.pcode = ".$userPcode."') )";
Hope this makes sense
I will post a more recent version of the code which is on my laptop tomorrow to see if its any clearer.
(Edited by timtom1 on 03-06-2005 23:00)
|
poi
Paranoid (IV) InmateFrom: France
Insane since: Jun 2002
|
posted 03-07-2005 02:12
Your previous message is not crystal clear for me, but it's 2am. I need to sleep.
try to replace :
$sql_2 = "SELECT p.property_name FROM property AS p, preston_data AS pd WHERE ( ( pd.pcode = '".$userPcode."' ) AND ( p.pcode = ".$userPcode."') )";
by
$sql_2 = "SELECT * FROM property AS p, preston_data AS pd WHERE ( ( pd.pcode = '".$userPcode."' ) AND ( p.pcode = ".$userPcode."') )";
(Edited by poi on 03-07-2005 02:13)
|
timtom1
Nervous Wreck (II) InmateFrom:
Insane since: Mar 2005
|
posted 03-07-2005 09:25
Hi Poi
Select all kind of worked however it is only displaying the first letter of the first word of Property Name for example in my database property name is Lostock View and it is just displaying L
[code]
//Calculator...
if(isset($_POST['Find']) && !empty($_POST['userPcode']) && !empty($_POST['usermiles'])){
$userPcode = $_POST['userPcode'];
$usermiles = $_POST['usermiles'];
$sql_1 = "SELECT * FROM preston_data WHERE pcode = '".$userPcode."' OR place_name = '".$userPcode."'";
$result = doQueryResult($sql_1);
$sql_2 = "SELECT * FROM property AS p, preston_data AS pd WHERE ( ( pd.pcode = '".$userPcode."' ) AND ( p.pcode = ".$userPcode."') )";
$result = doQueryResult($sql_2);
$userLat = $result[0]['latitude'];
$userLong = $result[0]['longitude'];
$userLocation = $result[0]['place_name'];
$dbhouse = $result[0] ['property_name']; // something to do with this however this is needed here
$sql_3 = "SELECT * FROM preston_data as p WHERE SQRT( (69.1 * (".$userLat." - p.latitude)) * (69.1 * (".$userLat." - p.latitude)) + (53.0 *(".$userLong." - p.longitude)) * (53.0 *(".$userLong." - p.longitude))) <= ".$usermiles."";
$finalResult = doQueryResult($sql_3);
}
?>
<!doctype html public "-//W3C//DTD HTML 4.0 //EN">
<html>
<head>
<title></title>
</head>
<body>
<form action="#" method="post">
Enter full Postcode or Place Name:
<input type="text" name="userPcode" size="20" value="<? echo $_POST['userPcode'] ?>"><br>
Search within
<SELECT id=usermiles size=1 name="usermiles">
<OPTION value=11 selected>[ no preference ]</OPTION>
<OPTION value=0.5>½ mile of this area</OPTION>
<OPTION value=1>1 mile of this area</OPTION>
<OPTION value=3>3 usermiles of this area</OPTION>
<OPTION value=5>5 usermiles of this area</OPTION>
<OPTION value=10>10 usermiles of this area</OPTION>
</SELECT><br>
<input type="submit" name="Find" value="Search">
</form>
<?php
if(is_array($finalResult)){
//reminder
//use "include" to get $finalResult on a seperate page
//fieldset; puts a boarder and splits each search result up
//legend; places Location within ... mile/s in the fieldset
foreach($finalResult as $location){
print("<fieldset><legend>Location within ".$_POST['usermiles']." mile/s from ".$userLocation."</legend>\n");
print("Place: ".$location['place_name']."<br>Postcode: ".$location['pcode']);
print("<br>Property Name: ".$dbhouse['property_name']); // Only displays the first letter of first word??
print("</fieldset>");
}
}
?>
(Edited by timtom1 on 03-07-2005 09:26)
|
timtom1
Nervous Wreck (II) InmateFrom:
Insane since: Mar 2005
|
posted 03-07-2005 11:15
I actually just found out as well as only displaying the first letter of the first word it only displays the first in the database table once I add that, bacisly it says all poperty names are L
Where is should display for example Lostock View
(Edited by timtom1 on 03-07-2005 11:17)
|
poi
Paranoid (IV) InmateFrom: France
Insane since: Jun 2002
|
posted 03-07-2005 11:26
You realise that you do : code:
$dbhouse = $result[0] ['property_name'];
then code:
print("<br>Property Name: ". $dbhouse['property_name'] );
Actually you're lucky it shows the first letter and don't crash with an evil grin.
Reading your code twice would avoid that kind of mistakes.
|
timtom1
Nervous Wreck (II) InmateFrom:
Insane since: Mar 2005
|
posted 03-07-2005 15:51
OK I will give it a try when I get in
|
timtom1
Nervous Wreck (II) InmateFrom:
Insane since: Mar 2005
|
posted 03-07-2005 18:10
Didn't work still only displayed L for property name 
[code]
//Calculator...
if(isset($_POST['Find']) && !empty($_POST['userPcode']) && !empty($_POST['usermiles'])){
$userPcode = $_POST['userPcode'];
$usermiles = $_POST['usermiles'];
$sql_1 = "SELECT * FROM preston_data WHERE pcode = '".$userPcode."' OR place_name = '".$userPcode."'";
$result = doQueryResult($sql_1);
$sql_2 = "SELECT * FROM property AS p, preston_data AS pd WHERE ( ( pd.pcode = '".$userPcode."' ) AND ( p.pcode = ".$userPcode."') )";
$result = doQueryResult($sql_2);
$dbhouse = $result[0] ['property_name'];
print("<br>Property Name: ".$dbhouse['property_name']);
$userLat = $result[0]['latitude'];
$userLong = $result[0]['longitude'];
$userLocation = $result[0]['place_name'];
$sql_3 = "SELECT * FROM preston_data as p WHERE SQRT( (69.1 * (".$userLat." - p.latitude)) * (69.1 * (".$userLat." - p.latitude)) + (53.0 *(".$userLong." - p.longitude)) * (53.0 *(".$userLong." - p.longitude))) <= ".$usermiles."";
$finalResult = doQueryResult($sql_3);
}
?>
|
poi
Paranoid (IV) InmateFrom: France
Insane since: Jun 2002
|
posted 03-07-2005 18:37
doh! but you do code:
$dbhouse = $result[0] ['property_name'];
No surprise that code:
print("<br>Property Name: ".$dbhouse['property_name']);
outputs a single letter. $dbhouse is a string and *already* represents the 'property_name' therefore there absolutely no need for the ['property_name'] part in your print( )
|
timtom1
Nervous Wreck (II) InmateFrom:
Insane since: Mar 2005
|
posted 03-07-2005 19:12
So it should look like this?
$result = doQueryResult($sql_2);
$dbhouse = $result[0] ['property_name'];
print("<br>Property Name: ".$dbhouse);
I will give it a try
|
poi
Paranoid (IV) InmateFrom: France
Insane since: Jun 2002
|
posted 03-07-2005 19:17
yes.
Since you're new to PHP, you should get the relfex to output your variables as soon as you meet a problem. The print_r( ) and var_dump( ) functions are *extremely* usefull.
(Edited by poi on 03-07-2005 19:22)
|
timtom1
Nervous Wreck (II) InmateFrom:
Insane since: Mar 2005
|
posted 03-07-2005 19:29
Cool that worked, however its displaying all the place names the same for example I type:
Postcode: PR1 7EN
Miles: 4
I get
Location Preston
Postcode: PR1 7EN
Property Name: Lostock View
Location Penwortham
Postcode: PR1 8NY
Property Name: Lostock View
etc
And Penwortham's property name should be different and only those two should display however its displaying all still that are with 4 miles for example; should I use a Distinct?
|
poi
Paranoid (IV) InmateFrom: France
Insane since: Jun 2002
|
posted 03-07-2005 19:55
You need to do a join in your $sql_3 query to request the property_name corresponding to the zipcode of the locations within the range entered by the user. Finally the code displaying the results should be :
code:
if( is_array( $finalResult ) )
{
print( "<fieldset><legend>Location within ". $_POST['usermiles']. " mile/s from ". $userLocation ."</legend>\n" );
foreach( $finalResult as $location )
{
print( "Place: ". $location['place_name'] ."<br>Postcode: " .$location['pcode'] );
print( "<br>Property Name: ". $location['property_name'] );
}
print("</fieldset>");
}
Note to self : I should really consider to become a freelance instead of vainly searching a job and doing the work of others for free.
|
timtom1
Nervous Wreck (II) InmateFrom:
Insane since: Mar 2005
|
posted 03-07-2005 20:00
ok, i will give it a go thanks for all your help 
|
timtom1
Nervous Wreck (II) InmateFrom:
Insane since: Mar 2005
|
posted 03-14-2005 09:52
Having a minor problem with teh $sql_3 join, its repeating its self when there are only two records in property its getting them from preston_data?? The 1st record and 4th are correct the rest shouldn't be there??
|
poi
Paranoid (IV) InmateFrom: France
Insane since: Jun 2002
|
posted 03-14-2005 14:44
could you give us an example of query you get with $sql_3 ?
|
timtom1
Nervous Wreck (II) InmateFrom:
Insane since: Mar 2005
|
posted 03-14-2005 18:56
//Calculator...
if(isset($_POST['Find']) && !empty($_POST['userPcode']) && !empty($_POST['usermiles'])){
$userPcode = $_POST['userPcode'];
$usermiles = $_POST['usermiles'];
$sql_1 = "SELECT * FROM preston_data WHERE pcode = '".$userPcode."'";
// $sql_1 = "SELECT * FROM preston_data WHERE pcode = '".$userPcode."' OR place_name = '".$userPcode."'";
$result = doQueryResult($sql_1);
// $sql_2 = "SELECT p.property_name FROM property as p, preston_data as pd WHERE ( ( pd.pcode = 'PR1 7EN' ) AND ( p.pcode = 'PR1 7EN' ) ) LIMIT 0, 30";
// $sql_2 = "SELECT * FROM property, preston_data WHERE ( ( preston_data.pcode = '".$userPcode."' ) AND ( property.pcode = '".$userPcode."' ) )";
// $result = doQueryResult($sql_2);
$userLat = $result[0]['latitude'];
$userLong = $result[0]['longitude'];
$userLocation = $result[0]['place_name'];
// $sql_3 = "SELECT * FROM preston_data as p WHERE SQRT( (69.1 * (".$userLat." - p.latitude)) * (69.1 * (".$userLat." - p.latitude)) + (53.0 *(".$userLong." - p.longitude)) * (53.0 *(".$userLong." - p.longitude))) <= ".$usermiles."";
$sql_3 = "SELECT * FROM property as py, preston_data as p WHERE SQRT( (69.1 * (".$userLat." - p.latitude)) * (69.1 * (".$userLat." - p.latitude)) + (53.0 *(".$userLong." - p.longitude)) * (53.0 *(".$userLong." - p.longitude))) <= ".$usermiles."";
// AND( ( py.pcode <= '".$userPcode."' ) AND ( p.pcode <= '".$userPcode."' ) )"";
$finalResult = doQueryResult($sql_3);
(Edited by timtom1 on 03-14-2005 19:05)
|
poi
Paranoid (IV) InmateFrom: France
Insane since: Jun 2002
|
posted 03-14-2005 21:32
I meant : could you echo you variable $sql_3 so that you can check it in the web admin of your DB, and if it doesn't work after several attempts to fix it by yourself you could post an example of query to see if someone can find the problem here.
... or you could hire my services.
Do you want to do a search by zipcode for yourself or for a client ? My former Project Manager would have destroyed me if I had spent more than an afternoon on a problem of queries generation/exectution. Sorry, I really enjoy to help people but it upsets me to do the work of somebody for free when I have some difficulties to find a fulltime job.
(Edited by poi on 03-14-2005 21:53)
|
timtom1
Nervous Wreck (II) InmateFrom:
Insane since: Mar 2005
|
posted 03-14-2005 21:57
|
poi
Paranoid (IV) InmateFrom: France
Insane since: Jun 2002
|
posted 03-14-2005 22:03
Hopefully for you. Whatever 11days ( since your post on PhpAddict is dated of the 3rd march ) on that is an incredible waste of time.
|
timtom1
Nervous Wreck (II) InmateFrom:
Insane since: Mar 2005
|
posted 03-14-2005 22:38
.
(Edited by timtom1 on 03-14-2005 22:59)
|
timtom1
Nervous Wreck (II) InmateFrom:
Insane since: Mar 2005
|
posted 03-14-2005 22:44
I have been doing assignments for university I do have a life apart from PHP you know, thanks for your help again.
(Edited by timtom1 on 03-14-2005 22:48)
|
How do I search based on zipcodes