Global limit on my query

[CanMan2004]

Hi all

I have been racking my brain trying to come up with a solution and cannot, can anyone help me?

I have a business tool finder, basically someone enters a postcode and it looks in a database called "postcodes" and looks for the closest 10 postcodes to the one entered in the search box, it finds the closest by checking the x and y co-ordinates. For each postcode it returns, it looks in another database called "customers" which then returns details for that customer, it returns a max of 10 customers for each postcode.

The code I use is as follows;

[code]
<?
$sql2 = "SELECT *, SQRT(POW((x - $x),2) + POW((y - $y),2)) AS dist FROM postcodes ORDER BY dist LIMIT 10";
$show2 = @mysql_query($sql2,$connection) or die(mysql_error());
while ($rows2 = mysql_fetch_array($show2)) {
$postcode1 = $rows2['postcode'];
?>
Pharmacists in <? print $postcode1; ?><br><br>
<?
if ($countpostcode == '2' || $countpostcode == '3')
{
$sql3 = "SELECT * FROM customers WHERE `post_code` LIKE '".$postcode1." %' AND ayp = 'TRUE' ORDER BY RAND() LIMIT 10";
}
if ($countpostcode == '4')
{
$sql3 = "SELECT * FROM customers WHERE `post_code` LIKE '".$postcode1."%' AND ayp = 'TRUE' ORDER BY RAND() LIMIT 10";
}
$show3 = @mysql_query($sql3,$connection) or die(mysql_error());

while ($rows3 = mysql_fetch_array($show3))
{
?>
Customer details here<br>
<?
}
?>[/code]

At the moment, it returns 10 postcodes and then 10 customers for each postcode, so you could get up to 100 rows returned. I want to change the code so that it returns a max of 20 rows in total, so if it found 10 rows on the first postcode and then 10 for the second postcode, it would just return those 20 and no more.

Basically im trying to do a global limit, so no more than 20 rows can be returned in total.

Can anyone help me? it would be great if someone can give me a hand.

Thanks in advance

Ed

[pnj]

You can use a global limit if you use a subquery.  I don't follow what you're doing with $countpostcode, but it would look something like this:

[code]SELECT *, SQRT(POW((x-$x),2) + POW(y-$y),2)) AS dist FROM customers
  INNER JOIN postcodes ON
      customers.post_code LIKE CONCAT(postcodes.post_code, "%")
      OR customers.post_code LIKE CONCAT(postcodes.post_code, " %")
  ORDER by dist, RAND()
  LIMIT 100;[/code]

The alternative is to use mysql_num_rows($show3) to maintain a running count, which tells you how much to limit the next mysql query.

Hope this helps,
-pnj

[CanMan2004]

Hi pnj

Not sure I follow, with that I get a QUERY WAS EMPTY

Because postcodes are stored like MW1 7YH (6 characters)  or NH66 5TG (7 characters), the $countpostcode is simply for checking both these formats,  can explain more if you need

Ed

[pnj]

Sorry I'm afraid that's the limit of my competence.  Since I haven't seen the structure of your database, i.e. how you store zip codes, what is x-$x and where those come from etc., I can't say exactly why your query is returning empty.  But the idea is that to limit to 100 you need everything in one query.  Perhaps somebody more astute can see where I have gone wrong.

Do you follow the second idea about using mysql_num_rows() to limit the later queries?  Though it would be most efficient to use a single query.  Why do you need to use LIKE, can you post a sample of what your postal codes look like in each table and how you want them to match?

[CanMan2004]

The x and y is the co-ordinates of where this postcode is stored and it is what is used to work out the closest postcode area to the one searched for. They are stored in number format, for example

x - 34567443
y - 21123096

Does this help?

[pnj]

I was rather thinking of how you're matching postal codes in the customer table to the postal code table.  Maybe this seems obvious to you, but I'm Canadian. :-)

So is it like 5-digit strings in postcodes (01234), and 5 digits dash 4 digits in customers (01234-5432), and you want to match the first five digits?  Or what, how do you determine a matching or "close" postal code?  By the first four digits?

-pnj

[CanMan2004]

Postcodes in the UK can be in either 7 digit format like xxxx xxx or as 6 digit xxx xxx, I have a database which stores the first part of every postcode, plus the x and y co-ordinates for where the postcode is located, for example

postcode    x            y

H23 6YH    4342342  7867687
YH43 3TG    1324232  6564488
T65 7YH      1364356  5644567
UH56 T56    3436789  5467890

Basically when the user does a search for a postcode, it checks the first 3 or 4 digits of the postcode they searched for and looks it up in the postcode database and returns the x and y co-ordinates for that postcode, I didnt show this before, but the script is

[code]<?
$postcode1 = str_replace(" ", "", $_GET['postcode1']);

$sql1 = "SELECT * FROM postcodes WHERE `postcode` = '".$postcode1."'";
$show1 = @mysql_query($sql1,$connection) or die(mysql_error());

$num = mysql_num_rows($show1);
if ($num != 0)
{
print "";
}
else
{
print "The postcode you entered is not valid, please try again";
}


