CODEWITHSUNDEEP
phpmysqlselectsql-like
Thew
Thew

Reputation: 15959

MySQL: LIKE and First character

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?

Upvotes: 6

Views: 24681

Answers (5)

Limitless isa
Limitless isa

Reputation: 3802

FOR Tel Number

5 finds that the first character

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

Upvotes: 0

gen_Eric
gen_Eric

Reputation: 227310

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

Upvotes: 18

Mosty Mostacho
Mosty Mostacho

Reputation: 43494

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

Upvotes: 6

Brad
Brad

Reputation: 31

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']) ."%'
    ";

Upvotes: 1

hkutluay
hkutluay

Reputation: 6944

for second.

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

Upvotes: 3

Related Questions