Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
Hi I'm pretty new to the php and MySQL.And I need to make a search engine for a friend who owns some restraints. In the search engine the user will be able type their address in and hit search. Once the results post up, I would like it to show any building that are in the users area. Starting with the closest. Dose anyone know a way to do this? Any help would be appreciated.
I'm using PHP 5.2 MySQL 5.1 and Dreamweaver CS3.Just let me know if there is anymore info you need.
thank you
Airun
I think you're going to need to maintain and plot zipcodes; I found this resource, which looks promising.
As a database design, I think I'd make the main location the primary record and then do a distance formula based on the user's zipcode. That way, it wouldn't really matter if the locations were local, regional, or national.
HTH
______________________
My work in progress. I hate JS. :P
0 
Awesome! thanks again shutat. you seem to always be the one to help me out. i really appreciate it. I'll see what I can do with this ( Im not very good at php and mysql yet) I'm still trying to getting the hang of it. I'll let you know if this worked or not. again thanks alot! your a life saver.
0
You give me way too much credit. :P
I don't know if it will help or how accurate it is, but I've made a simple script to compute distance based on similar zips. You can check it in practice here as long as the host keeps it active.
If it's close to what you need, then we can go from there if you still need help... well I'll try and help as best I can. :)
______________________
My work in progress. I hate JS. :P
0
thats exactly what Im looking for.
But I only want his restaurants to come up.
like you type 41501 and it'll come up with all his resturants starting witht the closest.
but, wow. Im very impressed with that example.
its very nice.
0
what was the formula that you used?
I might be able to get it then. (not really 100% positive on that)Airun
0
Hi,
What I did was create the table
mysql_query("create table if not exists tbl_zipcodes( ID int(5) unsigned auto_increment, zipcode double not null default 0, abrev varchar(3) not null default '', lat varchar(16) not null default '', lon varchar(16) not null default '', city varchar(64) not null default '', state varchar(64) not null default '', primary key(ID) )TYPE=MyISAM;") or die(mysql_error());For the query, I used
$src = mysql_query("select * from tbl_zipcodes where zipcode='$_POST[zipc]' limit 1;") or die(mysql_error()); if(is_resource($src) && mysql_num_rows($src) == 1) { $s_res = mysql_fetch_assoc($src); $res = mysql_query("select *, sqrt(" . " ((69.1 * (lat - $s_res[lat])) * (69.1 * (lat - $s_res[lat]))) + " . " ((53 * (lon - $s_res[lon])) * (53 * (lon - $s_res[lon])))" . ") as distance from tbl_zipcodes where zipcode != '$s_res[zipcode]'" . " and zipcode like '" . substr($_POST["zipc"],0,3) . "%'" . " order by distance asc;") or die(mysql_error());My apologies for the formatting, it's hard to get code to display neatly here sometimes.
Hope that helps.
[edit]
$s_res was the fetch_assoc result from a previous query that retrieved the columns of the zipcode that the user types in the form.
[/edit[______________________
My work in progress. I hate JS. :P
0
Sweet, I got it working!
Thankx alot for helping me once again.
I've said it once, i'll say it again. "your a life saver."
Sorry it took ao long to reply back to you, Ive been busy.
0
hey again, I'm revisiting this subject originally had it working, but I changed the format of how the data came out. Now it won't show the data information, It shows the table labels and the bottom links and that’s it. Here’s the code that I'm having trouble with right now.
<p align="center">
<table border="0" cellspacing="2" cellpadding="2">
<tr>
<th>Distance</th>
<th>Clinic</th>
<th>Address</th>
<th>Zip</th>
<th></th>
</tr><?php
//connect to your database ** EDIT REQUIRED HERE **
mysql_connect("localhost","username","password"); //(host, username, password)//specify database ** EDIT REQUIRED HERE **
mysql_select_db("bbnet") or die("Unable to select database"); //select which database we're using
$src = mysql_query("select * from
tbl_zipcodes where zipcode='" . $_POST["zipc"] . "' limit
3;") or die(mysql_error());
if(is_resource($src) && mysql_num_rows($src) == 1) {
$s_res = mysql_fetch_assoc($src);$res = mysql_query("select *, sqrt(" .
" ((69.1 * (lat - $s_res[lat])) * (69.1 * (lat - $s_res[lat]))) + " .
" ((53 * (lon - $s_res[lon])) * (53 * (lon - $s_res[lon])))" .
") as distance from tbl_zipcodes where zipcode != '$s_res[zipcode]'" .
" and zipcode like '" . substr($_POST["zipc"],0,3) . "%'" . "
order by distance asc;") or die(mysql_error());
}$i=0;
while ($i < $scr) {$f1=mysql_result($res,$i,"distance");
$f2=mysql_result($res,$i,"state");
$f3=mysql_result($res,$i,"city");
$f4=mysql_result($res,$i,"zipcode");
?><tr>
<td><?php echo $f1; ?></td>
<td><?php echo $f2; ?></td>
<td><?php echo $f3; ?></td>
<td><?php echo $f4; ?></td>
<td><?php echo "Schedule Appointment"?></td>
</tr>
<?php
$i++;
}
?>Any help please?
0
Hi,
I *think* you can probably just change a few lines.
Where you check the "posted" zipcode, limit it to 1 because this is the zip being tested against those in your database.
I don't know how your table is setup, but if you have the clinic names and addresses already within the db, then you could probably modify it as needed. Right now, you can get the distance, city, and state of the closest three using something like below
if(is_resource($src) && mysql_num_rows($src) == 1) { $s_res = mysql_fetch_assoc($src); $res = mysql_query("select *, sqrt(" . " ((69.1 * (lat - $s_res[lat])) * (69.1 * (lat - $s_res[lat]))) + " . " ((53 * (lon - $s_res[lon])) * (53 * (lon - $s_res[lon])))" . ") as distance from tbl_zipcodes where zipcode != '$s_res[zipcode]'" . " and zipcode like '" . substr($_POST["zipc"],0,3) . "%'" . " order by distance asc limit 3;") or die(mysql_error()); } while($row = mysql_fetch_assoc($res)) { ?> <tr> <td><?php echo round($row["distance"], 2); ?></td> <td><?php echo $row["city"]; ?></td> <td><?php echo $row["state"]; ?></td> <td><?php echo "Schedule Appointment"?></td> </tr> <?php } ?>Hope that helps.
0
Thanks alot shutat
but, I'm still having some problems with the code.
if I put something in the text box "zipc" then all I get is this error.Notice: Undefined variable: res in C:\wamp\www\search.php on line 49
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\wamp\www\search.php on line 49
I dont understand why? Any idea's?
0
Hi, could you post your entire script as you've got it now and the columns in the table?
With that error, it *may* be that $res isn't returning any results from the similar zips query. Before the call to while($row = mysql_fetch_assoc($res)) { ... }, try adding something like
die("res returned " . mysql_num_rows($res) . " rows");
0
no problem.
here my first page of html:<h2 align="center">Search Zip codes</h2>
<p align="center">Your zip code here</p>
<form action="search.php" method="post" name="zipsearch" id="zipsearch">
<div align="center">
<input name="zipc" type="text" size="10" maxlength="5" id="zipc" />
<input type="submit" name="Submit" value="Search" />
</div>
</form>which goes to php code.
mysql_connect("localhost","username","password"); //(host, username, password)
mysql_select_db("bbnet") or die("Unable to select database"); //select which database we're using
$src = mysql_query("select * from
tbl_zipcodes where zipcode='" . $_POST["zipc"] . "' limit
1;") or die(mysql_error());
if(is_resource($src) && mysql_num_rows($src) == 1) {
$s_res = mysql_fetch_assoc($src);$res = mysql_query("select *, sqrt(" .
" ((69.1 * (lat - $s_res[lat])) * (69.1 * (lat - $s_res[lat]))) + " .
" ((53 * (lon - $s_res[lon])) * (53 * (lon - $s_res[lon])))" .
") as distance from tbl_zipcodes where zipcode != '$s_res[zipcode]'" .
" and zipcode like '" . substr($_POST["zipc"],0,3) . "%'" . "
order by distance asc limit 3;") or die(mysql_error());
}while($row = mysql_fetch_assoc($res)) { ?>
<tr>
<td><?php echo round($row["distance"], 2); ?></td>
<td><?php echo $row["city"]; ?></td>
<td><?php echo $row["state"]; ?></td>
<td><?php echo "Schedule Appointment"?></td>
</tr>
<?php
}
?>
and I come up with this:Notice: Undefined variable: res in C:\wamp\www\search.php on line 49
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\wamp\www\search.php on line 49
hope this help you... to help me.
And once again thanks
0
Ok, it looks like it may be a scope problem. If $res fails to return a result on matching zips, then it would appear as an undefined variable once the mysql_fetch_assoc($res) function is called.
Try replacing the section shown below with the modified code below
if(is_resource($src) && mysql_num_rows($src) == 1) { $s_res = mysql_fetch_assoc($src); $res = mysql_query("select *, sqrt(" . " ((69.1 * (lat - $s_res[lat])) * (69.1 * (lat - $s_res[lat]))) + " . " ((53 * (lon - $s_res[lon])) * (53 * (lon - $s_res[lon])))" . ") as distance from tbl_zipcodes where zipcode != '$s_res[zipcode]'" . " and zipcode like '" . substr($_POST["zipc"],0,3) . "%'" . " order by distance asc limit 3;") or die(mysql_error()); while($row = mysql_fetch_assoc($res)) { ?> <tr> <td><?php echo round($row["distance"], 2); ?></td> <td><?php echo $row["city"]; ?></td> <td><?php echo $row["state"]; ?></td> <td><?php echo "Schedule Appointment"?></td> </tr> <?php } } ?>HTH
0
well that got rid of the error codes so thanks for that!
But I'm still not getting any results when I type a zip code in "zipc"? But when "zipc" is blank it pulls up information with distance.... where its getting the distances I don't know. could it be my data in the database? I only have our clinics information in it right now. Could that be why I'm not getting any results? I don't know if you can help without seeing the database but, thought I'd ask any way? if not I appreciate the help you've given me, thanks alot shutat!Airun
0
No worries. :)
I finally found the source I worked with a while ago, and tried to replicate your results. With a blank "zipc" field, I didn't get any results at all... it's strange that you are.
One thing you might want to add is a check of sorts to make sure the post["zipc"] field is valid. In the start of your php file, try adding
if(preg_match('/^([0-9]{5})+$/', $_POST["zipc"]) == 0) { die("invalid zip code"); }With the above, I only processed numerical zips of 5 digits, so if you need to modify it, you can add whatever you need inside of '/^([0-9]{5})+$/'
The next check is where the sqrt is calculated. If there are no matches found, the while($row = mysql_fetch_assoc($res)) { ... } will be invalid. Try changing with the following block and will take it from there.
... if(is_resource($src) && mysql_num_rows($src) == 1) { $s_res = mysql_fetch_assoc($src); $res = mysql_query("select *, sqrt(" . " ((69.1 * (lat - $s_res[lat])) * (69.1 * (lat - $s_res[lat]))) + " . " ((53 * (lon - $s_res[lon])) * (53 * (lon - $s_res[lon])))" . ") as distance from tbl_zipcodes where zipcode != '$s_res[zipcode]'" . " and zipcode like '" . substr($_POST["zipc"],0,3) . "%'" . " order by distance asc limit 3;") or die(mysql_error()); if(mysql_num_rows($res) > 0) { while($row = mysql_fetch_assoc($res)) { ?> <tr> <td><?php echo round($row["distance"], 2); ?></td> <td><?php echo $row["city"]; ?></td> <td><?php echo $row["state"]; ?></td> <td><?php echo "Schedule Appointment"?></td> </tr> <?php } } } else { echo "no clinics found"; } ...HTH
0
ok, I put the code in you suggested. Now I'm getting "no clinics found" when there is input in "zipc". Which may be a step forward than what i was getting before.
Before it was bringing the three first results up with some number in the distance. (Which were no correct) Now I get "invalid zip code".
Could it not be reading var9iable $res in the
while($row = mysql_fetch_assoc($res))
because its outside of the if statement?
0
Just three words... You are Awesome! It works great man, I really appreciate it. thank you. Every thing is working great.
And again, Thanks.
0  |
 |
 |
This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |
