To trim the fat so-to-speak in my script I decided to use 1 PDO prepare to span an array of predefined tables. The PDO executes in a while
loop and within the while
loop there is a foreach
to build the output of each result set.
This is code for a search. The script currently searches 3 tables for results (through the while
iterations). We will call them tables a, b, and c. For the tested search it finds 2 results in table a, 0 in table b, and 1 in table c.
Though it finds a total of 3 results it only displays 2. One from table 'a' and one from table 'c'. The script is not building the result from the second find in the table 'a'.
I have looked it over until my eyes bleed and searched for maybe something I have wrong, I cant figure it out. Any ideas of what is wrong with this code?
// --- Build an array of places to search
$tableArray = array("services", "webPages", "dsiu");
$tableCount = count($tableArray);
$count = "0";
$resCount = "0";
$result = "";
while ($tableCount > $count) {
// -- Search tables in the array for matches
$quotedString = $db->quote($searchString);
$qSQL = "SELECT title, ldesc, SUM(MATCH(title, sdesc, ldesc) AGAINST(:string IN BOOLEAN MODE)) AS score FROM ".$tableArray[$count]." WHERE MATCH (title, sdesc, ldesc) AGAINST (:string IN BOOLEAN MODE) ORDER BY score DESC";
$q = $db->prepare($qSQL);
$q->execute(array(':string'=>$quotedString));
// -- keep a count of the results
$rowCount = $q->rowCount();
if ($rowCount > 0) {
$resCount = $resCount + $rowCount;
// -- build result html
$html = $q->fetchAll(PDO::FETCH_ASSOC);
foreach ($html as $row) {
// --- Clean the results for display
$desc = cleanURL($row['ldesc']);
$desc = str_ireplace($searchString, '<b><font color="green">'.$searchString.'</font></b>', $desc);
$desc = substr($desc, 0, 300)."...";
$result .= "<font color='red'><b>".$row['title']."</b></font><br>".$desc."<br><br>";
}
}
$count++;
}