This went direct and not to the list. Andy
-----Original Message----- From: Andy Eastham [mailto:[EMAIL PROTECTED] Sent: 21 October 2003 08:19 To: Steven Ducat Subject: RE: SELECT 9 BETWEEN 1 AND 0 Steve, I'd add an extra column with "modified code" in it, where I subtracted 1 from the number bit in the second field, So 1 would become 0, and 0 would become 9. I'd search against that field ( modifying my search terms accordingly), and retrieve the proper value from the original column. Being from the UK and knowing how these codes work, I think I would also break the code up into four columns (eg from RH6 9XJ, ie 1 - first two letters of first field eg "RH", 2 - one or two numbers of first field eg "6", 3 - one number of second field (this is - the one I'd modify as above) eg "9" 4 - two letters from second field eg XZ Then add the extra modified field 3. I'd index all of these fields. Because you wouldn't have to perform substring searches on them, this should be a lot faster. Hope this helps, Andy > -----Original Message----- > From: Steven Ducat [mailto:[EMAIL PROTECTED] > Sent: 21 October 2003 07:13 > To: [EMAIL PROTECTED] > Subject: SELECT 9 BETWEEN 1 AND 0 > > > I am trying to create a select query to find the post town of a users > post code. I am using Royal Mails (UK) post town gazetteer. UK Postcode > (eg. RH6 9XJ). The first column contains the first half of the post code > (eg. RH6) and the next column holds the range of the second half (eg. > 2AA-6PP). > > I have some code as follows: > <?php > $pc = explode(" ","RH6 9XJ"); > > "SELECT postTown, postCode, SUBSTRING(postSector,1,3) as a, > SUBSTRING(postSector,5,3) as b, postCounty FROM postCode WHERE postCode > = \"".$pc[0]."\" AND \"".$pc[1]."\" BETWEEN SUBSTRING(postSector,1,3) > AND SUBSTRING(postSector,5,3)"; > ?> > > This will return 1 row for example if the first column was "RH6" and the > second column was "8ZZ-9ZZ". > > But in the post town list the range goes from 1 - 0 (eg. 1AA-0ZZ) so > what I find is if I use the postcode "RH6 9XJ" and a first column of RH6 > and a second column of 7AA-0BW it will not return the row as it does not > count from 1 - 0. > > The question is how can I use some sort of statement using mysql and php > to select all rows between 1 and 0 NOT 0 and 9 like the above statement > performs. > > > What I really need is some sort of function where I can set the range > that the between option sorts from. > > I have been trying to solve this for some weeks now. > > Thank You. > > Steve. > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
