How to sort with a mysql column

Hi EE.

I have a query that retrieve data that has the provided word and this works well but now I want to sort this data when being retrieved based on a column name `lp` but I don't seem to be able to do so as if I add the Order by then the query seems not to return anything as the row is returned with 0. Below is the query code that I'm referring to here.
$querrry = "SELECT * FROM `xxxxdatabase`.`xxxxtable`  WHERE tags LIKE '%$searchq%' OR name LIKE '%$searchq%' OR category LIKE '%$searchq%' OR username LIKE '%$searchq%' OR addr2 LIKE '%$searchq%' OR city LIKE '%$searchq%' OR province LIKE '%$searchq' OR country LIKE '%$searchq%' OR company LIKE '%$searchq%' LIMIT 0,10 ORDER BY lp ASC " or die(mysqli_error());
$resultz = mysqli_query($conn, $querrry);

Open in new window


I'm using Mysqli.
LVL 2
Mlungisi NdlelaFounder of MCSIDevelopers also a software developerAsked:
Who is Participating?
 
Chris StanyonConnect With a Mentor Commented:
The 0 value indicates a failure (false) and not zero records. You have an error in your SQL. You need the ORDER BY before the LIMIT:

SELECT * FROM `xxxxdatabase`.`xxxxtable`  WHERE tags LIKE '%$searchq%' OR name LIKE '%$searchq%' OR category LIKE '%$searchq%' OR username LIKE '%$searchq%' OR addr2 LIKE '%$searchq%' OR city LIKE '%$searchq%' OR province LIKE '%$searchq' OR country LIKE '%$searchq%' OR company LIKE '%$searchq%' ORDER BY lp ASC LIMIT 0,10

Open in new window

2
 
Peter SchultzSenior Network AdministratorCommented:
hmm is that field Ip numeric ? Then try IsNum ... ORDER BY lp IsNum " or ....
0
 
Mlungisi NdlelaFounder of MCSIDevelopers also a software developerAuthor Commented:
I've just tried that but still the row is returned with 0 which means there was no search.
0
 
Dave BaldwinFixer of ProblemsCommented:
Chris is right, MySQL is very picky about the order of the statements in a query.
0
 
NerdsOfTechConnect With a Mentor Technology ScientistCommented:
LIMIT should be last in the sequence,

Also you were missing a % in the province LIKE

$querrry = "SELECT * FROM `xxxxdatabase`.`xxxxtable`  WHERE tags LIKE '%$searchq%' OR name LIKE '%$searchq%' OR category LIKE '%$searchq%' OR username LIKE '%$searchq%' OR addr2 LIKE '%$searchq%' OR city LIKE '%$searchq%' OR province LIKE '%$searchq%' OR country LIKE '%$searchq%' OR company LIKE '%$searchq%' ORDER BY lp ASC LIMIT 0,10" or die(mysqli_error());
$resultz = mysqli_query($conn, $querrry);

Open in new window


Last but not least, make sure $searchq is 'sanitized' to prevent an SQL injection attack. Specifically, because you are using LIKE you will want to filter the input beyond real_escape_string() and remove % and _ characters as well.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.