SQL Query - full text - order by score.

SQL Query - full text - order by score. - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: SQL Query - full text - order by score. (/showthread.php?tid=53326)

SQL Query - full text - order by score. - El Forum - 07-19-2012

[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

SQL Query - full text - order by score. - El Forum - 07-19-2012

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

SQL Query - full text - order by score. - El Forum - 07-19-2012

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

SQL Query - full text - order by score. - El Forum - 07-19-2012

[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-guide/database/active_record.html

Search for "order_by"

SQL Query - full text - order by score. - El Forum - 07-21-2012

[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();