Search Box to search mysql table fulltext php

Codes here !

Moderators: macek, egami, gesf

kieran82
New php-forum User
New php-forum User
Posts: 1
Joined: Sat Jul 07, 2012 4:27 am

Search Box to search mysql table fulltext php

Postby kieran82 » Fri Aug 17, 2012 4:40 pm

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.

Code: Select all

<?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>

NigelRen
php-forum Active User
php-forum Active User
Posts: 450
Joined: Fri Aug 05, 2011 9:53 am

Re: Search Box to search mysql table fulltext php

Postby NigelRen » Fri Aug 17, 2012 11:35 pm

Have you tried running your SQL in something like phpmyadmin? This is just to make sure that your SQL is performing OK.

As for coding - you always seem to fetch a row - check what sort of information you have and then fetch the next row ( using mysql_fetch_array ) - this would mean that you will always miss the first row of data retrieved anyway.

NigelRen
php-forum Active User
php-forum Active User
Posts: 450
Joined: Fri Aug 05, 2011 9:53 am

Re: Search Box to search mysql table fulltext php

Postby NigelRen » Fri Aug 17, 2012 11:46 pm

One thing that could make your code a bit easier would be to let the SQL code do a bit more for you. So rather than having to work out which fields are set you could do something like...

Code: Select all

SELECT "Advert" as type, header, ad_text
    FROM advert
    WHERE MATCH (header,ad_text) AGAINST ('$searchquery'))
UNION
SELECT "Business" as type, company as header, aboutus as ad_text
    FROM members
    WHERE MATCH (company,aboutus) AGAINST ('$searchquery')


I haven't tested this but I hope you see the idea - it allows the SQL code to give you the same bits of information for each possibility, just with a label as to the sort of data it is.


Return to “mySQL & php coding”

Who is online

Users browsing this forum: No registered users and 1 guest