[SOLVED] php mysql select

[andyd34]

I have a members list in a table and one of the fields is the members post code in format AA12, A12, AA1, A1 what I am trying to do is fetch the members within a postal area, ie A or AA

 

I have tried

 

$postal = $_GET['post_code'];
$len = strlen($postal);

if($len=='1') {
$where_clause = " WHERE left(Usr_Pcode, 1) = '$postal'";
}
elseif($len=='2') {
$where_clause = " WHERE left(Usr_Pcode, 2) = '$postal'";
}
$query = mysql_query("SELECT id FROM table" . $where_clause);

 

But in one instance its fetching one lot of results then in the other its fetch all the results as is looking for the first and second letter. Is there a way to strip the numeric values in mysql to fetch the postal area instead of the where clause

 

Thanks

[.josh]

not really familiar with that post code format, but is it letters followed by numbers?  If so, you can do something like this:

 

// assuming $_GET['post_code'] is for example A or AA etc...?
$postal = $_GET['post_code'];
if (preg_match('~^[a-z]+$~i',$postal) {
  $where_clause = "WHERE Usr_Pcode regexp '^" . $postal . "[0-9]+' = 1";
}
$query = mysql_query("SELECT id FROM table" . $where_clause);

[andyd34]

Thanks, thhat worked fine. Didn't even know you can do than in mysql. would it also work like this

 

$Scotland = array("AB","DD","DG","EH","FK","G","TD","KA","KW","KY","ML","PA","PH","IV"); 

$query = mysql_query("SELECT id FROM table WHERE pcode regexp '^" . implode(', ', $Scotland). "[0-9]+' = 1");

[.josh]

you will need to implode with | as delimiter and wrap it in parenthesis

[andyd34]

Thanks for the advise, will try it.

http://www.phpfreaks.com/forums/index.php/topic,118755.0.html

[andyd34]

Tried your suggestion about the array but its thowing up some weird results using

 

 

$Scotland = array("AB","DD","DG","EH","FK","G","TD","KA","KW","KY","ML","PA","PH","IV"); 

$place_arry = implode("' | '", $Scotland);
$where_clause = " WHERE Usr_Pcode  REGEXP '^".$place_arry."[0-9]+'  = 1";

 

the statemt is echoing as

 

WHERE Usr_Pcode REGEXP '^AB' | 'DD' | 'DG' | 'EH' | 'FK' | 'G' | 'TD' | 'KA' | 'KW' | 'KY' | 'ML' | 'PA' | 'PH' | 'IV[0-9]+' = 1

 

But its throwing up ewsults from different postcodes

[.josh]

okay that's because you are not using | as a delimiter. You are using ' | ' as a delimiter.  And you did not wrap the implode in parenthesis.

 

$Scotland = array("AB","DD","DG","EH","FK","G","TD","KA","KW","KY","ML","PA","PH","IV"); 

$place_arry = "(" . implode("|", $Scotland) . ")";
$where_clause = " WHERE Usr_Pcode  REGEXP '^".$place_arry."[0-9]+'  = 1";

[andyd34]

Thanks again, your a star