while ($rows1 = mysql_fetch_array($show1)) {

$y = $rows1['y'];
$x = $rows1['x'];
?>[/code]

My query then checks the x and y co-ordinates for that postcode and brings back 9 postcodes (using the LIMIT 10), it works out the 10 by checking the rows x and y co-ordinates and returning the closest matches to both fields, the script is

[code]<?
$sql2 = "SELECT *, SQRT(POW((x - $x),2) + POW((y - $y),2)) AS dist FROM postcodes ORDER BY dist LIMIT 10";
$show2 = @mysql_query($sql2,$connection) or die(mysql_error());
while ($rows2 = mysql_fetch_array($show2)) {
$postcode1 = $rows2['postcode'];
?>[/code]

The script then pulls in from the customers database, 10 random customers which have that postcode, this script is

[code]<?
if ($countpostcode == '2' || $countpostcode == '3')
{
$sql3 = "SELECT * FROM customers WHERE `post_code` LIKE '".$postcode1." %' AND ayp = 'TRUE' ORDER BY RAND() LIMIT 10";
}
if ($countpostcode == '4')
{
$sql3 = "SELECT * FROM customers WHERE `post_code` LIKE '".$postcode1."%' AND ayp = 'TRUE' ORDER BY RAND() LIMIT 10";
}
$show3 = @mysql_query($sql3,$connection) or die(mysql_error());

while ($rows3 = mysql_fetch_array($show3))
{
?>
Customer details here<br>
<?
}
?>[/code]

So it's returning 10 postcodes and up to 10 customers from each postcode, so this could be 100 in total, I want to show just 20 in total.

Does this help?

Ed

[pnj]

Did you get this working with 100 results?  I'd like to be sure the problem is the query and not your string formatting on your postal codes.  In what you sent, the postal code table has entries that look like "XXX XXX", which is exactly what you store in your $postcode variable:

[code]$postcode1 = $rows2['postcode'];[/code]

You then use this same value to match to the customers table:

[code]WHERE `post_code` LIKE '".$postcode1." %'[/code]

In which, it is searching for something like "T2X X4Y %", which it will never find.

Perhaps you have omitted some code, but you need to make sure $postcode1 only contains the first three letters of the postal code or your wild card match does not make sense.

[pnj]

You could perhaps modify the original JOIN code to look like the following to match only the first three letters of the code.  This is a much more elegant solution than doing all the string formatting manually:

[code]SELECT *, SQRT(POW((x-$x),2) + POW(y-$y),2)) AS dist FROM customers
  INNER JOIN postcodes ON
      SUBSTR(customers.post_code,1,3)=SUBSTR(postcodes.post_code,1,3)
  ORDER by dist, RAND()
  LIMIT 100;[/code]


Rather than trying to make all your php and sql code work together, try these queries out until you get one working in the MySQL query browser or at the mysql command prompt.  Once you have a working query, go to php and figure out how to structure it automatically.

Hope this helps

-pnj

[CanMan2004]

Hi pnj

If I query that, I get an error, I tried to run the query

[code]SELECT *, SQRT(POW((x-392900),2) + POW(y-804900),2)) AS dist FROM postcodes[/code]

within phpmyadmin and even that part returns

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') , 2 ) ) AS dist FROM postcodes
LIMIT 0, 30' at line 1

Any ideas why that is? Too low version of php maybe?

[CanMan2004]

I have managed to tweak that so it almost returns something, but I get the error

#1066 - Not unique table/alias: 'postcodes'

Can you help?

[CanMan2004]

pnj

Would it not need to be

"AS dist FROM postcodes"

as it's the postcodes table which holds the x and y co-ordinates?

[pnj]

mysql perceives the table as "customer inner join postcode", so it is not a problem selecting dist.

probably the problem is you have a 'postcode' field in two of your tables, and mysql does not know which to use.  you can specify customer.postcode or postcodes.postcode?

[CanMan2004]

Where in the query do I change that and what should it be changed to?

I say about the dist part, because you are saying to write the following

[code]SELECT *, SQRT(POW((x-$x),2) + POW(y-$y),2)) AS dist FROM customers[/code]

but the x and y co-ordinates are held in the postcodes table, not the customers table, therefore should it not be

[code]SELECT *, SQRT(POW((x-$x),2) + POW(y-$y),2)) AS dist FROM postcodes[/code]

and not

[code]SELECT *, SQRT(POW((x-$x),2) + POW(y-$y),2)) AS dist FROM customers[/code]

Does that make sense?

[pnj]

Are you running the query exactly like this?

[code]
SELECT *, SQRT(POW((x-$x),2) + POW(y-$y),2)) AS dist FROM customers
  INNER JOIN postcodes ON
      SUBSTR(customers.post_code,1,3)=SUBSTR(postcodes.post_code,1,3)
  ORDER by dist, RAND()
  LIMIT 100;
[/code]

The dist is ok, because you are drawing the query from two joined tables, and the dist field is present.  I don't understand the alias thing, unless you have an x, y or dist field in your customers table.  You could try aliasing the tables, but I'm grasping at straws now:
[code]
SELECT *, SQRT(POW((x-$x),2) + POW(y-$y),2)) AS dist FROM customers AS c
  INNER JOIN postcodes AS p ON
      SUBSTR(c.post_code,1,3)=SUBSTR(p.post_code,1,3)
  ORDER by dist, RAND()
  LIMIT 100;
[/code]

Also maybe selected one or two customer fields instead of '*'?  But I can't imagine that would change much...

Sorry I can't be of more help

[CanMan2004]

thanks, ill keep trying your code and playing around with it