Search Box to search mysql table fulltext php

PHP
1

I am new to using fulltext search and union for mysql and php. I want to check if the results found came from the advert table and if so fetch results else from members table. it doesnt seem to be working for me because im getting no results even though i have the them in my database tables. can anyone help me please. Im stuck? here is my code i have done so far.


<?php
error_reporting(E_ALL);
ini_set('display_errors', '1');
$search_output = "";
if(isset($_POST['searchquery']) && $_POST['searchquery'] != ""){
	$searchquery = preg_replace('#[^a-z 0-9?!]#i', '', $_POST['searchquery']);
		include ('config/config.php');	
		$sqlCommand = "(SELECT * FROM advert WHERE MATCH (header,ad_text) AGAINST ('$searchquery')) UNION (SELECT * FROM members WHERE MATCH (company,aboutus) AGAINST ('$searchquery'))";
    $query = mysql_query($sqlCommand) or die(mysql_error());
	$count = mysql_num_rows($query);
	if($count > 1){
		$search_output .= "<hr />$count results for <strong>$searchquery</strong>";
		$row = mysql_fetch_array($query);
		if(isset($row['header']) OR isset($row['ad_text'])) {
			echo "Advert";
			while($rows = mysql_fetch_array($query)){
				$header = $rows["header"];
				$ad_text = $rows["ad_text"];
				$search_output .= "Item ID: $header - $ad_text<br />";
			} // close while
		}
		elseif(isset($row['company']) OR isset($row['aboutus'])) {
			echo "Business";
			while($rows = mysql_fetch_array($query)){
				$company = $rows["company"];
				$aboutus = $rows["aboutus"];
				$search_output .= "Item ID: $company - $aboutus<br />";
			} // close while
		}
	}
		else {
		$search_output = "<hr />0 results for <strong>$searchquery</strong><hr />$sqlCommand";
		}
}
?>
<html>
<head>
</head>
<body>
<h2>Search the Exercise Tables</h2>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
Search For:
  <input name="searchquery" type="text" size="44" maxlength="88">
<input name="myBtn" type="submit">
<br />
</form>
<div>
<?php echo $search_output; ?>
</div>
</body>
</html>
2

Are you getting an error from the MySQL query? The columns of both tables in a UNION query have to be the same; that is to say advert and members need to return the same columns, which I’m guessing they don’t as you’re MATCHing different columns. You can use aliases though:

(SELECT header AS col1, ad_text AS col2 FROM advert WHERE MATCH (header,ad_text) AGAINST ('$searchquery'))
UNION
(SELECT company AS col1, aboutus AS col2 FROM members WHERE MATCH (company,aboutus) AGAINST ('$searchquery'))

The problem then is you don’t know which table the results have come from, so if you need to know that you can add another column:

(SELECT header AS col1, ad_text AS col2, 'advert' AS matched_table FROM advert WHERE MATCH (header,ad_text) AGAINST ('$searchquery'))
UNION
(SELECT company AS col1, aboutus AS col2, 'members' AS matched_table FROM members WHERE MATCH (company,aboutus) AGAINST ('$searchquery'))