mysql LIKE select only the first letter starts with

Question

I would like to select rows which start with the word s but I am getting other results as well. I have rows red socks, blue sweater, skirt.... in the table and when I do the select as below I end up with the above result. I am guessing this is because the red socks contain s on the second word, so how can I just select skirt?

 $pr = 's%' 

$stmt = $db->prepare("SELECT * FROM table where product like ?");
$stmt->bind_param('s', $pr);
$stmt->execute();

rows returned

  skirt
  red socks
  blue sweater

I only need skirt

Can you execute `SELECT * FROM table where product like 's%'` through `MySQL console` or `PHPMyAdmin` and post the result here. — Ravinder Reddy, May 18 '14 at 18:04
@Ravinder I only get `skirt` when I do the query on PHPMyAdmin — user2666310, May 18 '14 at 18:10
Your query is very clear and should work with `mysqli` too. `echo $pr` before bind and see if it was `'%s%'` !!! — Ravinder Reddy, May 18 '14 at 18:13
What happens if you do `SELECT product FROM table ...` instead of `SELECT * FROM table`? What columns are in `table`? — O. Jones, May 18 '14 at 20:14
Holycrap `mysqli` prepared statements are weird... After 20 mins of trial and error, it works for me. `o%` works as expected, so your method works for me. — Rudie, May 18 '14 at 23:23
@Rudie I figured out at the end I had three similar files and was trying to edit the wrong one. It worked as it was expected.Thanks — user2666310, May 20 '14 at 02:36

score 0 · Answer 1 · answered May 18 '14 at 18:00

0

SELECT * FROM table where product like 's%'

You should look at this:

http://www.tutorialspoint.com/mysql/mysql-like-clause.htm

answered May 18 '14 at 18:00

ddagsan

1,786
18
21

I am using mysqli prepared statements so this is not useful. – user2666310 May 18 '14 at 18:04

score 0 · Answer 2 · answered May 18 '14 at 18:42

0

You are missing ; after $pr = 's'. Try:

$pr = 's'; 
$stmt = $db->prepare("SELECT * FROM table where product like CONCAT(?, '%')");
$stmt->bind_param('s', $pr);
$stmt->execute();

answered May 18 '14 at 18:42

Lucas Henrique

1,380
1
11
15

mysql LIKE select only the first letter starts with

2 Answers2