Jump to content


Photo

Setting Limit


  • Please log in to reply
24 replies to this topic

#1 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 24 September 2006 - 07:00 PM

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

#2 xyn

xyn
  • Members
  • PipPipPip
  • Advanced Member
  • 779 posts
  • LocationNorthampton

Posted 24 September 2006 - 07:09 PM

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

#3 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 24 September 2006 - 07:46 PM

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


#4 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 24 September 2006 - 07:53 PM

To give some explain my page layout

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'];

}

}

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

#5 mb81

mb81
  • Members
  • PipPipPip
  • Advanced Member
  • 120 posts

Posted 24 September 2006 - 08:13 PM

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.

#6 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 24 September 2006 - 08:21 PM

How can that be done?

#7 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 24 September 2006 - 08:33 PM

Sounds like query 2 and query 3 should be a single query, joining the 2 tables.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#8 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 24 September 2006 - 08:41 PM

Hi

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

<?
$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']; ?>
<?
}
}
}
?>

Is that of any help?

#9 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 24 September 2006 - 08:48 PM

If all you output from query 3 is the postcode, why bother doing it at all - you know the postcode from query 2
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#10 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 24 September 2006 - 08:53 PM

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

#11 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 24 September 2006 - 09:27 PM

Something like this

<?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
    }
}
?>

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#12 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 24 September 2006 - 09:38 PM

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

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

with that



#13 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 24 September 2006 - 09:40 PM

Looks like you called "post_code" in the members table
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#14 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 24 September 2006 - 09:42 PM

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?

#15 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 24 September 2006 - 09:45 PM

m.post_code.

Also change
$postcode = $rows1['postcode'];
to
$postcode = $rows1['post_code'];
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#16 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 24 September 2006 - 10:03 PM

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

#17 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 24 September 2006 - 10:11 PM

Is there a way to run

$num = mysql_num_rows($show);

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

#18 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 24 September 2006 - 10:12 PM

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.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#19 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 24 September 2006 - 10:22 PM

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

#20 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 24 September 2006 - 10:44 PM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users