Paginating using multiple record sets

[kidsleep]

I'm using the following code:

// break search terms into array
$terms=explode(" ",$_POST['term']);
$count=0;

foreach($terms as $key=>$val)   {
$query="SELECT * FROM table WHERE field like '%$val%'";

$result[$count]=mysql_query($query) or die(mysql_error());

$count++;

}

Is there anyway to paginate multiple results like I have here? Is there any better way to do the above code? Perhaps combining result sets or perhaps a SQL version of in_array()?

TIA

[tsinka]

Hi,

if you want to just get the total number of records containing any of the terms, you could do this:

// break search terms into array 
$terms=explode(" ",$_POST['term']); 

$where = "";

for ($cnt=0;$cnt<count($terms);$cnt++) {
  if ($cnt>0)
    $where .= " OR ";
  $where .= " field LIKE '%".mysql_escape_string($terms[$cnt])."%' ";
}

$count=0; 

$query = "SELECT COUNT(primkey) AS cnt FROM table WHERE $where";

$result=mysql_query($query) or die(mysql_error()); 

$arrRes = mysql_fetch_array($result);

$count = $arrRes['cnt'];

I don't know which way is faster, but at all the query might become very slow in both cases because of the %term%. MySQL will not use any indexes in that case.

Thomas