CODEWITHSUNDEEP
phpmysqldatabasefilter
jonlloyd
jonlloyd

Reputation: 81

How to filter data from a MySQL Database Table with PHP

I am trying to code a search box that will search though a column in my database. If the search matches the column then that record will be printed in the table below.

I am searching on a column that contains the county of a company record. There are no errors being displayed however when I search for a county that is in the database the table remains blank. I can't see what I have done wrong in theory I think the code should work! Any help would be appreciated.

DBconnect.php

<?php
// connect to the database
$db = 'stylecraft_dev';
$host = 'localhost';
$user = 'stylecraft_admin';
$password = '000000';

$dbConn = mysql_connect($host,$user,$password) or die("Failed to connect to database");
$result = mysql_select_db($db, $dbConn) or die("Failure selecting database");
?>

form.php

        <?php
            $sql = "SELECT * FROM member ";

            if (isset($_POST['search'])) {

                $search_term = mysql_real_escape_string($_POST['search-box']);

                $sql .= "WHERE MB_COUNTY = '{$search_term}' ";
            }

            $query = mysql_query($sql) or die(mysql_error());
            ?>

            <form name="search_form" method="POST" action="stockists.php">
            Search: <input type="text" name="search_box" value=" "/>
            <input type="submit" name="search" value="Search the stockists...">
            </form>

            <table width="70%" cellpadding="5" cellspace="5">

            <tr>
                <td><strong>Company Name</strong></td>
                <td><strong>Website</strong></td>
                <td><strong>Phone</strong></td>
                <td><strong>Address</strong></td>
            </tr>

            <?php while ($row = mysql_fetch_array($query)) {?>
            <tr>
                <td><?php echo $row['MB_COMPANY'];?></td>
                <td><?php echo $row['MB_MOBILE'];?></td>
                <td><?php echo $row['MB_PHONE'];?></td>
                <td><?php echo $row['MB_COUNTY'];?></td>
            </tr>

            <?php } ?>
            </table>

Upvotes: 0

Views: 25889

Answers (4)

J.Hosea
J.Hosea

Reputation: 1

This was your code $sql .= "WHERE MB_COUNTY = '{$search_term}' ";

The correct code is $sql .= " WHERE MB_COUNTY = '{$search_term}' ";

**space between " and where clause is needed **

Upvotes: 0

azbatuk
azbatuk

Reputation: 293

There is a space in the search box value.

<input type="text" name="search_box" value=" "/>

If that is not intentional for some reason and it is being submitted with search phrase then it may cause the search not succeeding, meaning if you are searching for "Mercedes" but submitting it with a space like " Mercedes" then it is not a match because of the space.

You can either trim the search_term $search_term = trim($search_term) or just remove that space from value=" ".

Upvotes: 0

Paul Lo
Paul Lo

Reputation: 6148

Try:

            $search_term = mysql_real_escape_string($_POST['search_box']);
            $sql .= "WHERE MB_COUNTY LIKE '%".$search_term."%'";

The input name doesn't match - search-box versus search_box:

$_POST['search-box'] and <input type="text" name="search_box" value=" "/>

You should try to echo $search_term and $sql for debugging.

Upvotes: 1

Jordy
Jordy

Reputation: 1049

You got a typo here

$search_term = mysql_real_escape_string($_POST['search-box']);

Must be according to your form

$search_term = mysql_real_escape_string($_POST['search_box']);

Upvotes: 0

Related Questions