Pattern Matching

[westminster86]

How would I write a select statement that fetches rows from a table accordingly. For example, a user enters the letter a into an inputbox, the select statement should retrieve the names of individuals beginning with the letter a.

 

"SELECT * FROM names WHERE name LIKE '%".$searchterm."%'"

 

The above is working, but its not getting back the right values. If a name has the letter a in it, regardless of whether its the first character or not, its being displayed. E.G. Natasha is being displayed and it shouldnt because the user typed in the letter a.

 

 

 

[BlueSkyIS]

remove the first percent sign in your query. that will get only those records starting with the search term.

[westminster86]

doesnt work

[BlueSkyIS]

the SQL for finding records where the column name starts with 'a' is:

 

SELECT * FROM names WHERE name LIKE 'a%'

 

that doesn't work for you? are you getting any errors? nothing?

 

the SQL runs as described here.

[rhodesa]

doesnt work

"Doesn't work" isn't very helpful.

 

Also, to make it case insensitive and prevent SQL injection, you would want something like:

 

<?php
$search = mysql_real_escape_string(strtoupper($searchterm));
$sql = "SELECT * FROM names WHERE UPPER(name) LIKE '{$search}%'";
?>

[BlueSkyIS]

it runs case-insensitive on my MySQL installation. your mileage may vary.

 

but yes, definitely wrap up any strings you are using in a SQL statement.

[rhodesa]

it runs case-insensitive on my MySQL installation. your mileage may vary.

 

but yes, definitely wrap up any strings you are using in a SQL statement.

 

Good to know, I jump back/forth between MySQL & DB2, and I know I have had case sensitive problems with DB2.

[westminster86]

bluesky, i dnt actually know what the user is entering i was just using A as an example, if i dnt know wht the first charcter is whta do i put? an underscore?

[rhodesa]

replace

"SELECT * FROM names WHERE name LIKE '%".$searchterm."%'"

with

"SELECT * FROM names WHERE name LIKE '".mysql_real_escape_string($searchterm)."%'"

 

if that isn't working, then you will need to post more of your code

[westminster86]

ok got it to work thanks for your help