PHP mysql database search

4 replies
I'm working on setting up my first database search function and I can't figure out the problem I'm having. I followed this guide php search script - php search engine - php mysql search with some changes to the column names for my database.

I originally had the query
SELECT zip FROM locate WHERE MATCH(zip,city,state) AGAINST '%$find%'
like this
SELECT * FROM locate WHERE city LIKE'%$find%'
but that didn't work either.

What am I donig wrong here?

PHP Code:
<h2>Zip Code Look-Up</h2>
<form name="search" method="post" action="<?=$PHP_SELF?>">
  City :
  <input type="text" name="find" />
  <input type="hidden" name="searching" value="yes" />
  <input type="submit" name="search" value="Search" />
</form>
<? 
//This is only displayed if they have submitted the form 
if ($searching =="yes") 

echo "<h2>Results</h2><p>"; 

//If they did not enter a search term we give them an error 
if ($find == "") 

echo "<p>You forgot to enter a search term</p>"; 
exit; 


// Otherwise we connect to our Database 
include 'connect.php';

// We preform a bit of filtering 
$find = strtoupper($find); 
$find = strip_tags($find); 
$find = trim ($find); 

//Now we search for our search term, in the field the user specified 
$data = mysql_query("SELECT zip FROM locate WHERE MATCH(zip,city,state) AGAINST '%$find%'"); 

//And we display the results 
while($result = mysql_fetch_array( $data )) 

echo $result['zip']; 
echo " "; 
echo $result['city']; 
echo "<br>"; 
echo $result['state']; 
echo "<br>"; 
echo "<br>"; 


//This counts the number or results - and if there wasn't any it gives them a little message explaining that 
$anymatches=mysql_num_rows($data); 
if ($anymatches == 0) 

echo "Sorry, but we can not find an entry to match your query<br><br>"; 


//And we remind them what they searched for 
echo "<b>Searched For:</b> " .$find; 

mysql_close($link);
?>
#database #mysql #php #search
  • Profile picture of the author otfromtot
    Also, I am using fulltext and MyISAM
    {{ DiscussionBoard.errors[8295365].message }}
  • Profile picture of the author otfromtot
    I ended up having to rewrite everything and got it working for the most part.
    PHP Code:
    <?php
    error_reporting
    (-1);
    include(
    "config.php");
    ?>

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Zip Code Look Up</title>
    <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.0/jquery.min.js"></script>
    <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.16/jquery-ui.min.js"></script>
    <link href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/themes/base/jquery-ui.css" rel="stylesheet" type="text/css"/>
    <style>
    BODY, TD {
        font-family:Arial, Helvetica, sans-serif;
        font-size:12px;
    }
    </style>
    </head>


    <body>

    <form id="form1" name="form1" method="post" action="search.php">
    <label for="city">City</label>
    <input name="city" type="text" id="city" size="10" value="<?php if($_SERVER['REQUEST_METHOD'] === 'POST') { echo $_REQUEST["city"];} ?>" />

    <label>State</label>
    <select name="state">
    <option value="">--</option>
    <?php
    if($_SERVER['REQUEST_METHOD'] === 'POST') {
        
    $sql "SELECT * FROM ".$SETTINGS["data_table"]." GROUP BY state ORDER BY state";
        
    $sql_result mysql_query ($sql$connection ) or die ('request "Could not execute SQL query" '.$sql);
        while (
    $row mysql_fetch_assoc($sql_result)) {
            echo 
    "<option value='".$row["state"]."'".($row["state"]==$_REQUEST["state"] ? " selected" "").">".$row["state"]."</option>";
        }
    }
    ?>
    </select>
    <input type="submit" name="button" id="button" value="Search" />
      </label>
      <a href="search.php"> 
      reset</a>
    </form>
    <br /><br />
    <?php
    if($_SERVER['REQUEST_METHOD'] === 'POST') {
        
    ?>
    <table width="700" border="1" cellspacing="0" cellpadding="4">
      <tr>
        <td width="90" bgcolor="#CCCCCC"><strong>Zip</strong></td>
        <td width="95" bgcolor="#CCCCCC"><strong>City</strong></td>
        <td width="159" bgcolor="#CCCCCC"><strong>State</strong></td>
      </tr>
    <?php

    if ($_REQUEST["city"]<>'') {
        
    $search_city " AND city='".mysql_real_escape_string($_REQUEST["city"])."'";    
    }

    if (
    $_REQUEST["state"]<>'') {
        
    $search_state " AND state='".mysql_real_escape_string($_REQUEST["state"])."'";    
    }

    if (
    $_REQUEST["city"]<>'' and $_REQUEST["state"]<>'') {
        
    $sql "SELECT * FROM ".$SETTINGS["data_table"]." WHERE city >= '".mysql_real_escape_string($_REQUEST["city"])."' AND state <= '".mysql_real_escape_string($_REQUEST["state"])."'".$search_city.$search_state;
    } else if (
    $_REQUEST["city"]<>'') {
        
    $sql "SELECT * FROM ".$SETTINGS["data_table"]." WHERE city >= '".mysql_real_escape_string($_REQUEST["city"])."'".$search_city.$search_state;
    } else if (
    $_REQUEST["state"]<>'') {
        
    $sql "SELECT * FROM ".$SETTINGS["data_table"]." WHERE state <= '".mysql_real_escape_string($_REQUEST["state"])."'".$search_city.$search_state;
    }

    $sql_result mysql_query ($sql$connection ) or die ('request "Could not execute SQL query" '.$sql);
    if (
    mysql_num_rows($sql_result)>0) {
        while (
    $row mysql_fetch_assoc($sql_result)) {
    ?>

    <tr>
        <td><?php echo $row["zip"]; ?></td>
        <td><?php echo $row["city"]; ?></td>
        <td><?php echo $row["state"]; ?></td>
      </tr>
      
    <?php
        
    }
    } else {
    ?>
    <tr><td colspan="5">No results found.</td>
    <?php    
    } }
    ?>
    </table>

    </body>
    </html>
    {{ DiscussionBoard.errors[8296144].message }}
  • Profile picture of the author KirkMcD
    I don't think you want to use the '<=' and '=>' comparisons.
    {{ DiscussionBoard.errors[8296248].message }}
    • Profile picture of the author otfromtot
      Originally Posted by KirkMcD View Post

      I don't think you want to use the '<=' and '=>' comparisons.
      You're probably right >_<
      I think I should replace it with LIKE, am I right?
      {{ DiscussionBoard.errors[8296267].message }}

Trending Topics