Need help concerning zipcode tables join statement-CodePudding

Very new to coding. I have 2 tables, zips_usa which includes all properly formatted zips (zipcode, lat / lon / city / state etc) in the US, and table ageus which includes columns such as first name, last name, category, comments. It took 3 weeks to write the zipcode radius search but I got there, mainly due to answers I found on this site. Thanks. I now need help dealing with the output. I think I need a join statement between the tables and cannot get my head around the logic. Any help would be appreciated.

sql statement for zip radius search runs error free. Input is performed by a html form.

  $sql = "SELECT latitude, longitude FROM zips_usa WHERE zipcode = '$zipcode'";

Table ageus column details (where users post to)

id first_name last_name dog_type comments city state entered gender category zipcode

How do I join the two tables so I can select output that includes all info from table ageus that includes zip codes found within the X mile radius?

CodePudding user response：

All you need is a table with a zipcode, lat, lng, a radius to find every record in the table that is within that radius, and a list of the lat and lng for each zipcode.

I made a "ziplocation" table.

$sql = "SELECT `lat`,`lng` FROM `zipLocation` WHERE `zip` = $zip ";
$results = mysqli_query($link,$sql);
list($lat,$lng) = mysqli_fetch_array($results, MYSQLI_NUM);

The use the results to populate the table with lat and lng in addition to the zipcode.

The best approach, IMHO, is to use the list of zipcode lat and lng to put populate the table's lat and long.

A table of zips with lat and lang is easy to find.
Here is the query:

$sql = "SELECT `level`,`id`,`pics`,`name`,`city`,`zip` , ( 3959 * acos( cos( radians($lat) ) * cos( radians( `lat` ) ) * cos( radians( `lng` ) - radians($lng) )   sin( radians($lat) ) * sin( radians( `lat` ) ) ) ) AS `distance` FROM `photos` WHERE 1 HAVING `distance` <= $miles ORDER BY `distance` ASC";

I copied the above sql from a demo app I did for a client in Ft. Lauderdale wanting an dating site.
The user enters their zipcode and a radius and it shows the available girls within that radius.

The number at the bottom of the page is the number of seconds it took to generate the HTML.

Dating app where only Girls within a zipcode radius are shown

CodePudding user response：

Thanks again to everyone who answered. Using Misunderstood's suggestions to populate the fields, I was able to get the script fully working by using the "Populating a table from query results (mysql)" thread, from 13 years ago, amending the details accordingly to mysqli. I've only been coding since I retired a few months ago. This is a fantastic site for learning all this new stuff. More importantly, we now have a working dog / animal rescue site YAY.