sql select using substring

[mackin]

I have an array of the outcodes of UK postcodes - so LA12, LA13 etc.....

I also have a table of businesses, one of the columns being est_postcode - which has full UK postcodes - i.e - LA13 9UT etc.

 

so the variable $matches  is created from the data stored in $postcode_radius - which works fine. (OUTCODES)

 

I then want to use the array to SELECT matching Postcodes from the businesses - but need to trim the last 3 characters from the est_postcode - i used the code below but nothing happens - i feel that the SUBSTRING part isnt correct - any advice??

 

 

$matches = implode(', ', array_unique($postcode_radius));

//echo $matches; // just checking that the variable looks right


$sql2 = "SELECT * FROM businesses WHERE SUBSTRING(est_postcode , -3) = '".$matches."'";

$result2 = mysql_query($sql2) or die('query failed: ' . mysql_error());

    while ($row2 = mysql_fetch_array($result2, MYSQL_ASSOC))
    { 

  echo $row2["est_postcode"], $row2["est_landline"];
  	
    } 

[Pikachu2000]

You're saying you need to match the first three characters, right? Why are you implode()in an array to the $matches variable? What is the output of echo $matches?

[mackin]

the output in this example is LA12, LA13, LA14, LA16

 

no, its not the first 3 characters, because the outcodes can be LA1 or LA12 - i.e. 3 or 4 characters, so the only constant is to remove the last 3 - leaving 3 or 4.

 

I  used explode as it was the only way i could find to use the array in the SELECT statement.

[Pikachu2000]

So what you really need is everything before the space, right? For that, you'll want to use SUBSTRING_INDEX(). You also will need to use IN() as the matching criteria in the query string, after imploding the array with single quotes and commas to separate them.

 

implode("', '", $matches);

//and for the query
SELECT . . . WHERE IN( '$matches' )

[mackin]

OK i cant get that to work - and to be honest I am sure there is a simpler solution - so let me put it this way.

 

I have the array

 array_unique($postcode_radius)

- which I use the unique as some of the Outcodes repeat themselves and it isn't necessary (LA14, LA14, LA13).

I use implode to get the contents of the array for the SELECT statement.

$matches = implode(array_unique($postcode_radius));

- there is the first issue - the correct use of "', '", in the explode function to create the ('LA12', 'LA17', 'LA15', 'LA16') for SELECT - at the moment the echo  gives LA14LA13LA12LA15LA16LA18.

 

NEXT

The column the SELECT will be searching is called est_postcode which contains full postcodes (LA13 9UT) - so I need the SELECT to search  est_postcode using perhaps LIKE $matches% - so it will take the LA14 part and what comes after doesnt matter?  How can I create the full SELECT statement to return all the postcodes that contain  the OUTCODES from $matches as the start of the full postcodes contained in est_postcode

 

I am sure that I made this sound more complicated than it is....... any ideas???

 

 

 

[fenway]

I'll second that substring_index() is the way to go.

[mackin]

$matches = implode(array_unique($postcode_radius));

echo $matches;


$sql2 = "SELECT * FROM hotels WHERE (SUBSTRING_INDEX(est_postcode, ' ', 4)) LIKE $matches";

$result2 = mysql_query($sql2) or die('query failed: ' . mysql_error());

    while ($row = mysql_fetch_array($result2, MYSQL_ASSOC))
    { 

  echo $row["est_postcode"] ."<br>". $row["est_landline"];
  	
    } 
var_dump($result2);

 

provides LA14LA13LA12LA15LA16LA18query failed: Unknown column 'LA14LA13LA12LA15LA16LA18' in 'where clause'

[Pikachu2000]

You're not using IN() and didn't implode the array like I showed you, with "', '" as the glue. It looks like you got the SUBSTRING_INDEX() part figured out though.

[mackin]

I had tried variations as i couldnt get it to work with IN() - if i use this code

 

$matches = implode("', '", array_unique($postcode_radius));

echo $matches;


$sql2 = "SELECT * FROM hotels WHERE (SUBSTRING_INDEX(est_postcode, ' ', 4)) IN ('$matches')";

$result2 = mysql_query($sql2) or die('query failed: ' . mysql_error());

    while ($row = mysql_fetch_array($result2, MYSQL_ASSOC))
    { 

  echo $row["est_postcode"] ."<br>". $row["est_landline"];
  	
    } 

 

it echo's

LA14', 'LA13', 'LA12', 'LA15', 'LA16', 'LA18 query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '', 'LA13', 'LA12', 'LA15', 'LA16', 'LA18)' at line 1

 

 

 

[Pikachu2000]

Your SUBSTRING_INDEX() function should use 1 rather than 4, since you want everything before the 1st occurrence of the [space], not the 4th occurrence of it. Although it contradicts the code you've posted, it appears that the syntax error is being caused by a missing closing single quote before the last parenthesis in the IN().

[mackin]

$matches = implode("', '", array_unique($postcode_radius));

echo $matches;


$sql2 = "SELECT * FROM hotels WHERE (SUBSTRING_INDEX(est_postcode, ' ', 1)) IN ('$matches')";

$result2 = mysql_query($sql2) or die('query failed: ' . mysql_error());

    while ($row = mysql_fetch_array($result2, MYSQL_ASSOC))
    { 

  echo $row["est_postcode"] ."<br>". $row["est_landline"];
  	
    } 

 

works like a charm!!! thx very much

[Pikachu2000]

Excellent. See, it wasn't all that hard, was it?

[mackin]

hahah no - not THAT hard!