CODEWITHSUNDEEP
phpmysql
Alaa Gamal
Alaa Gamal

Reputation: 1135

sort mysql results

Hello i want improve my search page

i am using this query to search in posts by post title or post content

SELECT * FROM posts 
WHERE post_title rlike $this->secure($_GET['p_title']) 
  OR post_content rlike $this->secure($_GET['p_title']);

i want sort posts

i want to print posts that i got by post_title first

and then print the posts that i got by post_content

should i use two querys to sort them? and if so. How to merge two mysql querys?

Upvotes: 0

Views: 83

Answers (4)

SteeveDroz
SteeveDroz

Reputation: 6156

You can sort arrays using PHP with usort (user sort) which can sort an array with a given comparison function. In order to do that, you first have to create a function that can compare two posts according to their titles and another one that can compare their contents.

You can alternatively use SQL to sort it in the query with ORDER BY, it's simple to code and also faster when loading the web page. The number 1 rule when using PHP/MySQL is: what can be done by MySQL should be done by MySQL.

To answer your last question: don't merge your queries, they'll be mixed up and you will then have to separate them with PHP. Two queries aren't that bad, you know!

Upvotes: 0

Sirko
Sirko

Reputation: 74106

You can merge the results of two queries using the UNION operator (Docu link) like the following:

( SELECT 0 AS toBeOrdered, * FROM posts 
  WHERE post_title rlike $this->secure($_GET['p_title'])  )
UNION
( SELECT 1 AS toBeOrdered, * FROM posts 
  WHERE post_content rlike $this->secure($_GET['p_title']) )
ORDER BY toBeOrdered ASC

I inserted a column toBeOrdered to reflect your desired sorting.

Upvotes: 2

Mike Mackintosh
Mike Mackintosh

Reputation: 14245

You can sort multiple columns with the following syntax:

SELECT * FROM posts 
WHERE post_title rlike $this->secure($_GET['p_title']) 
OR post_content rlike $this->secure($_GET['p_title'])
ORDER BY post_title ASC, post_content DESC

Of course, adjust ASC and DESC accordingly

Upvotes: 0

Bono
Bono

Reputation: 4869

try using the ORDER BY statement

Upvotes: 0

Related Questions