MySQL: LIKE and First character

Question

I have two MySQL questions.

$query = "  SELECT
                stationname
            FROM
                stations
            WHERE
                stationname >= '". mysql_real_escape_string($_GET['letter']) ."' 
            ORDER BY
                stationname

";

Here is the first query. In the URL is a parameter set $_GET['letter'] containing an Alphabetic character. I'm trying to select all the rows where stationname starts with $_GET['letter']. So i found this solution in an other Stackoverflow topic, but it doesn't seem to work, i get all my rows, and not just that single one. edit : seems it checks for all the characters in stationname, and not just the starting letter, how can i get that?

$query = "  SELECT
                stationname
            FROM
                stations
            WHERE
                stationname 
            LIKE
                    '". mysql_real_escape_string($_POST['search']) ."'
";

Second and final question. I want to make a search engine for my website, selecting all the rows where stationname contains $_POST['search']. But when i have 2 rows, one for example called cheese and the other one called cheese2, and i search for cheese, only cheese get selected, and when i search for cheese2, only cheese2 will get selected. Is there any way to select both cheese and cheese2?

Answer 1

LIKE supports wildcards. % means any number of characters (including zero), and _ means any one character`

stationname LIKE 'cheese%'

This would match cheese and cheese2.

You can use the % for the first issue too.

stationname LIKE 'a%'

This will find all words that start with 'a'.

Answer 2

I'm trying to select all the rows where stationname starts with $_GET['letter']

MySQL has a LEFT function which seems to be what you're looking for. So basically we extract the first letter of the stationname and compare it agains your letter:

 where left(stationname, 1) = '" . mysql_real_escape_string($_GET['letter']) . "'";

Is there any way to select both cheese and cheese2?

Well here the solution is a little smelly, as you should check whether cheese is contained in cheese2 and also whether cheese2 is contained in cheese. Try this:

where stationname like '%" . mysql_real_escape_string($_POST['search']) .
  "%' OR '" . mysql_real_escape_string($_POST['search']) .
  "' like concat('%', stationname, '%')";