Search Contacts Table Demo

Search Field:
Search Text:

Instructions


All Records in the Contacts Table

IdFirst NameLast NameAddressCityStateZipPhone
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>