MySQL: how to improve my query?

Currently I'm making my website application to validate the suburb values and ensure it exsits in the database data.

Here is my MySQL table

CREATE TABLE IF NOT EXISTS `postcodeTable` (
  `id` int(11) NOT NULL,开发者_StackOverflow社区
  `postcode` varchar(50) NOT NULL,
  `suburb` text NOT NULL,
  `state` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The user input 2 variables which are State and Suburb variable.

Here is my SQL statement to check if the row exists:

"select * from postcodeTable where state='".$state."' and suburb LIKE '%".$suburb."%'";

Here is my sample table data

Row1 id:1 postcode:3794 suburb: BANGHAM,BORDERTOWN,CANNAWIGARA,LOWAN, state: SA
Row2 id:2 postcode:6627 suburb: CANNA, state: WA

When a user input suburb CANNA and state SA it matches with the MySQL statement which is incorrect.

It matches because the LIKE statement because 'CANNA' word in CANNAWIGARA suburb name.

Which is incorrect because CANNA suburb only exist in WA state.

Is there a way to make the LIKE statement smarter which can go through the suburb string and ensure it matches the whole suburb name only?

Thanks so much in advance!

The problem here is your database schema : you should not store several suburbs in a single field of the postcodeTable table.

Instead, you should have one suburbs table, that would store one suburb per line, with a foreign key that points to the corresponding postcodeTable row.

Your postcodeTable table would be :

• id
• postcode
• state

And your suburbs table would be :

• id_suburb
• id_postcode : foreign key to postcodeTable
• name

Then, as suburbs.name would contain the exact name of one suburb, you wouldn't have to use like %...% anymore : you'd just have to use suburbs.name = '...'

And here's an idea of what your SQL query would look like :

select postcodeTable.*
from postcodeTable
    inner join suburbs on suburbs.id_postcode = postcodeTable.id
where 
    suburbs.name = 'CANNA'
    and postcodeTable.state = 'SA'

You could try regular expression matching. But I think it would be better to normalize your database. That is, split the suburbs off in a separate table, with a key pointing to the postcode table. Then you could do:

SELECT * FROM postcodeTable p
LEFT JOIN suburbTable s ON s.postcode_id = p.id
WHERE p.state = 'SA' AND s.name = 'CANNA';

Which should return zero rows.

If you need to search for exact comparisons, rather use

"select * from postcodeTable where state='".$state."' and suburb = $suburb

or drop the "%" at the back.

Have a look at the SQL Wildcards available: http://www.w3schools.com/sql/sql_wildcards.asp

If you don't want to modify your tables as people are suggesting, try:

"select * from postcodeTable where state='".$state."' and suburb LIKE '%".$suburb."%,'";

It will solve some problems, but not all of them.

Try:

$query = "select * from postcodeTable
  where state='".$state."' and suburb REGEXP '(^|,)".$suburb."(,|$)'";

But as many others did, I strongly suggest you to normalize your schema.

Thanks for the responses!

Silly me I just realised if I just add a comma to the suburb field, it would solves the problem.

Since "comma" symbol in my suburb string means end of surburb word.

Cheers!

Thanks for the quick responses!

Silly me I just realised if I just add a comma to the suburb input field, it would solve the problem.

Since "comma" symbol in my suburb string means end of surburb word.

Thanks for your advices! I would normalise my Postcode table when I get the chance.

Cheers!