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
49	Joe	Downey
50	Tyler	Surline
51	Allie	Hunt
52	Luis Rodrigo	Dominguez Tapia
53	Natalie	Bachman
54	Catherine	Moiles
55	Carla	Pitawanakwat
56	Kali	Jury
57	Gabriel	LaMore
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