Setting Limit

[CanMan2004]

Hi all

I wonder if someone can help me.

Basically I have a sql table of "postcodes" and a php search page which has a input form, you can then search for a postcode, it will then return one postcode and information about the location of that postcode.

What I do then is to perform a 2nd query which takes the details of the location of the postcode (which was searched) and returns 10 postcodes which are closest to it.

I then have another database of department names, within this database there is loads of rows which have a department name and a postcode area assigned to them. What I do next is to put a 3rd query in the between the 2nd query, what this does is returns all departments that match that postcode for each of the 10 postcodes in the 2nd query.

Basically you end up with how ever many departments are stored in 10 different postcode areas.

What I want to do is to put a limit on how many department names are found in the last query, and set this to 20, so that it only shows the first 20 departments, I would then remove the limit on the 2nd query, so that it looks through the database at unlimited postcode areas until 20 departments have been found, even if 20 departments are found in one postcode area. The reason im having a problem, is that

1: I cant set a limit on the 1st query which provides the details of the location of the postcode seached, as it wouldnt make any sense to.

2: Although I can set a limit on the 2nd query, it doesnt stop more than 20 results from being returned on the 3rd query.

3: I can set a limit on the 3rd query, but the 3rd query is repeated for each of the 10 postcode areas found, so it might put a limit for each query repeated, but it wont do one globally.

Does that make sense?

Any help would be great

Thanks in advance

Ed

[xyn]

tried LIMIT 10;
ie:
"SELECT * FROM table WHERE NAME = 'a%' LIMIT 20";

[CanMan2004]

Hi

Yes I have put a limit, but as the 2nd query produces 10 results each time and then each of the 10 results performs another query then there is no way to control the overall limit

[CanMan2004]

To give some explain my page layout

[code]QUERY1 FROM postcode DB WHERE postcode = $_GET['POSTCODE'];

{

$location =  $rows['location'];

}

QUERY2 FROM postcode DB WHERE location = $location;

{

$postcode=  $rows['postcode'];

QUERY3 FROM customersdetails DB WHERE postcode = $_GET['POSTCODE'];

{

$postcode =  $rows['location'];

}

}[/code]

So I want to put a limit on the query 3, so no more than 10 results are found overall

[mb81]

Just set an incrementer variable for the overall number, that's easy enough. Every time you output the departments, increment the value and make sure you're checks include an operation that deals with that number.

[CanMan2004]

How can that be done?

[Barand]

Sounds like query 2 and query 3 should be a single query, joining the 2 tables.

[CanMan2004]

Hi

Ive nevered worked with the join funtion so would find it hard to get that working, my full code is

[code]<?
$postcode = $_GET['postcode1'];

$sql = "SELECT * FROM postcodes WHERE `postcode` = '".$postcode."'";
$show = @mysql_query($sql,$connection) or die(mysql_error());
$num = mysql_num_rows($show);
$total = 10 - $num;

while ($rows = mysql_fetch_array($show)) {

$y = $rows['y'];
$x = $rows['x'];

$sql1 = "SELECT *, SQRT(POW((x - $x),2) + POW((y - $y),2)) AS dist FROM postcodes ORDER BY dist LIMIT 10";
$show1 = @mysql_query($sql1,$connection) or die(mysql_error());
while ($rows1 = mysql_fetch_array($show1)) {
$postcode = $rows1['postcode'];
?>
<?
$sql2 = "SELECT * FROM members WHERE `post_code` LIKE '%".$postcode."%' ORDER BY post_code ASC";
$show2 = @mysql_query($sql2,$connection) or die(mysql_error());
$num2 = mysql_num_rows($show2);

while ($rows2 = mysql_fetch_array($show2)) {
?>
<?

?>
<? print $rows2['post_code']; ?>
<?
}
}
}
?>[/code]

Is that of any help?

[Barand]

If all you output from query 3 is the postcode, why bother doing it at all - you know the postcode from query 2

[CanMan2004]

because query 3 takes the postcode returned and gets all the members details from another table that have that postcode in their row

[Barand]

Something like this

[code]
<?php
$postcode = $_GET['postcode1'];

$sql = "SELECT * FROM postcodes WHERE `postcode` = '".$postcode."'";
$show = @mysql_query($sql,$connection) or die(mysql_error());
$num = mysql_num_rows($show);
$total = 10 - $num;

while ($rows = mysql_fetch_array($show)) {

    $y = $rows['y'];
    $x = $rows['x'];

    $sql1 = "SELECT m.*, SQRT(POW((p.x - $x),2) + POW((p.y - $y),2)) AS dist
            FROM postcodes p
            INNER JOIN members m ON m.postcode = p.postcode
            ORDER BY dist LIMIT 10";
    $show1 = @mysql_query($sql1,$connection) or die(mysql_error());
    while ($rows1 = mysql_fetch_array($show1)) {
        $postcode = $rows1['postcode'];
        $member_id = $rows['member_id'];
        // get other member data here
    }
}
?>[/code]

[CanMan2004]

Thanks for that, but when I run it, I get a

Unknown column 'm.postcode' in 'on clause'

with that

[Barand]

Looks like you called "post_code" in the members table

[CanMan2004]

in the table `members` the postcode field is called post_code, therefore which part of

members m ON m.postcode = p.postcode

needs to be changed to post_code and is that the only field name change that needs to be done?

[Barand]

m.post_code.

Also change
$postcode = $rows1['postcode'];
to
$postcode = $rows1['post_code'];

[CanMan2004]

that seems to return postcodes in the wrong order, the query I had done before was working it out correct, but for some reason, the one you provided, returns them incorrect

anymore help would be greatful

[CanMan2004]

Is there a way to run

[code]$num = mysql_num_rows($show);[/code]

and keep adding the total the is returned for each query done

[Barand]

Previously it would list the closest 10 postcodes even if they had no members.

This should list the closest 10 that have a member matching the postcode.

[CanMan2004]

Doesnt seem to be doing it correct, for example, there are 24 rows located in AB10 but when the postcode AB10 is searched, it doesnt return even one AB10 result, and instead it returns

DD8
PH10
IV1
G1
PA1
NE23

and so on, where it should have returned 10 results based in AB10

[CanMan2004]

My query returns AB10 as

AB10
AB10
AB10
AB10
AB10

when I run your query, I get

DD8
PH10
IV1
G1
PA1

Any idea?

Many thanks

[Barand]

No. I'm flying blind as I don't have any data to test on.

[Barand]

Are there any members in AB10?

[CanMan2004]

a sample of my postcodes table is

postcode  district  x  y  latitude  longitude
MK46 Milton Keynes 489500 251500 52.15 -0.69
MK5 Milton Keynes 483500 236500 52.02 -0.78
MK6 Milton Keynes 486300 237600 52.03 -0.74
MK7 Milton Keynes 489900 236500 52.01 -0.69
MK8 Milton Keynes 482100 238200 52.03 -0.8

and a sample of my members table is

id,name,postcode
34,fg hills,MK6 5TT
35,b w,MK6344
36,t h,MK8 2EE

so ive been working out the closest postcodes using the x and y fields in postcode and then returning the members details by matching the first part of the closest matching postcodes

does this help?

[CanMan2004]

Yes, there are about 26 matching AB10

[Barand]

Looking at your data the idea of joining the tables on postcode is a non-runner - codes in postcode table do not match those in members. The only way it would work is the postcode halves were held in 2 columns in the members table