How to sort by name in alphabets using mysql query

Question

MY albums are sorted by id , now i would like to show only album only B alphabet.

My old query

$valueql_select="SELECT DISTINCT (i.gallery_album_id) FROM mov_gallery_album AS a, mov_gallery_images AS i WHERE a.gallery_album_id = i.gallery_album_id AND a.gallery_cat_id =".$_GET['cat']." ORDER BY gallery_id desc";

I tried by using LIKE

$valueql_select="SELECT DISTINCT (i.gallery_album_id) FROM mov_gallery_album AS a, mov_gallery_images AS i WHERE a.gallery_album_id = i.gallery_album_id AND a.gallery_cat_id =".$_GET['cat']." ORDER BY name LIKE 'b%'";

But the above query doesn't work .

score 1 · Accepted Answer · edited May 23 '17 at 11:48

Put the condition in the WHERE clause:

$valueql_select="SELECT DISTINCT i.gallery_album_id
                 FROM mov_gallery_album AS a
                 JOIN mov_gallery_images AS i
                 ON a.gallery_album_id = i.gallery_album_id
                 WHERE a.gallery_cat_id = '42'
                 AND name LIKE 'b%'
                 ORDER BY name";

Also you have an SQL injection vulnerability. Do not concatenate values into your SQL string. Especially when they are directly from user input without any validation.

Related

How can I prevent SQL injection in PHP?

score 0 · Answer 2 · answered Oct 03 '12 at 08:44

If you need to sort these names which start with B first try this:

ORDER BY
CASE WHEN name name LIKE 'b%' THEN 0 ELSE 1 END, name ;

And if you want to get these names that start with B's you have to move the LIKE predicate to the WHERE clause like so:

WHERE a.gallery_album_id = i.gallery_album_id 
 AND a.gallery_cat_id =".$_GET['cat']."
 AND name LIKE 'b%'
 ORDER BY name

How to sort by name in alphabets using mysql query

2 Answers2