6 {{ upvoteCount | shortNum }}
6 {{ upvoteCount | shortNum }}

PHP wildcard search function

by otfromtot
5 replies
I have a search form that can search for exact terms and I am trying to make it so you can search a partial term.
If I search for a city, Palm I want it to pull up palm beach, palm beach gardens, and west palm beach. I know that wildcard is % % but I cannot figure out how to add them here

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

    $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)) {
        if($_SERVER['REQUEST_METHOD'] === 'POST') {
        echo "<option value='".$row["state"]."'".($row["state"]==$_REQUEST["state"] ? " selected" : "").">".$row["state"]."</option>";
    }
    else {
    echo "<option value='".$row["state"]."'>".$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>
#function #php #search #wildcard
  • Profile picture of the author KirkMcD
    and city LIKE '%searchterm%'
    {{ DiscussionBoard.errors[8296238].message }}
  • Profile picture of the author otfromtot
    Originally Posted by KirkMcD View Post

    and city LIKE '%searchterm%'
    PHP Code:
    WHERE city LIKE '%".mysql_real_escape_string($_REQUEST["city"])."%'".$search_city.$search_state; 
    ?
    {{ DiscussionBoard.errors[8296271].message }}
  • Profile picture of the author otfromtot
    I've cleaned up the sql calls a bit but am still not able to use the wildcard search

    PHP Code:
    <?php
    error_reporting    (0);
    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">
    <?php
     
            $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)) {
                    if(    $_SERVER['REQUEST_METHOD'] === 'POST') {
                    echo     "<option value='".$row["state"]."'".($row["state"]==$_REQUEST["state"] ? " selected" : "").">".$row["state"]."</option>";
            }
            else {
            echo     "<option value='".$row["state"]."'>".$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

    $sql     = sprintf("SELECT * FROM %s WHERE city LIKE '%s' AND state LIKE '%s'",
                       $SETTINGS["data_table"],
                       mysql_real_escape_string($_REQUEST['city']),
                       mysql_real_escape_string($_REQUEST['state'])); 
    //$sql = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE city LIKE '".mysql_real_escape_string($_REQUEST["city"])."' AND state LIKE '".mysql_real_escape_string($_REQUEST["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[8301809].message }}
  • Profile picture of the author otfromtot
    I have tried
    PHP Code:
     = sprintf("SELECT * FROM %s WHERE city LIKE '%%s%' AND state LIKE '%s'", 
    PHP Code:
     = sprintf("SELECT * FROM %s WHERE city LIKE %%s% AND state LIKE '%s'", 
    PHP Code:
     = sprintf("SELECT * FROM %s WHERE city LIKE "%'%s'%" AND state LIKE '%s'", 
    PHP Code:
     = sprintf("SELECT * FROM %s WHERE city LIKE '%"%s"%' AND state LIKE '%s'", 
    and none of it works. I haven't been able to find a guide that will help get it working with the mysql_real_escape_string()
    {{ DiscussionBoard.errors[8309569].message }}
  • Profile picture of the author otfromtot
    I found it.

    PHP Code:
     = sprintf("SELECT * FROM %s WHERE city LIKE '%%%s%%' AND state LIKE '%s'", 
    or

    PHP Code:
    $sql = sprintf("SELECT * FROM %s WHERE city LIKE '%s' AND state LIKE '%s'",
                       $SETTINGS["data_table"],
                       "%".mysql_real_escape_string($_REQUEST['city'])."%",
                       mysql_real_escape_string($_REQUEST['state'])); 
    {{ DiscussionBoard.errors[8310009].message }}

Trending Topics