Member Avatar for jtdbidirect

This is a keyword search script for one table only (pages). i need this search on 2 tables (pages and services) with one result set at the end to echo to the page. Can anyone help me? I have been trying to find a solution for 2 days - it would take too long to describe what I have already tried :) Thank you!

$todo=$_POST['todo'];
if(isset($todo) and $todo=="search"){
$search_text=$_POST['search_text'];
$type=$_POST['type'];

$bad_string = $search_text;
$bad_chars=array(',', '!', '.', 'ï','»','¿','â','¢','®');
$search_text=str_replace($bad_chars,'',$bad_string);

$search_text=ltrim($search_text);
$search_text=rtrim($search_text);

db_connect();

if($type<>"any"){
$query="select * FROM pages WHERE page_text='$search_text' ";
}else{
$kt=split(" ",$search_text);
while(list($key,$val)=each($kt)){
if($val<>" " and strlen($val) > 0){$q .= " page_text like '%$val%' or ";}

}

$q=substr($q,0,(strlen($q)-3));

$query="select * FROM pages where $q";

} 

$result=mysql_query($query);
echo mysql_error();

$num_results = mysql_num_rows($result);


   echo "<table cellspacing = 0 cellpadding = 10>";



   while($row=mysql_fetch_array($result)){

     $page_text = $row['page_text'];
     $qid = $row['qid'];
     $page_name = $row['page_name'];


     echo "<tr><td>";
     echo '* <u><a href="';
     echo $page_name;
     echo '.php">';
     echo substr($row['page_text'],0,100);
     echo '... *read more...</a></u><br><br>';
     echo "</td></tr>";
  }   
     echo "</table>";

  }
Edited by mike_2000_17 because: Fixed formatting
  • 2 Contributors
  • 2 Replies
  • 178 Views
  • 22 Hours Discussion Span
  • Latest Post Latest Post by jtdbidirect

Dani AI

Generated

For a combined search across two tables you don't need to copy rows into PHP and merge them — let the database return a single, unified result set. your approach of splitting the search string into words and building OR/LIKE terms is fine, but it can be made safer and clearer. already cleaned up formatting; below is a compact, maintainable pattern that (1) maps both tables to the same result columns, (2) uses prepared statements to avoid SQL injection, and (3) uses UNION to return one set.

// build terms
$search = trim($_POST['search_text']);
$terms = preg_split('/\s+/', $search, -1, PREG_SPLIT_NO_EMPTY);

// build WHERE fragments and bindings for both tables
$bind = [];
$wP = []; $wS = [];
foreach ($terms as $t) {
    $wP[] = "page_text LIKE ?";
    $wS[] = "service_text LIKE ?";
    $like = "%$t%";
    $bind[] = $like; $bind[] = $like; // page then service
}

$sqlP = "SELECT qid AS id, page_name AS name, page_text AS excerpt, 'pages' AS source FROM pages WHERE " . implode(' OR ', $wP);
$sqlS = "SELECT sid AS id, service_name AS name, service_text AS excerpt, 'services' AS source FROM services WHERE " . implode(' OR ', $wS);

$sql = "($sqlP) UNION ($sqlS) ORDER BY name LIMIT 100";
$stmt = $pdo->prepare($sql);
$stmt->execute($bind);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

Notes and troubleshooting

  • Make sure each SELECT returns the same number and compatible types of columns (here: id, name, excerpt, source). Adjust column names to match your services table.
  • For correctness and performance prefer PDO/mysqli with prepared statements; avoid old mysql_* functions.
  • For larger datasets use FULLTEXT indexes and MATCH...AGAINST or a search engine (Sphinx/Elasticsearch) for relevance scoring.
  • Debugging tips: echo the built SQL (with placeholders) and run each SELECT separately first; verify binding order if results look wrong; use UNION ALL if you want to preserve duplicates.

Recommended Answers

All 2 Replies

Member Avatar for chrishea

First, let's see how it looks with proper code tags.

$todo=$_POST['todo'];
if(isset($todo) and $todo=="search"){
$search_text=$_POST['search_text'];
$type=$_POST['type'];

$bad_string = $search_text;
$bad_chars=array(',', '!', '.', 'ï','»','¿','â','¢','®');
$search_text=str_replace($bad_chars,'',$bad_string);

$search_text=ltrim($search_text);
$search_text=rtrim($search_text);

db_connect();

if($type<>"any"){
$query="select * FROM pages WHERE page_text='$search_text' ";
}else{
$kt=split(" ",$search_text);
while(list($key,$val)=each($kt)){
if($val<>" " and strlen($val) > 0){$q .= " page_text like '%$val%' or ";}

}

$q=substr($q,0,(strlen($q)-3));

$query="select * FROM pages where $q";

} 

$result=mysql_query($query);
echo mysql_error();

$num_results = mysql_num_rows($result);


echo "<table cellspacing = 0 cellpadding = 10>";



while($row=mysql_fetch_array($result)){

$page_text = $row['page_text'];
$qid = $row['qid'];
$page_name = $row['page_name'];


echo "<tr><td>";
echo '* <u><a href="';
echo $page_name;
echo '.php">';
echo substr($row['page_text'],0,100);
echo '... *read more...</a></u><br><br>';
echo "</td></tr>";
} 
echo "</table>";

}
Member Avatar for jtdbidirect

Thank you, Chrishea!
I have been working on this for days. Any help would be greatly appreciated.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.