How to filter data from a MySQL Database Table with PHP

Question

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>

Paul Lo · Accepted Answer · 2015-03-04 12:45:39Z

1

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.

edited Mar 4, 2015 at 12:45

answered Mar 4, 2015 at 12:39

Paul Lo

6,1466 gold badges33 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jonlloyd Over a year ago

Ah yes thankyou I have corrected this however the problem persists!

Jordy · Accepted Answer · 2015-03-04 12:39:02Z

0

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']);

answered Mar 4, 2015 at 12:39

Jordy

1,0692 gold badges11 silver badges29 bronze badges

9 Comments

jonlloyd Over a year ago

Ah yes thankyou I have corrected this however this has not fixed my problem!

Jordy Over a year ago

Can you put an echo in the first if isset(), that echos out the country? echo $_POST['search_box'] Check if it echos out the result you want. Then try manually querying it on phpmyadmin to see if it actually gives results.

Jordy Over a year ago

@jonlloyd so right above the query, what does echo $sql give you? What if you copy that exact query and paste it in phpmyadmin.

jonlloyd Over a year ago

When I go to run the query it produces an error I have not seen before! SQL query: Documentation $sql = "SELECT * FROM member "; MySQL said: Documentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$sql = "SELECT * FROM member "' at line 1

Jordy Over a year ago

Here look at that it's not actually taking the value from $_POST search box. The query is not working because you got $sql in it as well. If you execute SELECT * FROM member it will work fine.

|

azbatuk · Accepted Answer · 2017-10-14 05:23:19Z

0

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=" ".

answered Oct 14, 2017 at 5:23

azbatuk

2931 silver badge8 bronze badges

1 Comment

azbatuk Over a year ago

Just noticed this was from 2 years ago. No idea how I ended up on this page.

JC Guidicelli · Accepted Answer · 2018-06-18 14:54:36Z

0

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 **

edited Jun 18, 2018 at 14:54

JC Guidicelli

1,3268 silver badges16 bronze badges

answered Jun 18, 2018 at 14:24

J.Hosea

11 bronze badge

Collectives™ on Stack Overflow

How to filter data from a MySQL Database Table with PHP

4 Answers 4

1 Comment

9 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

9 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related