LIMIT CLAUSE in "nested" SQL

[fresca]

Hi all,
I'm sorta new to this and am definetely in need of help.

Here's my problem.

I have a database with two tables:

One is for zipcodes and the other is for vendors.

My first query selects all the UNIQUE zipcodes from the first table where the distance from a submitted zipcode natches a certain requirement.
OK.

Now, for each one of these unique zipcodes I am querying the vendor table to return the list of all vendors who are in any of the usable zipcodes.

I want to have ten records per page.

Here's the problem. I can't seem to use a LIMIT in the second query.

For each of the qualified zipcodes there may be more OR less numbers of vendors who fall into the catagory.

Therefore, if there are not at least TEN vendors for each zipcode, the LIMIT is never reached and the count starts all over agin.

Can anyone help with this?

I hope this makes sense, and thanks in advance,

fresca

[Sxooter]

I think I get what you wanna do. got some SQL that almost works you could show?

[fresca]

I sure do. Thanks.
here it is (I'll try and keep it as lean as I can)

//mySQL Query THIS FINDS THE ZIPCODES THAT ARE WITHIN A CERTAIN DISTANCE
$sql = "SELECT DISTINCT ZIPCode, Longitude, Latitude FROM zipCodes WHERE
Latitude >= $minLat
AND Latitude <= $maxLat
AND Longitude >= $minLong
AND Longitude <= $maxLong";

	if (!$result = mysql_query($sql)) {
		//Error in mySQL query
		print "There was an error in sql statement, ".mysql_error()."<br><b>$sql</b>";
		exit;
	}
	else {
		$zcdDistance = new DistanceAssistant;

		while ($myrow = mysql_fetch_array($result)){


			$Distance = $zcdDistance->Calculate($Latitude,$Longitude,$myrow["Latitude"],$myrow["Longitude"]);
			   if ($Distance <= $Miles){
				//this ZIP Code is within $Miles of $ZIPCode
				//Here is where I pass the chosen ZIPCODES into another SQL

				$zc = $myrow["ZIPCode"];



				$sql = "SELECT * FROM vendors WHERE zip_code = '$zc' limit 0,10";

//As vendors are selected depending on each passed xipcode they are displayed. If there are more than ten then the limit works. If there are not then the while statement reiterates and passes another zipcode. So, the page displays values for zipcodes until it the query gets one that has more than 10 associated vendors.

				$newresult = mysql_query($sql,$dbConn) or die ("NO WAY.");
				  	while ($newrow = mysql_fetch_array($newresult))

					{

				  	$name= $newrow["name"];
					$address = $newrow["address"];
					$phone = $newrow["phone"];
					$desc = $newrow["description"];
					$vendor_menu = $newrow["vendor_show"];

Well there it is.
I can't fiqure out how to display 10 per page short of using a simple count loop as I print the results but then I can't go back and forth between pages without starting the count over each time.

Thanks for the help in advance,

fresca

[Sxooter]

You should be able to pass an offset offset (hehe) variable that makes your second query like this:

select * from table where zip=$zip limit $offset,10

or am I missing something?

[fresca]

Sxooter,
That's what I have now actually, I've just used "0" instead of a variable.

It's not the offset that seems to be the problem.

It's the number of records it's pulling within that LIMIT clause that cause the problem.

The loop will continue feeding new zips to the second query until it sends one which will allow the LIMIT to find enough records to satisfy it's parameters, THEN and only then will the script move on. Hence I'm getting alot more than 10 records per page.

This thing seems so simple but it's killin' me.

Thanks for the help

fresca

[kevinsequeira]

$sql = "SELECT * FROM vendors WHERE zip_code = '$zc' limit 0,10";

//As vendors are selected depending on each passed xipcode they are displayed. If there are more than ten then the limit works. If there are not then the while statement reiterates and passes another zipcode. So, the page displays values for zipcodes until it the query gets one that has more than 10 associated vendors.

im not sure if i understood right, but do u mean to say that say for zip code 12345 if the query returns only 4 vendors the query fails and the next zip code is passed ???

reg
kevin

[fresca]

Kevin,
Thanks.

It's not that the query "fails", it's just that until the Limit clause is satisfied the nextpage/prevpage code is not read.

For example:

say the first zipcode passed to the second query is 10000.

In the vendors table, only 3 vendors are in this zip. The LIMIT clause is not satisfied so another zip (10001) is passed.

This zip yeilds 5 vendors, still not enough for the limit clause but nontheless I now have 8 displayed results on this page. No matter, another zip is passed.

This third zip (10002) yeilds 12 vendors. The Limit clause is satisfied. The first 10 vendors are displayed ALONG with the other 8 and the script moves on to the prev/next part.

Everything works fine except that I now have 18 records on the page instead of 10.

Hmmm.

I've tried different if loops but the offset value of LIMIT seems to be key to the whole prev/next function.

Still puzzled.
Thanks

fresca

[kevinsequeira]

it's just that until the Limit clause is satisfied the nextpage/prevpage code is not read.

where is this done ??

could u post the whole code for you page, if its too long u could upload it as a text file

reg
kevin

[fresca]

Here's the code for the prev/next part.
It seems to work fine.

One note.

The code I posted earlier did not have variables in the Limit clause for ease of explanation.

the actual clause is LIMIT $start, $full

where $start is initially 0 and $full is 9

giving me 10 records per limit.

here's the code (if this is screwy, please have at it..thanks)

if($start < 10){
$start = $start + 10;
print " <form method=\"GET\" action=\"$_SERVER['PHP_SELF']\">
<input type=\"hidden\" name=\"start\" value=\"$start\">
<input type=\"submit\" value=\"Next\">
</form>";
}else{
$back = $start - 10;
$start = $start + 10;

		print" <form method=\"GET\" action=\"$_SERVER['PHP_SELF']\"> 
		<input type=\"hidden\" name=\"start\" value=\"$start\">
		<input type=\"submit\" value=\"Next\"> 
		</form>
		<form method=\"GET\" action=\"$_SERVER['PHP_SELF']\"> 
		<input type=\"hidden\" name=\"start\" value=\"$back\">
		<input type=\"submit\" value=\"back\"> 
		</form>";

		}

thanks,

fresca

[kevinsequeira]

could you post the whole code for the page together in one post cause its easier to debug.

reg
kevin

[fresca]

Sure.

Here it is.

Thanks.

<?php

include("DistanceAssistant.inc");
include("RadiusAssistant.inc");
//Simulate data entry
$ZIPCode = "90046";
$Miles = "3.00";

//Connect to database
$dbhost = "*****"; 
$dbuser = "*****";
$dbpass = "******";
$db     = "******";
if ($start == NULL) // No address line variable 
{ 
$start = 0; // Default value 
} 
$full ="9";

$dbConn = @mysql_connect($dbhost, $dbuser, $dbpass) or die("The site database is down. Please try logging on later.");
if ($db!="" and !@mysql_select_db($db))
	die("The site database is unavailable. Please try logging on later.");

$sql = "SELECT Latitude,Longitude FROM zipCodes WHERE ZIPCode = '$ZIPCode'";
if (!$result = mysql_query($sql)) {
	//Error in mySQL query
	print "There was an error in sql statement, ".mysql_error()."<br><b>$sql</b>";
	exit;
}
elseif ($result=="") {
	//ZIP code does not exist - report to user...
	echo "ZIP code does not exist";
}
else {
	//ZIP code exists
	$myrow = mysql_fetch_array($result);
	$Latitude = $myrow["Latitude"];
	$Longitude = $myrow["Longitude"];

	$zcdRadius = new RadiusAssistant($Latitude,$Longitude,$Miles);
	$minLat = $zcdRadius->MinLatitude();
	$maxLat = $zcdRadius->MaxLatitude();
	$minLong = $zcdRadius->MinLongitude();
	$maxLong = $zcdRadius->MaxLongitude();


	$sql = "SELECT DISTINCT ZIPCode, Longitude, Latitude FROM zipCodes WHERE
		Latitude >= $minLat
		AND Latitude <= $maxLat
		AND Longitude >= $minLong
		AND Longitude <= $maxLong";

	if (!$result = mysql_query($sql)) {

		print "There was an error in sql statement, ".mysql_error()."<br><b>$sql</b>";
		exit;
	}
	else {
		$zcdDistance = new DistanceAssistant;

		while ($myrow = mysql_fetch_array($result)){
			$Distance = $zcdDistance->Calculate($Latitude,$Longitude,$myrow["Latitude"],$myrow["Longitude"]);
			   if ($Distance <= $Miles){
				//this ZIP Code is within $Miles of $ZIPCode
				//Pass zips to second query
				$zc = $myrow["ZIPCode"];

				$sql = "SELECT * FROM vendors WHERE zip_code = '$zc'limit $start, $full";

				$newresult = mysql_query($sql,$dbConn) or die ("NO WAY.");
				  	while ($newrow = mysql_fetch_array($newresult))

					{

				  	$name= $newrow["name"];
					$address = $newrow["address"];
					$phone = $newrow["phone"];
					$desc = $newrow["description"];
					$show_ven = $newrow["menu_show"];



					print "
					<html>
						<head>
						<title>Vendors</title>
						<link rel=\"stylesheet\" type=\"text/css\" href=\"Themes/Cobalt/Style.css\">
						</head><body bgcolor=\"#669966\"  >
						<div align=\"center\">
						<table width=\"500\" border=\"0\" cellpadding=\"0\ cellspacing=\"0\" class=\"CobaltResultsTABLE\">

						<tr>
						<td class=\"RestName\" width=\"50%\">$name</td>
						<td>&nbsp;</td>
						</tr>
						<tr>
						<td class=\"ResultsContent\">$address</td>
						<td class=\"DescriptionTD\" >$desc</td>
						</tr>
						<tr>
						<td class=\"ResultsContent\">$phone</td>
						<td><a href=\"$show_ven\" class=\"venLink\">view list</a></td>
						</tr>
						</table>";

					}
				}

		}	

	}	

}

print "$start ----- $full"; // Just for checking purposes

if($start < 10){
$start = $start + 10;
print " <form method=\"GET\" action=\"$_SERVER['PHP_SELF']\">
<input type=\"hidden\" name=\"start\" value=\"$start\">
<input type=\"submit\" value=\"Next\">
</form>";
}else{
$back = $start - 10;
$start = $start + 10;

		print" <form method=\"GET\" action=\"$_SERVER['PHP_SELF']\"> 
		<input type=\"hidden\" name=\"start\" value=\"$start\">
		<input type=\"submit\" value=\"Next\"> 
		</form>
		<form method=\"GET\" action=\"$_SERVER['PHP_SELF']\"> 
		<input type=\"hidden\" name=\"start\" value=\"$back\">
		<input type=\"submit\" value=\"back\"> 
		</form>";
		print "</div></body></html>";
		}

mysql_close($dbConn);
?>

[kevinsequeira]

is this page available on an extrernal server or is it on your local machine., cause i would like to take a look at the output.

another thing is you would need to pass the $ZIPCode and $Miles on the previous and next forms when you go live.

reg
kevin

[fresca]

Kevin,
Unfortunately it's only local.

But I can tell you that everything works fine.
The next and previous links work and display pages with the proper results.
The only snag is that there are more than 10 records per page.

Thanks for the help.

fresca

[fresca]

Does the sql statement return a array or hash?

And if so, is it reduced to an empty array as it passes through the WHILE statement?

thanks

[kevinsequeira]

well i tried looking over your code quite a few times but its a bit confusing for me to follow, anyway you have put the limit on the innermost query

SELECT * FROM vendors WHERE zip_code = '$zc' limit $start, $full

so if this gets 5 records the first time and 9 the next time the page will display 14 which is what its suppossed to do according to the program logic.

do you want to try to limit the total number of records displayed for all the zipcodes ???

if thats what you want to do then you have to have a counter which keeps track of the total records returned irrespective of individual records per zipcode.

hope that makes sense, is that what you are trying to do ?

Does the sql statement return a array or hash?

And if so, is it reduced to an empty array as it passes through the WHILE statement?

mysql_query() returns a resource identifier , its a resultset and u use the while loop in combination with mysql_fetch_array to iterate the resultset.

reg
kevin
reg
kevin

[fresca]

<quote>if thats what you want to do then you have to have a counter which keeps track of the total records returned irrespective of individual records per zipcode. </quote>

Kevin,
Yes that is what I'm trying to do.
I can compose a counting loop to inly show ten records and then move on. However, I don't know how to mimic the "offset" feature of the LIMIT clause.

Seems to me, once I count and display 10 records and then go to the next page, the sql will start over and return the same ten agian without the offset parameter.

I'm stumped.

Mike