PHP/MySQL search... show all data by default, or show matched data

Question

I've built a page that shows a whole MySQL table however I now want to add a search into it.

What I've done is set it so when the user searches it displays the matching results, if there's no search then it displays the whole table. It's showing the table, but when I search it's returning no data (everything else is working though)

This is what I've got for the search...

<?php
if(isset($_POST['submit'])){
if(isset($_GET['query'])){
if(preg_match("/^[  a-zA-Z]+/", $_POST['query'])){
$query=$_GET['query'];
$q=mysql_query("SELECT * FROM employees WHERE name LIKE '%$query%'" ) or die("could not search");
$result = mysql_query($q) or die(mysql_error());
while ($row = mysql_fetch_array($result)){

I can't work out how it needs to be formatted. My search box is 'Query'. Any help would be appreciated!

----- EDIT ----- I can't get it to work still... any pointers?

<?php if(!empty($_SESSION['LoggedIn']) && !empty($_SESSION['Username']){?><br /><form method="post" action="search.php">
<input name="query" type="text" required class="forms" id="query" placeholder="Search name..." size="35" />
<input type="submit" name="submit" id="submit" value=" Search " HEIGHT="25" WIDTH="70" BORDER="0" ALT="Submit"><button onclick="window.location.href='search.php'"> Clear </button></form><br />
<table border="0" cellspacing="2" class="data"><tr>
<td align="center" class="idtd"><strong>ID</strong></td>
<td align="center" class="nametd"><strong>Name</strong></td>
<td align="center" class="positiontd"><strong>Position</strong></td>
<td align="center" class="banktd"><strong>Bank</strong></td>
<td align="center" class="pooltd"><strong>Pool</strong></td>
<td align="center" class="starttd"><strong>Start Date</strong></td>
<td align="center" class="endtd"><strong>End Date</strong></td>
<td align="center" class="ghourstd"><strong>Gross Hours</strong></td>
<td align="center" class="chourstd"><strong>Cont'd Hours</strong></td>
</tr></table>
<?php
if(isset($_POST['submit'])){$where = !empty($_GET['query']) ? $db->real_escape_string($_GET['query']) : "";
$q = mysql_query("SELECT * FROM employees WHERE name LIKE '% " . $where . "%'") or die(mysql_error());
while ($row = mysql_fetch_array($result)){
echo "<table class='data' border='0' cellspacing='2'><tr>
<td align='center' class='idtd'>".$row['id']."</td>
<td align='center' class='nametd'>".$row['name']."</td>
<td align='center' class='positiontd'>".$row['position']."</td>
<td align='center' class='banktd'>".$row['bank']."</td>
<td align='center' class='pooltd'>".$row['pool']."</td>
<td align='center' class='starttd'>".$nStartDate."</td>
<td align='center' class='endtd'>".$row['enddate']."</td>
<td align='center' class='ghourstd'>".$row['grosshours']."</td>
<td align='center' class='chourstd'>".$row['contractedhours']."</td><tr ></table>";}}}} else{ ?>
<h1>You must be logged in to view this page.</h1><?php } ?>

If you can, you should [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) [statements](http://php.net/manual/en/pdo.prepared-statements.php) instead, and consider using PDO, [it's really not hard](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Jun 10 '15 at 15:59
You're also mixing $_GET and $_POST in that preg_match line. — demonkoryu, Jun 10 '15 at 16:04

score 1 · Answer 1 · answered Jun 10 '15 at 16:57

take help of some variable like below

$condition = '1 = 1';//which will result all rows (1=1 is TRUE)
if($_GET['query'])
{
  $query=$_GET['query'];
  $condition = " name LIKE '%$query%'";// this will search only with querydata
}

$q=mysql_query("SELECT * FROM employees WHERE '$condition'" ) or die("could not search");

$result = mysql_query($q) or die(mysql_error());//you have double mysql_query() remove this

And most important thing is stop using mysql_* functions they are deprecated long ago, use mysqli_* or pdo , dont think these are new & difficult ,once look at the tutorials example.

score 0 · Answer 2 · answered Jun 10 '15 at 16:03

0

I believe you need a period in your SQL syntax:

$q=mysql_query("SELECT * FROM employees WHERE name LIKE '%.$query.%'" ) or die("could not search");

answered Jun 10 '15 at 16:03

Kirk Powell

908
9
14

I'm still struggling with this. Please see my edited post for full code. – noozl Jun 10 '15 at 18:17
`$q = mysql_query("SELECT * FROM employees WHERE name LIKE '% " . $where . "%'") or die(mysql_error()); while ($row = mysql_fetch_array($result)){` the `$result` variable has no reference, should it be `$q` instead? – Kirk Powell Jun 10 '15 at 18:21
Ah yes, it should. Doh! Now it just returns the whole table instead of matching the searched word... hmm – noozl Jun 10 '15 at 22:09

score 0 · Answer 3 · answered Jun 10 '15 at 16:05

I just would use this:

if(isset($_POST['submit'])){
    $where = !empty($_GET['query']) ? $db->real_escape_string($_GET['query']) : "";
    $q = mysql_query("SELECT * FROM employees WHERE name LIKE '% " . $where . "%'") or die(mysql_error());
    while ($row = mysql_fetch_array($result)){
        // ToDo
    }
}

For your information: You made the mysql_query() two times: the first time of a string, the second time of a result - the second time was the mistake.

PHP/MySQL search... show all data by default, or show matched data

3 Answers3