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 } ?>

Answer 1

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");

Answer 2

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.

Answer 3

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.