Jump to content

Setting Limit


CanMan2004

Recommended Posts

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
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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?
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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?
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.