1

In my search script I'm having troubles getting a result for the following code:

    $sqlCommand = "SELECT `Loc.`, Model, Make, Description, `Tag No.`,
            MATCH(`Loc.`, Model, Make, Description, `Tag No.`)
            AGAINST ('$searchquery' IN BOOLEAN MODE) AS score FROM ag_combine
            WHERE MATCH(`Loc.`, Model, Make, Description, `Tag No.`)
            AGAINST ('$searchquery' IN BOOLEAN MODE) AND (Status='IN' or Status='OF') ORDER BY score DESC";

The query works fine when searching columns up until the Tag No. column, then it'll show a 0 Results message; however the same query in phpMyAdmin will produce the desired Tag No. result.

I'm guessing it has to do with the blank space in the column name, but not sure how to correctly select from it.

Here is the full code I'm testing:

<?php
 include "includes/config.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']);
     if($_POST['filter1'] == "Combines"){
        $sqlCommand = "SELECT `Loc.`, Model, Make, Description, `Tag No.`,
                MATCH(`Loc.`, Model, Make, Description, `Tag No.`)
                AGAINST ('$searchquery' IN BOOLEAN MODE) AS score FROM ag_combine
                WHERE MATCH(`Loc.`, Model, Make, Description, `Tag No.`)
                AGAINST ('$searchquery' IN BOOLEAN MODE) AND (Status='IN' or Status='OF') ORDER BY score DESC";     
    } else if($_POST['filter1'] == "Tractors"){
        $sqlCommand = "(SELECT `Loc.`, Model, Make, Description, Status, `Tag No.` FROM ag_tractor WHERE MATCH (`Loc.`, Model, Make, Description,`Tag No.` ) AGAINST ('$searchquery' IN BOOLEAN MODE) AND  (Status='IN' or Status='OF'))";      
    }

    $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><hr />$sqlCommand<hr />";
        while($row = mysql_fetch_array($query)){
            $loc = $row["Loc."];
            $tag = $row["Tag No."];
            $model = $row["Model"];
            $make = $row["Make"];
            $description = $row["Description"];
            $search_output .= "$loc: $tag: $make $model - $description<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"> 
Within: 
<select name="filter1">
<option value="Combines">Combines</option>
<option value="Tractors">Tractors</option>
</select>
<input name="myBtn" type="submit">
<br />
</form>
<div>
<?php echo $search_output; ?>
</div>
</body> 
</html>
MacCapper
  • 11
  • 3
  • You are not sharing the SQL command, you share just the PHP code that (partially) builds it. Just share the bare SQL command (or share two: The last working one and the not working one). Also which part of [Blank spaces in column names with MySQL](http://stackoverflow.com/questions/6055505/blank-spaces-in-column-names-with-mysql) didn't work for you? What makes your question different to that question? – hakre Dec 16 '12 at 21:15
  • I've updated the question to show full code. I can successfully search the Loc., Model, Make, & Description columns. It's when I try to search the `Tag No.` column it's producing 0 results. – MacCapper Dec 16 '12 at 21:51
  • 1
    Geez, calm down. Besides, I don't care what you think of the code. if($count >= 1) has fixed the issue. – MacCapper Dec 16 '12 at 22:01
  • 1
    Good to see that asking for feedback has helped you to review your code. And no need to calming down, I mean, I'm not hot or something. Just wanted to point you out that if you ask a question about why a SQL query does not work, you should provide the SQL query or ask a different question. That's all. Keep in mind that for this site we're looking for questions that are applicable to a larger community, not individual problems because of some mistake, like the wrong if check (wrong boundary check). – hakre Dec 16 '12 at 22:03
  • if($count > 1) was keeping the query from showing a single result. Changed to if($count >= 1) and it's working. – MacCapper Dec 16 '12 at 22:04
  • 2
    As a practical matter, I think you would want to avoid blank spaces and punctuation in column names, just as you would want to avoid using a reserved word for a column name. – Ray Paseur Dec 16 '12 at 22:08

0 Answers0