Home
Search Contacts Table Demo
Instructions
- Step 1 -- Select the Field you want to search.
- Step 2 -- Enter the TEXT you want to find in the Field.
- Step 3 -- Click Search and the results will appear above.
- NOTE: Entering search text with no special characters finds records that match exactly that text.
-
Wild card: the percent sign (%) matches any number of characters. Searching Last Name for
"berg" finds only Berg; "%berg" also finds
Berenberg; "%berg%" also finds
Berger.
-
Placeholder: the underline (_) matches exactly one character. "an_" finds Ana and Ann;
"an__" finds Anne, Anna, and Aner.
-
Combinations work too: "a_%_a" finds first names that start and end with "a" and are at
least four characters long (Adana, Alexandra, but not Ana).
- Please use only a-z, A-Z, 0-9, % and _. Other characters may produce odd results.
-
All of this is done with one simple MySQL query:
"SELECT * FROM contacts WHERE $field LIKE '$text' ORDER BY $field"
All Records in the Contacts Table
| 1 |
Gaynor |
Berger |
8 Horizon Road |
New Haven |
CT |
06510 |
(336) 902-1538 |
| 2 |
Lavaysse |
Circus |
27 Woodland Avenue |
San Mateo |
CA |
94402 |
(211) 107-2844 |
| 3 |
Weisman |
Berenberg |
3 Cindy Court |
Englewood |
NJ |
07631 |
(276) 673-3707 |
| 4 |
Garrett |
Mag |
83 Gentry Drive |
Melville |
NY |
11747 |
(521) 333-7762 |
| 5 |
Carie |
Max |
236 Stanton Street |
brookline |
MA |
02446 |
(180) 351-8977 |
| 6 |
Mason |
Homes |
587 Nathan Hale Road |
Bonita Springs |
FL |
34134 |
(638) 895-7446 |
| 7 |
Newton |
Berg |
2461 Riverside Dr. |
New York |
NY |
10002 |
(123) 720-9779 |
| 8 |
Erv |
Weitzman |
442 W. 98th St # 886 |
New York |
NY |
10009 |
(736) 854-3262 |
| 9 |
Homes |
Lambert |
841 central park west |
St Davids |
PA |
19087 |
(139) 523-8779 |
| 10 |
Rubin |
Sands |
134 Iven Avenue |
New York |
NY |
10024 |
(386) 634-8851 |
| 11 |
Riches |
Rags |
9739 85th Street |
New York |
NY |
10003 |
(571) 811-5584 |
| 12 |
Lee |
Jett |
918 Central Ave. #313 |
Christiansted |
VI |
00823 |
(841) 332-7923 |
| 13 |
John |
Mcadams |
Box 146 |
New York City |
NY |
10003 |
(515) 650-2411 |
| 14 |
Hall |
Mark |
Livio Andronico 22 |
New York |
NY |
10025 |
(534) 289-1364 |
| 15 |
Parson |
Comp |
300 Knoll Blvd. |
Wayne |
NJ |
07470 |
(279) 258-2678 |
| 16 |
Alex |
Carson |
8 Sundance Drive |
Mason |
MI |
48920 |
(825) 281-8880 |
| 17 |
Anna |
Lands |
80 Hudson Street |
Bellevue |
WA |
98004 |
(803) 918-7430 |
| 18 |
Adana |
Scheiby |
5219 433th Ave SE |
Hastings-on-Hudson |
NY |
10706 |
(129) 957-2662 |
| 19 |
Calvin |
Klien |
32 Gray Gardens East |
Broadlands |
VA |
20148 |
(793) 815-6116 |
| 20 |
Walker |
Floors |
794 A Fairmont Ave. |
Hoboken |
NJ |
07030 |
(351) 656-8438 |
| 21 |
Anne |
Oaks |
1941 aberdeen way |
Davis |
CA |
95616 |
(143) 275-2569 |
| 22 |
Ana |
Holms |
92525 N Meridian |
Pleasantville |
NY |
10570 |
(365) 404-2548 |
| 23 |
Brian |
Higgins |
P.O. Box 664 |
Indianapolis |
IN |
46290 |
(533) 230-4366 |
| 24 |
Davis |
Lodge |
492 Locust Road |
Charlotte |
NC |
28204 |
(164) 963-3123 |
| 25 |
Alexandra |
Tippon |
4114 W. Elm, Suite 370 |
Stratham |
NH |
03885 |
(330) 871-5564 |
| 26 |
Richman |
Blue |
PO BOX 4971 |
Boulder |
CA |
80302 |
(131) 664-3716 |
| 27 |
Ann |
Arms |
4388 Folsom Street, Suite 673 |
Santa Clara |
CA |
95050 |
(642) 915-9286 |
| 28 |
Weston |
Jobs |
100 44th Ave.SE |
Glenview |
IL |
60026 |
(486) 959-2036 |
| 29 |
Daves |
Davis |
4585 W. Lake |
Norman, |
OK |
73026 |
(643) 147-7945 |
| 30 |
Aner |
Rodman |
600 Art Blvd. |
Lansing |
MI |
48911 |
(158) 201-5442 |
Code follows
<?php
error_reporting(E_ALL);
$self = basename($_SERVER['SCRIPT_NAME']);
$step = isset($_POST['step']) ? $_POST['step'] : 0;
$search_field = isset($_POST['search_field']) ? $_POST['search_field'] : null;
$search_text = isset($_POST['search_text']) ? $_POST['search_text'] : null;
// ================= functions =================
// reports a query, line number, script name, and MySQL's actual error
function report($query, $line, $file)
{
global $con;
echo($query . '<br>' . $line . '<br>' . $file . '<br>' . mysqli_error($con) . '<br>');
}
// cleans text for the db (helps prevent SQL injection)
function cleanForDB($con, $str)
{
$str = stripslashes($str);
$str = trim($str);
$str = mysqli_real_escape_string($con, $str);
return $str;
}
// ================= get column names =================
include('includes/open-db.php');
$field = array(); // raw names, e.g. first_name
$field_text = array(); // pretty names, e.g. First Name
$query = "SHOW COLUMNS FROM contacts";
$result = mysqli_query($con, $query) or die(report($query, __LINE__, __FILE__));
while ($row = mysqli_fetch_array($result))
{
$field[] = $row[0];
$field_text[] = ucwords(str_replace('_', ' ', $row[0]));
}
// only allow a real column name (the dropdown can be tampered with)
if ($search_field !== null && !in_array($search_field, $field))
{
$search_field = $field[0];
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Search Contacts Table Demo</title>
<link rel="stylesheet" href="../css/styles.css">
</head>
<body>
<div class="page">
<p class="nav"><a href="../../index.php">Home</a></p>
<h2>Search Contacts Table Demo</h2>
<form action="<?php echo($self); ?>" method="post">
<table class="full">
<tr>
<td class="right leftside">Search Field:</td>
<td class="left rightside">
<select name="search_field">
<?php
foreach ($field_text as $key => $value)
{
$selected = ($field[$key] == $search_field) ? 'selected' : '';
echo("<option value=\"$field[$key]\" $selected>$value</option>");
}
?>
</select>
</td>
</tr>
<tr>
<td class="right leftside">Search Text:</td>
<td class="left rightside">
<input type="text" size="43" id="search_text" name="search_text"
value="<?php echo(htmlspecialchars($search_text ?? '')); ?>">
</td>
</tr>
<tr>
<td class="controls" colspan="2">
<input type="hidden" name="step" value="1">
<input type="submit" class="button" name="submit" value="Search">
</td>
</tr>
</table>
</form>
<?php
// only show results IF a search was done
if ($step == 1)
{
$search_field = cleanForDB($con, $search_field);
$search_text = cleanForDB($con, $search_text);
$starttime = microtime(true);
$query = "SELECT COUNT(*) AS total FROM contacts WHERE $search_field LIKE '$search_text'";
$result = mysqli_query($con, $query) or die(report($query, __LINE__, __FILE__));
$row = mysqli_fetch_array($result);
$total = $row['total'];
$query = "SELECT * FROM contacts WHERE $search_field LIKE '$search_text' ORDER BY $search_field";
$result = mysqli_query($con, $query) or die(report($query, __LINE__, __FILE__));
$totaltime = round(microtime(true) - $starttime, 5);
?>
<h2>Results of Search: <?php echo($total); ?> Records</h2>
<table class="full">
<tr class="header2">
<?php
foreach ($field_text as $heading)
{
echo("<th>$heading</th>");
}
?>
</tr>
<?php
if (mysqli_num_rows($result))
{
$i = 0;
while ($row = mysqli_fetch_array($result))
{
?>
<tr class="row<?php echo($i++ & 1); ?>">
<td><?php echo($row['id']); ?></td>
<td><?php echo($row['first_name']); ?></td>
<td><?php echo($row['last_name']); ?></td>
<td><?php echo($row['address']); ?></td>
<td><?php echo($row['city']); ?></td>
<td><?php echo($row['state']); ?></td>
<td><?php echo($row['zip']); ?></td>
<td><?php echo($row['phone']); ?></td>
</tr>
<?php
}
}
else
{
echo('<tr><td class="red center" colspan="8">NO RECORDS FOUND!</td></tr>');
}
?>
</table>
<p class="tiny center"><?php echo($total); ?> total records returned - Search took: <?php echo($totaltime); ?> seconds</p>
<?php
}
?>
<h2>Instructions</h2>
<ul class="instructions">
<li>Step 1 -- Select the Field you want to search.</li>
<li>Step 2 -- Enter the TEXT you want to find in the Field.</li>
<li>Step 3 -- Click Search and the results will appear above.</li>
<li>NOTE: Entering search text with no special characters finds records that match <b>exactly</b> that text.</li>
<li>
Wild card: the percent sign (%) matches any number of characters. Searching Last Name for
"berg" finds only <span class="highlight">Berg</span>; "%berg" also finds
Beren<span class="highlight">berg</span>; "%berg%" also finds
<span class="highlight">Berg</span>er.
</li>
<li>
Placeholder: the underline (_) matches exactly one character. "an_" finds Ana and Ann;
"an__" finds Anne, Anna, and Aner.
</li>
<li>
Combinations work too: "a_%_a" finds first names that start and end with "a" and are at
least four characters long (Adana, Alexandra, but not Ana).
</li>
<li>Please use only a-z, A-Z, 0-9, % and _. Other characters may produce odd results.</li>
<li>
All of this is done with one simple MySQL query:<br><br>
<span class="blue">"SELECT * FROM contacts WHERE $field LIKE '$text' ORDER BY $field"</span>
</li>
</ul>
<hr>
<h2>All Records in the Contacts Table</h2>
<table class="full">
<tr class="header2">
<?php
foreach ($field_text as $heading)
{
echo("<th>$heading</th>");
}
?>
</tr>
<?php
$query = "SELECT * FROM contacts WHERE id > '0' ";
$result = mysqli_query($con, $query) or die(report($query, __LINE__, __FILE__));
$i = 0;
while ($row = mysqli_fetch_array($result))
{
?>
<tr class="row<?php echo($i++ & 1); ?>">
<td><?php echo($row['id']); ?></td>
<td><?php echo($row['first_name']); ?></td>
<td><?php echo($row['last_name']); ?></td>
<td><?php echo($row['address']); ?></td>
<td><?php echo($row['city']); ?></td>
<td><?php echo($row['state']); ?></td>
<td><?php echo($row['zip']); ?></td>
<td><?php echo($row['phone']); ?></td>
</tr>
<?php
}
include('includes/close-db.php');
?>
</table>
<hr>
<h2>Code follows</h2>
<div class="code"><?php highlight_file(__FILE__); ?></div>
<p class="nav nav-foot"><a href="../../index.php">Home</a></p>
</div>
</body>
</html>