Search Contacts Table Form

 
 
Search Field:
Search Text:
Order By Field:
 
   
 
 

Instructions

The following are All the Records in the Contact Table

Id>First Name>Last Name>Address>City>State>Zip>Phone>
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

// set error reporting to all
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;

$order_field = isset($_POST['order_field']) ? $_POST['order_field'] : null;

// declare variables that are populated by the open-db file
$db_name = '';
$con = '';

// open db without exposing credentials
include('includes/open-db.php');

// db functions that report mysql errors and clean text for inserting into db
include('includes/functions.php');

// pull the field names directly from the db table
$field = array();
$field_text = array();

// query to see fields
$query = "SHOW COLUMNS FROM contacts FROM $db_name";
$result = mysqli_query($con, $query) or die(report($query, __LINE__, __FILE__));

while ($row = mysqli_fetch_array($result))
{
    // pull the actual fields
    $a = $row[0];
    // store raw field
    $field[] = $a;
    // remove underlines from raw field
    $a = str_replace('_', ' ', $a);
    // uppercase first letter of each word
    $field_text[] = ucwords($a);
}

// close db
include('includes/close-db.php');

include('includes/header.php');
?>

<h2>Search Contacts Table Form</h2>

<div class="clear">
    &nbsp;
</div>

<form action="<?php echo($self); ?>" method="post">
    <table class="full">
        <tr>
            <td>&nbsp;</td>
        </tr>
        <tr>
            <td class="right leftside">Search Field:</td>
            <td class="left rightside">

                <select name="search_field">
                    <?php
                    // populate the pull-down menu
                    foreach ($field_text as $key => $value)
                    {
                        $selected = '';
                        if ($field[$key] == $search_field)
                        {
                            $selected = 'selected';
                        }
                        echo("<option value=\"$field[$key]\" $selected >$value");
                    }
                    ?>

                </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($search_text); ?>">
            </td>
        </tr>
        <!-- add option to order results -->
        <tr>
            <td class="right leftside">Order By Field:</td>
            <td class="left rightside">

                <select name="order_field">
                    <?php
                    // populate the pull-down menu
                    foreach ($field_text as $key => $value)
                    {
                        $selected = '';
                        if ($field[$key] == $order_field)
                        {
                            $selected = 'selected';
                        }
                        echo("<option value=\"$field[$key]\" $selected >$value");
                    }
                    ?>

                </select>
            </td>
        </tr>
        <!-- added option to order results -->
        <tr>
            <td>&nbsp;</td>
        </tr>
        <tr>
            <td class="controls">
                <input type="hidden" name="step" value=1>
                &nbsp; &nbsp; <input type="submit" class="button" name="submit" value="Search">
            </td>
        </tr>
        <tr>
            <td colspan="2">&nbsp;</td>
        </tr>

    </table>
</form>

<div class="clear">
    &nbsp;
</div>

</tr>

<?php

// only provide search results IF a search was done
if ($step == 1)
{
    // open db without exposing credentials
    include('includes/open-db.php');

    $search_field = cleanForDB($con, $search_field);
    $search_text = cleanForDB($con, $search_text);

    $order_field = cleanForDB($con, $order_field);

    $query = "SELECT COUNT(*) as total FROM contacts WHERE $search_field LIKE '$search_text' ORDER BY '$order_field' ";
    $result = mysqli_query($con, $query) or die(report($query, __LINE__, __FILE__));

    $row = mysqli_fetch_array($result);
    $total = $row['total'];

    ?>

    <h2>
        Results of Search <?php echo($total); ?> Records
    </h2>

    <table class="full">

        <tr class="header2">
            <?php
            // populate the headers (<th>) of the table
            foreach ($field_text as $field)
            {
                echo("<th>$field></th>");
            }
            ?>
        </tr>

        <?php

        // start clock
        $starttime = microtime();
        $startarray = explode(" ", $starttime);
        $starttime = $startarray[1] + $startarray[0];

        // get count
        $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'];

        // get records that match
        $query = "SELECT * FROM contacts WHERE $search_field LIKE '$search_text' ORDER BY $order_field ";
        $result = mysqli_query($con, $query) or die(report($query, __LINE__, __FILE__));

        // end clock
        $endtime = microtime();
        $endarray = explode(" ", $endtime);
        $endtime = $endarray[1] + $endarray[0];
        $totaltime = $endtime - $starttime;
        $totaltime = round($totaltime, 5);

        // close db
        include('includes/close-db.php');

        // if a search was successful (num_rows > 0)
        if (mysqli_num_rows($result))
        {
            $i = 0;
            // pull data from result and display rows
            while ($row = mysqli_fetch_array($result))
            {
                $id = $row['id'];
                $first_name = $row['first_name'];
                $last_name = $row['last_name'];
                $address = $row['address'];
                $city = $row['city'];
                $state = $row['state'];
                $zip = $row['zip'];
                $phone = $row['phone'];
                // the below adds records to the table displayed
                ?>

                <tr class="row<?php echo($i++ & 1); ?>">
                    <td>
                        <?php echo($id); ?>
                    </td>
                    <td>
                        <?php echo($first_name); ?>
                    </td>
                    <td>
                        <?php echo($last_name); ?>
                    </td>
                    <td>
                        <?php echo($address); ?>
                    </td>
                    <td>
                        <?php echo($city); ?>
                    </td>
                    <td>
                        <?php echo($state); ?>
                    </td>
                    <td>
                        <?php echo($zip); ?>
                    </td>
                    <td>
                        <?php echo($phone); ?>
                    </td>
                </tr>

                <?php
                $i++;
            }
        }
        else // no results
        {
            echo('<tr><td class="red center" colspan="7">NO RECORDS FOUND!</td</tr>');
        }
        ?>

    </table>

    <?php
    echo("<p class=\"tiny center\">$total total records returned - Search took: $totaltime seconds</p>");
}
?>

