How to filter data from a MySQL Database Table with PHP

[jonlloydd]

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.

db_connect.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>

[pritaeas]

I suggest you echo the query, then run it against your database to see what happens.

[jonlloydd]

I have done this however when I run the query in phpmyadmin it produces an error

SQL query: Documentation

SELECT *
FROM memberif( isset(
$_POST[ 'search']
) ) {$search_term = mysql_real_escape_string(
$_POST[ 'search_box']
);

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 'if (isset($_POST['search'])) {

                $search_term = mysql_real_escape_strin' at line 3 

[pritaeas]

You are running the code, that can't work...

$sql = "SELECT * FROM member ";
if (isset($_POST['search'])) {
    $search_term = mysql_real_escape_string($_POST['search_box']);
    $sql .= "WHERE MB_COUNTY = '{$search_term}' ";
}

echo $sql; // this outputs the query to your browser, run that

[jonlloydd]

That code also produces an error

SQL query: Documentation

SELECT *
FROM memberif( isset(
$_POST[ 'search']
) ) {$search_term = mysql_real_escape_string(
$_POST[ 'search_box']
);

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 'if (isset($_POST['search'])) {
    $search_term = mysql_real_escape_string($_PO' at line 2 

[pritaeas]

You're not doing as I asked.

[OsaMasw]

can't do a SQL like this in phpmyadmin
try to create SQL query with fixed string to see if your sql is right
for example

$sql = "SELECT * FROM member WHERE MB_COUNTY = 'IQ'"; // put somthing you know its in the database

and check if there is an error.

[user888]

well johnl time to show pritaeas some shit
@pritaeas great work ahead

[Gideon_1]

@jollydd, you sql is not working because you are running php codes in it.

Follow pritaes tutorial and run that sql in phpMyadmin. By the way mysql is deprecated try to use PDO or Mysqli