PHP MySQL search not returning results

Question

I've created a php search script to search a MySQL database but no search is returning results.

Form Code

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

PHP Script

<?php
                    if(isset($_POST['completedsearch']))
                    {
                            $term = $_POST['query'];
                            $mysql = mysql_connect("localhost","searchuser","password");
                            mysql_select_db("hcsd");
                            $qu = mysql_query("SELECT * FROM your_table WHERE COMPANY LIKE '%{$term}%' OR LOCATION LIKE '%{$term}%' OR KEYWORDS LIKE '%{$term}%' OR PRODUCTSSERVICES LIKE '%{$term}%' ");
                            echo "
                                            <th>Name</th>
                                            <th>Location</th>
                                            <th>Products/Services</th>
                                            ";
                            while($row = mysql_fetch_array($qu))
                                       {

                                            echo "<tr><td>";  
                                            echo $row['COMPANY'];
                                            echo "</td>";
                                            echo "<td>";
                                            echo $row['LOCATION'];
                                            echo "</td>";
                                            echo "<td>";
                                            echo $row['PRODUCTSSERVICES'];
                                            echo "</tr></td>";
                            }
                    }
            ?>

The MySQL database has 4 columns, headed COMPANY, LOCATION, KEYWORDS & PRODUCTSSERVICES, and this script should be searching any of the columns for the search term and then displaying COMPANY, LOCATION and PRODUCTSSERVICES for any matching rows in a table, yet even using search terms I know 100% are in the MySQL table, I'm still receiving no results.

And check this out: [SQL Injection](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1) — Vincent Ramdhanie, Nov 26 '13 at 14:56
At the end, you swapped and , it should echo "" I think - you might have to look into your generated html code — CppChris, Nov 26 '13 at 14:56
Is the table you are selecting from really called 'your_table' ? — Chris Wheeler, Nov 26 '13 at 15:00
@ChrisWheeler you're a lifesaver, I put 'your_table' in as a placeholder until my boss gave me PHPMyAdmin access, forgot to change it. Now having a problem with it all showing on one row (including the headers) instead of separate rows, any ideas? (changed to too) If it'd help, the search is on http://www.healthcareservicesdirect.com/ — user2965329, Nov 26 '13 at 15:04
Excellent, see my answer about escaping the data too - thats very important for security. Your header columns aren't wrapped in , and also you havent started or ended the table. A table should look like `
Heading 1 Heading 2
Data 1 Data 2
` — Chris Wheeler, Nov 26 '13 at 15:08
Please, before you write **any** more SQL interfacing code, you must read up on [proper SQL escaping](http://bobby-tables.com/php) to avoid severe [SQL injection bugs](http://bobby-tables.com/). Also, `mysql_query` should not be used in new applications. It's a deprecated interface that's being removed from future versions of PHP. A modern replacement like [PDO is not hard to learn](http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/). A guide like [PHP The Right Way](http://www.phptherightway.com/) will help you avoid making mistakes like this. — tadman, Nov 26 '13 at 15:27
@tadman I'll definitely look at those links, I'd love to be writing cleaner PHP/SQL code but I've only been working with it for about 7 hours.. lol — user2965329, Nov 26 '13 at 15:56
If you're just getting started, it's important to not pick up any poisonously bad habits that'll later bite you. The best way to learn PHP is to pick a [development framework](http://codegeekz.com/best-php-frameworks-for-developers/) which will give you a lot of tools and community code to help solve your problems rather than force you to write it all yourself. They also set good examples you can follow from. — tadman, Nov 26 '13 at 16:31

Chris Wheeler · Accepted Answer · 2013-11-26T15:29:38.010

1

I'm not sure the curly braces are helping here...

Replace

 $qu = mysql_query("SELECT * FROM your_table WHERE COMPANY LIKE '%{$term}%' OR LOCATION LIKE '%{$term}%' OR KEYWORDS LIKE '%{$term}%' OR PRODUCTSSERVICES LIKE '%{$term}%' ");

With

 $qu = mysql_query("SELECT * FROM your_table WHERE COMPANY LIKE '%".$term."%' OR LOCATION LIKE '%".$term."%' OR KEYWORDS LIKE '%".$term."%' OR PRODUCTSSERVICES LIKE '%".$term."%'");

You should escape the input as well

 $qu = mysql_query("SELECT * FROM your_table WHERE COMPANY LIKE '%".mysql_real_escape_string($term)."%' OR LOCATION LIKE '%".mysql_real_escape_string($term)."%' OR KEYWORDS LIKE '%".mysql_real_escape_string($term)."%' OR PRODUCTSSERVICES LIKE '%".mysql_real_escape_string($term)."%'");

Also consider using the more recent mysqli_ functions

edited Nov 26 '13 at 15:29

answered Nov 26 '13 at 14:55

Chris Wheeler

1,623
1
11
18

Accepted this answer due to the help adding the input escape and @Chris Wheeler answering my question in his comment above. – user2965329 Nov 26 '13 at 15:08
+1 to improve this request. The best I thing is to use PDO and use prepare request or recent mysqli as @Chris Wheeler suggested. It's more secure. Recommended by PHP Doc: http://php.net/manual/fr/function.mysql-escape-string.php – Lapinou Nov 26 '13 at 15:14
It's not "additional security", it's writing it **properly**. Failing to escape will lead to all kinds of bugs if user data contains characters like `'`. – tadman Nov 26 '13 at 15:27

score 1 · Answer 2 · answered Nov 26 '13 at 14:55

1

What is the name of your table ?

You should try your request in phpmyadmin or anything software you use for manage your DB and see the results. If not result, there is probably an error in your request.

answered Nov 26 '13 at 14:55

Lapinou

1,467
2
20
39

The table is called Sheet1 Didn't think of that, I'll give it a go – user2965329 Nov 26 '13 at 14:59
+1 for asking about the table name, slightly before I did I think :) – Chris Wheeler Nov 26 '13 at 15:11

PHP MySQL search not returning results

2 Answers2