Binita PHP Example

Back

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



                <label>

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

                </label>

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

}



?>

Back


Search Field:
Search Text: