[eluser]unmillon[/eluser]
John, thanks for helping me creating the search model for my site. I love what you did because it's very accurate with the result, but the only problem is that it's super slow.
I'm going to transfer the site from a share hosting to a dedicated server and see if that improve the speed on the query.
I will include the model created by John for me, can someone take a look and tell me if i can do anything else to improve the speed. This model is really accurate as i said before.
Code:
<?php if (!defined('BASEPATH')) exit('No direct script access allowed');
class Search_model extends Model {
function Search_model()
{
parent::Model();
}
function m_filter($tmp_filter, $num, $offset, $andor='AND')
{
// following values are returned
$result['agood' ] = array();
$result['unable'] = array();
$result['found' ] = array();
if (empty($tmp_filter))
{
$awords = array('Please_try_again: and__enter_words_ separated___by__spaces');
}else{
$awords = explode(" ", trim($tmp_filter)); // TRIM IS ESSENTIAL !!!
}
// search ALL words AND if agood word add to agood ELSE add to bad
$cnt = count($awords);
for ($i2=0; $i2<$cnt; $i2++)
{
$row = $awords[$i2];
$sql = "SELECT * FROM songs WHERE title LIKE '%" .$row ."%' OR artistName LIKE '%" .$row ."%'";
$query = $this->db->query($sql);
// Results found from a single query - IF FOUND then add to agood to use in final sql statement
if ($query->num_rows() > 0)
{
$result['agood'][] = $awords[$i2];
}else{
$result['unable'][] = $awords[$i2];
} // endif
}// for (...) ============================
// the number of successful words found
$cnt = count($result['agood']);
// if we find more than one then dab word found onto the end
$sql_agood = ''; // initally empty but words added when found
for ($i2=0; $i2<$cnt; $i2++)
{
$thisword = $result['agood'][$i2];
// I DON'T THINK THE OR WILL EVER BE USED BUT IT IS HERE JUST IN CASE
if ($andor == 'OR')
{
$andor = ($i2>0) ? ' OR ' : '';
}else{
$andor = ($i2>0) ? ' AND ' : '';
}
$andor .= " ( (title LIKE '%" .$thisword ."%' OR artistName LIKE '%" .$thisword ."%'))";
// test num_rows()
$query = $this->db->query("SELECT * FROM songs WHERE " .$sql_agood .$andor);
// now test for combined words
if (($query->num_rows() > 0) AND (!empty($row)))
{
$result['found'][] = $thisword;
$sql = "SELECT * FROM songs WHERE " .$sql_agood .$andor . "LIMIT $offset, $num";
$sql_agood .= $andor; // initally empty but words added when found
}else{
$result['unable'][] = $thisword;
}
}// end for/next for each word found
// just in case the last found was FALSE
$query = $this->db->query($sql);
$cnt = $query->num_rows();
$result = $query->result(); //$sql;
return $result;
}//endfunc m_filter
} //End Class
?>
Thanks.
