CanMan2004 Posted September 24, 2006 Share Posted September 24, 2006 Hi allI 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 that1: 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 greatThanks in advanceEd Quote Link to comment Share on other sites More sharing options...
xyn Posted September 24, 2006 Share Posted September 24, 2006 tried LIMIT 10;ie:"SELECT * FROM table WHERE NAME = 'a%' LIMIT 20"; Quote Link to comment Share on other sites More sharing options...
CanMan2004 Posted September 24, 2006 Author Share Posted September 24, 2006 HiYes 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 Quote Link to comment Share on other sites More sharing options...
CanMan2004 Posted September 24, 2006 Author Share Posted September 24, 2006 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 Quote Link to comment Share on other sites More sharing options...
mb81 Posted September 24, 2006 Share Posted September 24, 2006 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. Quote Link to comment Share on other sites More sharing options...
CanMan2004 Posted September 24, 2006 Author Share Posted September 24, 2006 How can that be done? Quote Link to comment Share on other sites More sharing options...
Barand Posted September 24, 2006 Share Posted September 24, 2006 Sounds like query 2 and query 3 should be a single query, joining the 2 tables. Quote Link to comment Share on other sites More sharing options...
CanMan2004 Posted September 24, 2006 Author Share Posted September 24, 2006 HiIve 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? Quote Link to comment Share on other sites More sharing options...
Barand Posted September 24, 2006 Share Posted September 24, 2006 If all you output from query 3 is the postcode, why bother doing it at all - you know the postcode from query 2 Quote Link to comment Share on other sites More sharing options...
CanMan2004 Posted September 24, 2006 Author Share Posted September 24, 2006 because query 3 takes the postcode returned and gets all the members details from another table that have that postcode in their row Quote Link to comment Share on other sites More sharing options...
Barand Posted September 24, 2006 Share Posted September 24, 2006 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] Quote Link to comment Share on other sites More sharing options...
CanMan2004 Posted September 24, 2006 Author Share Posted September 24, 2006 Thanks for that, but when I run it, I get a Unknown column 'm.postcode' in 'on clause'with that Quote Link to comment Share on other sites More sharing options...
Barand Posted September 24, 2006 Share Posted September 24, 2006 Looks like you called "post_code" in the members table Quote Link to comment Share on other sites More sharing options...
CanMan2004 Posted September 24, 2006 Author Share Posted September 24, 2006 in the table `members` the postcode field is called post_code, therefore which part of members m ON m.postcode = p.postcodeneeds to be changed to post_code and is that the only field name change that needs to be done? Quote Link to comment Share on other sites More sharing options...
Barand Posted September 24, 2006 Share Posted September 24, 2006 m.post_code.Also change$postcode = $rows1['postcode'];to$postcode = $rows1['post_code']; Quote Link to comment Share on other sites More sharing options...
CanMan2004 Posted September 24, 2006 Author Share Posted September 24, 2006 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 incorrectanymore help would be greatful Quote Link to comment Share on other sites More sharing options...
CanMan2004 Posted September 24, 2006 Author Share Posted September 24, 2006 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted September 24, 2006 Share Posted September 24, 2006 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. Quote Link to comment Share on other sites More sharing options...
CanMan2004 Posted September 24, 2006 Author Share Posted September 24, 2006 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 returnsDD8PH10IV1G1PA1NE23and so on, where it should have returned 10 results based in AB10 Quote Link to comment Share on other sites More sharing options...
CanMan2004 Posted September 24, 2006 Author Share Posted September 24, 2006 My query returns AB10 asAB10AB10AB10AB10AB10when I run your query, I getDD8PH10IV1G1PA1Any idea?Many thanks Quote Link to comment Share on other sites More sharing options...
Barand Posted September 24, 2006 Share Posted September 24, 2006 No. I'm flying blind as I don't have any data to test on. Quote Link to comment Share on other sites More sharing options...
Barand Posted September 24, 2006 Share Posted September 24, 2006 Are there any members in AB10? Quote Link to comment Share on other sites More sharing options...
CanMan2004 Posted September 24, 2006 Author Share Posted September 24, 2006 a sample of my postcodes table ispostcode district x y latitude longitudeMK46 Milton Keynes 489500 251500 52.15 -0.69MK5 Milton Keynes 483500 236500 52.02 -0.78MK6 Milton Keynes 486300 237600 52.03 -0.74MK7 Milton Keynes 489900 236500 52.01 -0.69MK8 Milton Keynes 482100 238200 52.03 -0.8and a sample of my members table isid,name,postcode34,fg hills,MK6 5TT35,b w,MK634436,t h,MK8 2EEso 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 postcodesdoes this help? Quote Link to comment Share on other sites More sharing options...
CanMan2004 Posted September 24, 2006 Author Share Posted September 24, 2006 Yes, there are about 26 matching AB10 Quote Link to comment Share on other sites More sharing options...
Barand Posted September 24, 2006 Share Posted September 24, 2006 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.