YOUR PHP Example

Back
CRUD Records in Authors Table


ID	First Name	Second Name
1	Johny	Joness
2	Steve	Pet
3	Tom	Thumb
5	Don	Duck
8	tedd	sperling
12	Joey	Shears
14	Thurston	Howell III
15	Jan	Brady
16	Mark	Twain
17	Yeehaw	Dawg
20	Bunny	Rabit
22	Bronwyn	Rruffus
24	Lauren	Olin
25	Billy	Williams
26	Joseph	Tierney
27	Owen	Bozsik
30	Amin	Hassen
31	Lawrence	Roberts
32	Hyereen	Shin
33	Dane	Farquhar
36	Ajay	McDaniel
37	Nick	Lycos
38	Chris	Sycamore
39	Don	Eckford
40	Veronica	Hutchins
41	Kaylyn	Fragel
42	Ashlynn	Fritz
43	Daniel	Paxton
44	Kaitlyn	Silengo
45	Mahmoud	Siryani
46	Binita	Tamang
47	Brian	Lark
Current Selected Record
Fields	Values
ID:	<-- You cannot edit this ID (This is generated via autoincrement)
First Name:
Last Name:
CODE FOLLOWS

<?php

//==================================================================

//  authors.php (CRUD) Authors by Tedd

//==================================================================



// code

if (session_id() == '')

{

    session_start();

}



session_name("sperlt");



include('includes/functions.php');    // standard functions



// init vars and arrays

$self = basename($_SERVER['SCRIPT_NAME']);

$author_table = 'authors';

$author_book_table = 'author_book';



// author arrays

$first_names = array();

$last_names = array();



$first_name = '';

$last_name = '';

$rec_id = 0;

$result = '';

$comment = '';

$submit = '';

$con = '';



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



// determine what the user selected



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



//==================================================================

// If User selected 'Save Record' then update author record



if ($submit == 'Save Record')

{

    $rec_id = isset($_POST['rec_id']) ? $_POST['rec_id'] : 0;

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

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



    // clean data for db entry



    $first_name = mysqli_real_escape_string($con, $first_name);

    $last_name = mysqli_real_escape_string($con, $last_name);



    if (ctype_digit($rec_id) and $rec_id > 0)    // clean -- make sure $rec_id is a number

    {

        $query = "UPDATE $author_table SET first_name='$first_name', last_name='$last_name' WHERE id='$rec_id' ";

        $comment = "Could not save $author_table records: $query";

        mysqli_query($con, $query) or die(report($comment, mysqli_error($con), __LINE__, __FILE__));

    }

}



//==================================================================

//  If User selected 'Add record' then create a new record in author table



if ($submit == 'Add Record')

{

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

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



    // clean data



    $first_name = mysqli_real_escape_string($con, $first_name);

    $last_name = mysqli_real_escape_string($con, $last_name);



    $query = "INSERT INTO $author_table (first_name, last_name) VALUES ('$first_name', '$last_name') ";

    $comment = "Could not insert $author_table records: $query";

    mysqli_query($con, $query) or die(report($comment, mysqli_error($con), __LINE__, __FILE__));

}



//==================================================================

// If User selected 'Delete record'

// then must Delete the Author AND any reference to the Author in author_book table



if ($submit == 'Delete Record')

{

    $rec_id = isset($_POST['rec_id']) ? $_POST['rec_id'] : 0;

    if (ctype_digit($rec_id) and $rec_id > 0)    // clean -- make sure $rec_id is a number

    {



        // delete all records where the author is listed

        $query = "DELETE FROM $author_book_table WHERE author_id= '$rec_id' ";

        $comment = "Could not delete $author_book_table records: $query";

        mysqli_query($con, $query) or die(report($comment, mysqli_error($con), __LINE__, __FILE__));



        // optimize the table -- do this after a delete

        $query = "OPTIMIZE TABLE $author_book_table ";

        $comment = "Could not optmize $author_book_table records: $query";

        mysqli_query($con, $query) or die(report($comment, mysqli_error($con), __LINE__, __FILE__));



        // delete the author

        $query = "DELETE FROM $author_table WHERE id = '$rec_id' ";

        $comment = "Could not delete $author_table records: $query";

        mysqli_query($con, $query) or die(report($comment, mysqli_error($con), __LINE__, __FILE__));



        // optimize the table -- do this after a delete

        $query = "OPTIMIZE TABLE $author_table ";

        $comment = "Could not optmize $author_table records: $query";

        mysqli_query($con, $query) or die(report($comment, mysqli_error($con), __LINE__, __FILE__));



    }

}