<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 any Search TEXT will produce records that contain <b>exactly AND only</b> that TEXT.
    </li>
    <li>
        Using a "Wild Card" Character usually provides more results. This can be preformed by using the percent sign
        (i.e., %). For example, searching Last Name Field with the text "<span class="highlight">berg</span>" will
        provide
        people (in the table) with the last name of exactly <span class="highlight">Berg</span>. Whereas using
        "%berg"
        provides people with the last name of <span class="highlight">Berg</span> and Beren<span class="highlight">berg</span>.
        Using '%berg% provides people with the last name of <span class="highlight">Berg</span>, Beren
        <span class="highlight">berg</span>, and <span class="highlight">Berg</span>er.
    </li>
    <li>
        Another option is using the "Placeholder" Character. This can be preformed by using the Underline Character
        (i.e., _).
        For example, searching the First Name Field using a first name of "<span class="highlight">an_</span>" will
        provide "<span class="highlight">Ana</span>" and "<span class="highlight">Ann</span>". Whereas using
        "<span class="highlight">an__</span>" (note, two underlines) will provide "
        <span class="highlight">Anne</span>", "<span class="highlight">Anna</span>", and
        "<span class="highlight">Aner</span>".
    </li>
    <li>
        Also, using combinations of the "Wild Card" and "Placeholder" Characters can be used for more exotic
        searches. For example, "<span class="highlight">a_%_a</span>" will find all first names that start and end
        with an "a" and that are at least four characters long such as "<span class="highlight">Adana</span>" and
        "<span class="highlight">Alexandra</span>", but not "<span class="highlight">Ana</span>"
    </li>
    <li>
        Please enter only characters a-z, A-Z (case does not matter), Numbers 0-9, Percent sign (i.e., %), and
        Underline (i.e., _). Entering other characters may produce odd results.
    </li>
    <li>
        Please Note: All of this was accomplished by use of a simple MySQL Query, namely:<br><br>
        <span class="blue">"SELECT * FROM contacts WHERE $field LIKE '$text' ORDER BY $field"</span>
    </li>
</ul>

<div class="clear">
    <hr>
</div>

<h2>The following are All the Records in the Contact Table</h2>

<!-- start of html table -->
<table class="full">

    <tr class="header2">
<!-- show the headers of the table-->
        <?php

        foreach ($field_text as $field)
        {
            echo("<th>$field></th>");
        }
        ?>
    </tr>

    <?php

    // open db without exposing credentials
    include('includes/open-db.php');

    // get all records in the database
    $query = "SELECT * FROM contacts WHERE id > '0' ";
    $result = mysqli_query($con, $query) or die(report($query, __LINE__, __FILE__));

    // close db
    include('includes/close-db.php');

    // display all records
    while ($row = mysqli_fetch_array($result))
    {
        $id = $row['id'];
        $first_name = $row['first_name'];
        $last_name = $row['last_name'];
        $name = $first_name . " " . $last_name;
        $address = $row['address'];
        $city = $row['city'];
        $state = $row['state'];
        $zip = $row['zip'];
        $phone = $row['phone'];
        $i = 0;
        ?>

        <tr class="row<?php echo($i++ & 1); ?>">
            <td>
                <?php echo($id); ?>
            </td>
            <td>
                <?php echo($first_name); ?>
            </td>
            <td>
                <?php echo($last_name); ?>
            </td>
            <td>
                <?php echo($address); ?>
            </td>
            <td>
                <?php echo($city); ?>
            </td>
            <td>
                <?php echo($state); ?>
            </td>
            <td>
                <?php echo($zip); ?>
            </td>
            <td>
                <?php echo($phone); ?>
            </td>
        </tr>

        <?php
    }
    ?>

</table>

<div class="clear">
    <hr>
</div>

<?php

include('includes/footer.php');

?>