Search Contacts Table Demo

Search Field:	IdFirst NameLast NameAddressCityStateZipPhone
Search Text:

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 any Search TEXT will produce records that contain exactly AND only that TEXT.
• 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 "berg" will provide people (in the table) with the last name of exactly Berg. Whereas using "%berg" provides people with the last name of Berg and Berenberg. Using '%berg% provides people with the last name of Berg, Beren berg, and Berger.
• 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 "an_" will provide "Ana" and "Ann". Whereas using "an__" (note, two underlines) will provide " Anne", "Anna", and "Aner".
• Also, using combinations of the "Wild Card" and "Placeholder" Characters can be used for more exotic searches. For example, "a_%_a" will find all first names that start and end with an "a" and that are at least four characters long such as "Adana" and "Alexandra", but not "Ana"
• 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.
• Please Note: All of this was accomplished by use of a simple MySQL Query, namely:
  
  "SELECT * FROM contacts WHERE $field LIKE '$text' ORDER BY $field"

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

?>

---