Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

Search Box to search mysql table fulltext php

Codes here !

Moderators: macek, egami, gesf

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>
kieran82
New php-forum User
New php-forum User
 
Posts: 1
Joined: Sat Jul 07, 2012 4:27 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.
NigelRen
php-forum Active User
php-forum Active User
 
Posts: 450
Joined: Fri Aug 05, 2011 9:53 am


Return to mySQL & php coding

Who is online

Users browsing this forum: Google [Bot] and 1 guest

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.

cron