//==================================================================

// At this point we have finished altering all table(s)

// Now read back in all the Author records from the current table

//==================================================================



$query = "SELECT * FROM $author_table WHERE id > '0' ";

$comment = "Could not get $author_table records: $query";

$result = mysqli_query($con, $query) or die(report($comment, mysqli_error($con), __LINE__, __FILE__));



while ($row = mysqli_fetch_array($result))

{

    $id = $row['id'];

    $first_names[$id] = htmlentities($row['first_name']);

    $last_names[$id] = htmlentities($row['last_name']);



    // set current record to the last record from ALL records

    // can be overridden by 'Select Record' user selection



    $rec_id = $id;

    $first_name = $first_names[$id];

    $last_name = $last_names[$id];

}



//==================================================================

// User has selected a Record for edit/save operation

// Make the selected record the current record

//==================================================================



if ($submit == 'Select Record')

{

    $rec_id = isset($_POST['choice']) ? $_POST['choice'] : 1;

    if (!ctype_digit($rec_id))    // clean -- make sure $rec_id is a number

    {

        $rec_id = 1;

    }



    $query = "SELECT * FROM $author_table WHERE id = '$rec_id' ";

    $comment = "Could not get a specific $author_table records: $query";

    $result = mysqli_query($con, $query) or die(report($comment, mysqli_error($con), __LINE__, __FILE__));

    $row = mysqli_fetch_array($result);



    // set current record



    $rec_id = $row['id'];    // we already have it, but pull it anyway

    $first_name = htmlentities($row['first_name']);

    $last_name = htmlentities($row['last_name']);

}



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



include('includes/header.php');





// Now show data

?>

    <h2 class="center">CRUD Records in Authors Table</h2>



    <form action="<?php echo($self); ?>" method="post">

        <table class="full">

            <tr>

                <th colspan=4 class="header1">

                    ALL RECORDS

                </th>

            </tr>

            <tr class="header2">

                <th></th>

                <th>ID</th>

                <th>First Name</th>

                <th>Second Name</th>

            </tr>



            <?php

            $i = 0;

            foreach ($first_names as $key => $value)

            {

                ?>



                <tr class="row<?php echo(++$i & 1); ?>">

                    <td class="w5">

                        <input type="radio" name="choice"

                               value="<?php echo($key); ?>" <?php if ($key == $rec_id)

                        {

                            echo('CHECKED');

                        } ?>>

                    </td>

                    <td class="w5 center yellow">

                        <?php echo($key); ?>

                    </td>

                    <td class="w30">

                        <?php echo($first_names[$key]); ?>

                    </td>

                    <td class="w30">

                        <?php echo($last_names[$key]); ?>

                    </td>

                </tr>



                <?php

            }

            ?>



        </table>



        <div class="clear">

            &nbsp;

        </div>



        <table class="full">

            <tr>

                <th colspan=2 class="header1">

                    Current Selected Record

                </th>

            </tr>

            <tr class="header2">

                <th>Fields</th>

                <th>Values</th>

            </tr>

            <tr>

                <td class="right">

                    ID:

                </td>

                <td class="left">

                    <input type="text" size="5" name="rec_id" value="<?php echo($rec_id); ?>" READONLY

                           class="bold noborder">

                    <span class="red bold"> &lt;-- You cannot edit this ID (This is generated via autoincrement) </span>

                </td>

            </tr>

            <tr>

                <td class="right">

                    First Name:

                </td>

                <td class="left">

                    <input type="text" size="60" name="first_name" value="<?php echo($first_name); ?>">

                </td>

            </tr>

            <tr>

                <td class="right">

                    Last Name:

                </td>

                <td class="left">

                    <input type="text" size="60" name="last_name" value="<?php echo($last_name); ?>">

                </td>

            </tr>

        </table>

        <br>

        <input type="submit" name="submit" value="Select Record">

        <input type="submit" name="submit" value="Save Record">

        <input type="submit" name="submit" value="Add Record">

        <input type="submit" name="submit" value="Delete Record">

    </form>



<?php

include('includes/footer.php');

?>
Back