Need some help :-)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply

Did you understand my problem?

Poll ended at Sun Sep 18, 2005 4:54 am

yes
0
No votes
no
0
No votes
kinda, but not all
1
100%
no clue waht you talking bout at all
0
No votes
 
Total votes: 1

Jermaine
Forum Newbie
Posts: 1
Joined: Thu Sep 08, 2005 4:30 am

Need some help :-)

Post by Jermaine »

patrikG | Please use

Code: Select all

and

Code: Select all

tags where approriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]
I have 3 single scripts that all work, now i want to make one script of all these three.

First i have an input field that searches for all zip codes in a given radius and gives me an array of all these.
Second i have a script that gives me all contact data of people living in this area and gives me a list of these (in some areas up to 300)
Third i have a script that makes me a google like navigation of all data from the peoples table for ONE given zip code.

What i want to do is have the last script do the google like navigation in the result from the second script, but here i have no way to bring in start and limit.

here are my script parts:

zip area:


$PI = 3.14159265358979;

function getGeo($plz) {
	$result = mysql_query("SELECT long, lat FROM plz_de WHERE plz >= $plz ORDER BY plz LIMIT 1");
	mysql_data_seek($result, 0);
	return mysql_fetch_object($result);
}

function Entfernungsberechnung($inputlat, $inputlong, $nextlat, $nextlong) {
	global $PI;
	
	$nextlat= $nextlat/ 180 * $PI;
	$nextlong= $nextlong/ 180 * $PI;
	// Die Formel zur Entfernungberechnung bedient sich einer Einheitskugel
	$e = sin($inputlat) * sin($nextlat) + cos($inputlat) * cos($nextlat) * cos($nextlong-$inputlong);
	return (acos($e) * 6378.388);
}


$PLZ = getGeo($plz);

$maxbreite = $PLZ->breite + ($umkreis / 111.324);		
$minbreite = $PLZ->breite - ($umkreis / 111.324);

$maxlaenge = $PLZ->laenge + ($umkreis / 60);			 
$minlaenge = $PLZ->laenge - ($umkreis / 60);

$PLZ->breite = $PLZ->breite / 180 * $PI;
$PLZ->laenge = $PLZ->laenge / 180 * $PI;


$result = mysql_query("SELECT * FROM plz_de WHERE breite > $minbreite AND breite < $maxbreite AND laenge > $minlaenge AND laenge < $maxlaenge;");

for ($i = mysql_num_rows ($result) - 1; $i >=0; $i--) {
	mysql_data_seek($result, $i);
	$row = mysql_fetch_object ($result);


	$entfernung = Entfernungsberechnung($PLZ->breite, $PLZ->laenge, $row->breite, $row->laenge);
	if ($entfernung < $umkreis) {
		$treffer[$row->plz] = $entfernung;
	}
}

asort($treffer);



next i search in the peoples table:


foreach ($treffer as $value => $wert) {


$sql = "SELECT *
	FROM x_people where plz = $value";

$result = mysql_query($sql,$connection) or die("not working.");

$gone=0;

while ($row = mysql_fetch_array($result)) {
	$haus = $row['name'];
	$street = $row['strasse'];
	$zip = $row['plz'];
	$city = $row['ort'];
	$link = $row['url'];

...
...
...

}

}


plz is equal to zip in german breite means latitude laenge means longitude.

Maybe some of you guys have a zip search module with pagination working on your page or understand what i mean and can help.


The question in other words:

i have a script that gives me zipcodes and with this zipcodes i have to search for users in another table matching these zipcodes i have stored in an array or working in the shown "foreach" statement.

What i have tried so far did not work, as i need a start and limit parameter, but when the data is collected from users table it for example stops at 8 as there are only 8 users in this zip area and then it jumps one zipcode further and the counting starts at 1



I made my mind up for some different way to solve the problem for almost 12 hours now and cant find a solution.

Please help me sleeping again.
#lol#

Jermaine 
[color=red][b]patrikG[/b] | Please use

Code: Select all

and

Code: Select all

tags where approriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

I think what you need is to add the LIMIT/OFFSET parameters on the select of the users in the zip area

Code: Select all

SELECT X FROM Y WHERE inzipcode=TRUE LIMIT x OFFSET y;
Hope that is what you are needing.
Post Reply