SQL Query - full text - order by score.

[eluser]Unknown[/eluser]
I am moving my plain old php site to CI. How can I replicate this SQL within a CI query?

Code:

$sql = "SELECT *, MATCH(title,caption,locName,name,text,keywords) AGAINST ('%" . $q . "%' IN BOOLEAN MODE) AS score
  FROM `dartmoorArchive`.`mainArchive`
  WHERE MATCH(title,caption,locName,name,text,keywords) AGAINST ('%" . $q . "%' IN BOOLEAN MODE)
  ORDER BY score DESC

[eluser]solid9[/eluser]
You use "Active Records" for this.
Everything is in the docs.

[eluser]Unknown[/eluser]
Hi there, I am aware that I need Active Records but I cannot see reference to ordering by score.

[eluser]boltsabre[/eluser]
Get familiar with the User Documentation, it's fantastic and should become your best friend.

To get you started:
http://ellislab.com/codeigniter/user-gui...ecord.html

Search for "order_by"

[eluser]rwestergren[/eluser]
Active record is great for simple queries, but for more advanced ones like this, I'd run the query manually; especially since you already have it written, there's no need to rewrite it with active record.

Also, there's no need to concatenate the string when using double quotes.

Code:

$sql = "SELECT *, MATCH(title,caption,locName,name,text,keywords) AGAINST ('%$q%' IN BOOLEAN MODE) AS score
  FROM `dartmoorArchive`.`mainArchive`
  WHERE MATCH(title,caption,locName,name,text,keywords) AGAINST ('%$q%' IN BOOLEAN MODE)
  ORDER BY score DESC"

$results = $this->db->query($sql)->result_array();

Query binding may be safer, since it will automatically escape the values.

Code:

$sql = "SELECT *, MATCH(title,caption,locName,name,text,keywords) AGAINST ('%?%' IN BOOLEAN MODE) AS score
  FROM `dartmoorArchive`.`mainArchive`
  WHERE MATCH(title,caption,locName,name,text,keywords) AGAINST ('%?%' IN BOOLEAN MODE)
  ORDER BY score DESC"

$results = $this->db->query($sql, array($q, $q)->result_array();