searching through sql data and displaying on php

Question

This is a repost but my previous one wouldn't let me edit. I'm trying to take details from a html/php page and allow at the user to "search" through the results page, however, when I enter a search term nothing is displayed on the search.php page.

HTML/PHP showing current details + searchbox:

<form method="post" action="search.php">
<input type="text" name="search" />
<input type="submit" name="submit" value="   Search   ">
</form>

<div id="leftdiv" style="width: 40%; float:left">
    <form id="form1" method="get">
    <table id="table">
        <th>Property ID</th>
        <th>Property Name</th>
            <?php   
            $result = mysql_query("SELECT * FROM Property");
            while ($row = mysql_fetch_array($result))
                {
                    $pID = $row['pID'];
                    $pLocation = $row['pLocation'];

                    echo "<tr data-row='$pID'><td>$pID</td>";
                    echo "<td>".$pID."</td>";
                    echo "<td>".$pLocation."</td>";
                }
            ?>
    </table>
    </form>
</div>

searchresults.php code:

$search=$_POST['search'];
$sql="SELECT * FROM Property
WHERE pLocation like 'search%'";

$result=mysql_query($sql) or die(mysql_error());


while ($row=mysql_fetch_array($result))
            {
            $pID = $row['pID'];
            $pLocation = $row['pLocation'];
            echo "<tr data-row='$pID'>";
            echo "<td>".$pID."</td>";
            echo "<td>".$pLocation."</td></tr>";
            }

Essentially all im trying to do is display all properties in database, then allow user to filter results based on location. If they type in London, only properties in London to be shown.

**Warning:** you're using [a **deprecated** database API](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) and should use a [modern replacement](http://php.net/manual/en/mysqlinfo.api.choosing.php). You are also **vulnerable to [SQL injection attacks](http://bobby-tables.com)** that a modern API would make it easier to [defend](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) yourself from. Moreover, please use [`htmlspecialchars`](http://php.net/htmlspecialchars) when outputting to HTML to prevent XSS. — Marcel Korpel, Jan 08 '14 at 13:38
Hi, thanks for replying again. It just is not displaying anything when I enter a search term in the box. — user3173250, Jan 08 '14 at 13:41
I think your query is built the wrong way. Since you're putting the parameter in $search (which you shouldn't do directly like this). something like this should work: $sql="SELECT * FROM Property WHERE pLocation like '%" . $search . "%'"; You can write it differently, I like this way, so you can clearly see the variables being used. — Ruben Verschueren, Jan 08 '14 at 13:42
You are posting to "search.php" but now you renamed it to searchresults.php? — Digital Chris, Jan 08 '14 at 13:44
Sorry, that was just my mistake in writing this. the file is still called search.php.. — user3173250, Jan 08 '14 at 13:44

score 0 · Answer 1 · edited May 23 '17 at 12:19

Ok try this: i've escaped the search, and added an else case if you get no results back:

$search= mysql_real_escape_string($_POST['search']);
$sql="SELECT * FROM Property
WHERE pLocation like '".$search."%'";

$result=mysql_query($sql) or die(mysql_error());

if (count($result)) {
    while ($row=mysql_fetch_array($result))
            {
            $pID = $row['pID'];
            $pLocation = $row['pLocation'];
            echo "<tr data-row='$pID'>";
            echo "<td>".$pID."</td>";
            echo "<td>".$pLocation."</td></tr>";
    }
} else {
    echo "<tr><td colspan='3'>No results found.</td></tr>";
}

Note: this is a followup to this question

@user3173250 remember to select as the correct answer please. :) — Digital Chris, Jan 08 '14 at 14:50

score 0 · Answer 2 · answered Jan 08 '14 at 13:49

Your query should be

$sql = "SELECT * FROM Property WHERE pLocation like '$search%'";

Note that is not a good way to do it - as earlier comments mention, you are using a deprecated database API, and you're making yourself vulnerable to SQL injections.

I wonder why you added javascript as the first tag, not much js here.

searching through sql data and displaying on php

2 Answers2