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?

Solution 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'.

Solution 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, '%')";

Solution 3

for second.

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

Solution 4

The text wildcard in MySQL is %, so for your first query you would probably want:

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

And for your second query:

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

View more solutions

Comments

[hkutluay]

@Thew query above only checks starting chars.

[gen_Eric]

@Thew: You mean for the starting character? Does stationname LIKE 'a%' not work?