SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Nov 2011
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Search Box to search mysql table fulltext php

    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:
    <?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. #2
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Location
    Surrey, UK
    Posts
    84
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    (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:

    Code:
    (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'))
    Martin.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •