CODEWITHSUNDEEP
phpmysqlsqlcodeigniter
robins
robins

Reputation: 1668

how to put limit in normal sql query

I want to put the limit 10 in this

function getArticles($search_key,$pub_status)       
{ 
    $sql="SELECT * FROM (`ms_article`) ";
    if($search_key != '')
    {
        $sql.="WHERE  (";
        $words = explode(" ",$search_key);       
        for($i=0;$i<count($words);$i++)
        {
            $title_con.="`article_title` LIKE '%".$words[$i]."%' OR";
            $abstract_con.="`article_abstract` LIKE '%".$words[$i]."%' OR";
            $keywords_con.="`article_keywords` LIKE '%".$words[$i]."%' OR"; 
        }
        $sql.=$title_con.$abstract_con.substr($keywords_con, 0, -2).") AND  (`article_status` = 1 ";   
    } else{
        $sql.="WHERE (`article_status` = 1 ";
    }            

    if($pub_status==0){
       $sql.="AND `private_status` <> 1";
    }
    $sql.=")"; 
    $result=$this->db->query($sql) ;   
    // echo[$result];
    // return $result;  
}

but I dont know limit append in that query. Where can I place this and how to echo the query in codeigniter?

Upvotes: 1

Views: 140

Answers (4)

user2439481
user2439481

Reputation:

Try this code maybe useful for you.

$sql.=")";
$sql.=" LIMIT 0,10";
echo $sql;
$result=$this->db->query($sql) ;

Upvotes: 2

Mohan
Mohan

Reputation: 4829

You can add the Limit at the end of your query like this : 

$sql.=")";
$sql.=" LIMIT 10, 0";   // added limit of 10 and offset 0 
echo $sql;              //printing generated query
$result=$this->db->query($sql) ;

In your case you are simply building your query into a string so you can echo the string to see the final query.

However when using Codeigniter's Query Builder You can check the last executed Query with : 

$this->db->last_query();

I usually put these king of function in a separate helper file which are useful for debugging any piece of code later like this :

function q($die = true){
  $ci =& get_instance();
  echo $ci->db->last_query();
  if($die) die('<br>----------Query Ends here--------<br>');
}

Upvotes: 1

lorenzobe
lorenzobe

Reputation: 74

Pulled straight from the docs: http://www.codeigniter.com/user_guide/database/examples.html#standard-query-with-single-result

But this is also dependend on which database you use. The LIMIT syntax works for example with PostgreSQL, MySQL, SQLite but sqlserver (Microsoft) it's a whole different story

Upvotes: 1

Manwal
Manwal

Reputation: 23816

Append Limit at end of query then echo it:

$sql.=")";
$sql.=" LIMIT 10";// added limit at last of query
echo $sql; //printing generated query
$result=$this->db->query($sql) ;

Upvotes: 8

Related Questions