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?

Answers


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.


try using the ORDER BY statement


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


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!


Need Your Help

Intellij Idea 13 can't find Hierarchy Tools when programing Android apps

android intellij-idea hierarchy

I try to find in View/Tools windows and also the right part of Intellij,but can not find as Intellij idea12.And I really want to use the tools for my Android Apps.

Paging: Basic, Hierarchical, Hashed, and Inverted

operating-system hashtable paging page-tables

With respect to operating systems and page tables, it seems there are 4 general methods to paging and page tables