Search Contacts Table Demo

 
 
Search Field:
Search Text:
 
   
 
 

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

    // code
    error_reporting(E_ALL);    // set error reporting to 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;

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

    include('includes/open-db.php');        //====== open dB

    // This pulls the field names directly from the db table
    // this is not necessary IF you know the names of the fields
    // however, if you want to write code that adapts to a changing table
    // then this is one way to do it.

    $field = array();
    $field_text = array();

    $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))
        {
        $a = $row[0];    // pull the actual fields
        $field[] = $a;    // store raw field
        $a = str_replace('_', ' ', $a);        // remove underlines from raw field
        $field_text[] = ucwords($a);    // uppercase first letter of each word
        }

    include('includes/close-db.php');        //====== close dB
    include('includes/header.php');
?>

    <h2>Search Contacts Table Demo</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
                            // this populates 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>
            <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)
        {

        include('includes/open-db.php');    //====== open dB

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

        $query = "SELECT COUNT(*) as total FROM contacts WHERE $search_field LIKE '$search_text' ORDER BY '$search_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 // this populates 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 $search_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);

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

                    if (mysqli_num_rows($result))    // if a search was sucessful (num_rows > 0)
                        {
                        $i = 0;
                        while ($row = mysqli_fetch_array($result))        // pull data from result and display rows
                            {
                            $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
                            ?>

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

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

        <tr class="header2">
            <?php // do the headers of the table

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

        <?php

            include('includes/open-db.php');        //====== open dB

            // get all records in the database

            $query = "SELECT * FROM contacts WHERE id > '0' ";
            $result = mysqli_query($con, $query) or die(report($query, __LINE__, __FILE__));

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

            // 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');

    // The following two functions should be in your code
    // -- OR -- be in the includes directory under "functions.php"
    // AND include that file in this script.

    // ================= functions  =================

    //--------------  show db errors --------------
    // this function reports mysql errors with line number and script name

    function report($query, $line, $file)
        {
        echo($query . '<br>' . $line . '<br>' . $file . '<br>');
        }

    //--------------  clean data for input into db  --------------
    // this function cleans all text for inserting into db
    // in other words, this function returns SQL injection-proof strings

    function cleanForDB($con, $str)
        {
        $str = stripslashes($str);
        $str = trim($str);
        $str = mysqli_real_escape_string($con, $str);
        return $str;
        }

?>