Clever Geek Handbook

MySQL: LIKE and first character

Hello Stackoverflow again! 2 MySQL questions.

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

First, here's the first request. The URL contains a parameter set $_GET['letter']

containing an alphabetic character. I am trying to select all lines where it stationname

starts with $_GET['letter']

. So I found this solution on another Stackoverflow thread, but it doesn't seem to work, I am getting all my lines, not just one. edit : it looks like it checks for all characters in stationname

and not just the start letter, how can I get that?

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

Second and last question. I want to create a search engine for my site by selecting all the rows where it stationname

contains $_POST['search']

. But when I have 2 rows, one of which is called cheese

and the other is cheese2

, and I search cheese

, only gets selected cheese

, and when I search cheese2

, only cheese2

gets selected. Is there a way to choose both cheese

and cheese2

?

+3


source to share


5 answers


LIKE

supports wildcards. %

means any number of characters (including zero), and _

means any character

stationname LIKE 'cheese%'

This will match cheese

and cheese2

.



You can use %

for the first problem.

stationname LIKE 'a%'

This will find all words starting with 'a'

.

+11


source


I am trying to select all lines where the station name starts with $ _GET ['letter']

MySQL has a feature LEFT

which seems to be what you are looking for. So basically, we extract the first letter stationname

and compare it to your letter:

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


Is there a way to choose cheese and cheese2?

Well, the solution here is a little smelly, as you have to check if is contained cheese

in cheese2

as well as if is contained cheese2

in cheese

. Try the following:

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


source


for the second.

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


source


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

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

And for your second request:

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


source


FOR TELECOMMUNICATIONS

5 detects that the first character

$Sql="SELECT * FROM customers WHERE TEL REGEXP '^[5]' LIMIT 0,500";
0


source






More articles:


All Articles