matteodallombra matteodallombra - 8 months ago 35
PHP Question

Match full string with partial value in MySQL column (postcodes)

I think the solution to this problem should be easy, but I can't find a good way around it.

I'm working with UK postcodes. My reference db looks like this:

| ID | postcode | leadtime |
| 1 | AB10 | 1 |
| 2 | AB11 | 2 |
| 3 | B7 | 3 |
| 4 | SE16WD | 1 |

The value in this table only represents the first half of a full UK postcode, but that's all we need to check against.

Now, the user has a form where they can type their FULL postcode to check what the lead time will be (the full postcode is then use as part of the delivery address).

Uk postcodes can have different length and you can type them with or without spaces between the first and the second half. Right now I have some PHP code that would basically take whatever was typed by the user and try to run it through all the different layout possibility, but it doesn't always work.

I'd like to find a way to do so in MySQL. I've started playing around with

SELECT leadtime FROM myTable WHERE postcode LIKE '$postcode%'

But this only works for the first few digits. As soon as the user types the entire postcode, this query won't find anything in the table.

I cannot for the life of me, find a way to match the two values!!

I'm sure I'm missing something really basic here!

Thanks a lot for your time and help with this.

EDITED the MySQL table to better reflect the variety in reference postcodes to check against.

| user_id | user   |
|       3 | George |
|       1 | John   |
|       2 | Paul   |
|       4 | Ringo  |

SELECT * FROM user WHERE 'Ringo Starr' LIKE CONCAT(user,'%');
| user_id | user  |
|       4 | Ringo |