Union of two queries for one table

[eluser]crimsun[/eluser]
Hello All,

I need help in mysql related queries Actully i have a 10 record in database table and while use like and search with some keyword then it matches with only 2 records and output with two records only but i want to display all records.....mean search matching record at the start and other after that so how this is possible in one query please suggest me solution

Thanks

[eluser]toopay[/eluser]
If you use 'LIKE' statement, thats mean your database will only returns match result. You can't get "searched result" and "all result" in a single query. Your option is either, (1)execute two queries or (2)select all of your record, unpack it, grouping it into two different array in your model, pack it again, then send it to your controller.

[eluser]oppenheimer[/eluser]
I suggest query the DB for all records and put the result into an array. Then do a search on the array to highlight the two records you want (adding a new "column" that can flag these two records would probably work).

[eluser]Madoc[/eluser]
You could use something like:

Code:

SELECT * FROM table
where field like '%search%'
UNION
select * from table
where field not like '%search%'

This will return what you need I think, the matched rows before the unmatched ones.

[eluser]danmontgomery[/eluser]
If you're using MATCH AGAINST, you can sort by the score.

Code:

$this->db->select('*, MATCH(field_a) AGAINST("'.$keyword.'") AS score', FALSE)
    ->order_by('score','desc')
    ->get('table');

Requires full text indexing, myisam, etc.

http://dev.mysql.com/doc/refman/5.5/en/f...earch.html

[eluser]toopay[/eluser]
@noctrum ;-)

[eluser]crimsun[/eluser]
Hi friends,

Thanks for your reply i resolved this problem by using the two separate quires and make them two separate array and merge them thanks for your help..but now i face some other problem as i use the group by clause for title so that if there are 3 same title its show only one time i need that its correct but if suppose you comment on the title so the order by date will display the latest comment first..........but its not work it still show me old one title first then other....

so please help me for this

[eluser]toopay[/eluser]
show your query.

[eluser]crimsun[/eluser]

Code:

//Check For Conditions
                if(is_array($conditions) and count($conditions)>0)
                        $this->db->where($conditions);
                //Check For like statement
                if(is_array($like) and count($like)>0)        
                        $this->db->like($like);    
                //Check For like statement
                if(is_array($not_like) and count($not_like)>0)        
                        $this->db->not_like($not_like);    
                //Check For Limit    
                if($limit){
                        $this->db->limit($limit, $start);
                }
                //Check for Order by
                if(is_array($orderby) and count($orderby)>0)
                        $this->db->order_by($orderby[0], $orderby[1]);
                
            $this->db->order_by('created','DESC');
//             $this->db->order_by('tbl_shouts.s_id','ASC');
            // check for group by
                if($groupby !='')
            {
                //$this->db->distinct($groupby);
                $this->db->group_by($groupby);
            }
                $this->db->from('tbl_shouts');
                $this->db->join('users', 'users.id = tbl_shouts.own_id','left');
                if($fields!='')
                        $this->db->select($fields);
                else
                $this->db->select('s_id,);
                        $result = $this->db->get();
                        return $result;

[eluser]toopay[/eluser]
Now you should show your tables structure related with above query (tbl_shouts,users) too.