dcldesign code

PHP CRUD

Page Discussion

This is a Create Rename Update Delete template to manage a single database table. It displays a table of the database contents that has sortable headings.

  1. Create the database and assign user rights:
    CREATE DATABASE books;
CREATE USER 'drupal'@'localhost' IDENTIFIED BY 'password';
GRANT ALL on drupal.* TO 'drupal'@'localhost';
USE books;
  2. Execute SQL to create the necessary table: 
    CREATE TABLE `books` (
    `id` int(11) NOT NULL auto_increment,
    `title` varchar(100) NOT NULL,
    `author` varchar(100) NOT NULL,
	`owner` varchar(25) NOT NULL,
    `isbn` varchar(13) NOT NULL,
    PRIMARY KEY  (`id`)
);
  3. Create the necessary 8 files in the web server directory:
    1. style.css
    2. sort-table.js
    3. index.php
    4. edit.php
    5. delete.php
    6. config.php
    7. add.php
    8. add.html

style.cssEdit

body {background-color: pink;}
table, th, td, p {
  font-size: 1.5em;
}
table {
  width:100%;
}
button {
  font-size: 1em;
}
input[type=text], input[type=submit] {
  font-size: inherit;
}
a {font-size: 1em;}
table 	{border: 1px solid black; border-collapse: collapse; }
th, td 	{ padding: 2px 5px; border: 1px solid black; }
thead 	{ background: #ddd; }
table#demo2.js-sort-0 tbody tr td:nth-child(1),
table#demo2.js-sort-1 tbody tr td:nth-child(2),
table#demo2.js-sort-2 tbody tr td:nth-child(3),
table#demo2.js-sort-3 tbody tr td:nth-child(4),
table#demo2.js-sort-4 tbody tr td:nth-child(5),
table#demo2.js-sort-5 tbody tr td:nth-child(6),
table#demo2.js-sort-6 tbody tr td:nth-child(7),
table#demo2.js-sort-7 tbody tr td:nth-child(8),
table#demo2.js-sort-8 tbody tr td:nth-child(9),
table#demo2.js-sort-9 tbody tr td:nth-child(10) {
background: #dee;}

sort-table.jsEdit

/**
 * sort-table.js
 * A pure JavaScript (no dependencies) solution to make HTML
 *  Tables sortable
 *
 * Copyright (c) 2013 Tyler Uebele
 * Released under the MIT license.  See included LICENSE.txt
 *  or http://opensource.org/licenses/MIT
 *
 * latest version available at https://github.com/tyleruebele/sort-table
 */

/**
 * Sort the rows in a HTML Table
 *
 * @param Table The Table DOM object
 * @param col   The zero-based column number by which to sort
 * @param dir   Optional. The sort direction; pass 1 for asc; -1 for desc
 * @returns void
 */
function sortTable(Table, col, dir) {
    var sortClass, i;

    // get previous sort column
    sortTable.sortCol = -1;
    sortClass = Table.className.match(/js-sort-\d+/);
    if (null != sortClass) {
        sortTable.sortCol = sortClass[0].replace(/js-sort-/, '');
        Table.className = Table.className.replace(new RegExp(' ?' + sortClass[0] + '\\b'), '');
    }
    // If sort column was not passed, use previous
    if ('undefined' === typeof col) {
        col = sortTable.sortCol;
    }

    if ('undefined' !== typeof dir) {
        // Accept -1 or 'desc' for descending.  All else is ascending
        sortTable.sortDir = dir == -1 || dir == 'desc' ? -1 : 1;
    } else {
        // sort direction was not passed, use opposite of previous
        sortClass = Table.className.match(/js-sort-(a|de)sc/);
        if (null != sortClass && sortTable.sortCol == col) {
            sortTable.sortDir = 'js-sort-asc' == sortClass[0] ? -1 : 1;
        } else {
            sortTable.sortDir = 1;
        }
    }
    Table.className = Table.className.replace(/ ?js-sort-(a|de)sc/g, '');

    // update sort column
    Table.className += ' js-sort-' + col;
    sortTable.sortCol = col;

    // update sort direction
    Table.className += ' js-sort-' + (sortTable.sortDir == -1 ? 'desc' : 'asc');

    // get sort type
    if (col < Table.tHead.rows[Table.tHead.rows.length - 1].cells.length) {
        sortClass = Table.tHead.rows[Table.tHead.rows.length - 1].cells[col].className.match(/js-sort-[-\w]+/);
    }
    // Improved support for colspan'd headers
    for (i = 0; i < Table.tHead.rows[Table.tHead.rows.length - 1].cells.length; i++) {
        if (col == Table.tHead.rows[Table.tHead.rows.length - 1].cells[i].getAttribute('data-js-sort-colNum')) {
            sortClass = Table.tHead.rows[Table.tHead.rows.length - 1].cells[i].className.match(/js-sort-[-\w]+/);
        }
    }
    if (null != sortClass) {
        sortTable.sortFunc = sortClass[0].replace(/js-sort-/, '');
    } else {
        sortTable.sortFunc = 'string';
    }
    // Set the headers for the active column to have the decorative class
    Table.querySelectorAll('.js-sort-active').forEach(function(Node) {
        Node.className = Node.className.replace(/ ?js-sort-active\b/, '');
    });
    Table.querySelectorAll('[data-js-sort-colNum="' + col + '"]:not(:empty)').forEach(function(Node) {
        Node.className += ' js-sort-active';
    });

    // sort!
    var rows = [],
        TBody = Table.tBodies[0];

    for (i = 0; i < TBody.rows.length; i++) {
        rows[i] = TBody.rows[i];
    }
    if ('none' != sortTable.sortFunc) {
        rows.sort(sortTable.compareRow);
    }

    while (TBody.firstChild) {
        TBody.removeChild(TBody.firstChild);
    }
    for (i = 0; i < rows.length; i++) {
        TBody.appendChild(rows[i]);
    }
}

/**
 * Compare two table rows based on current settings
 *
 * @param RowA A TR DOM object
 * @param RowB A TR DOM object
 * @returns {number} 1 if RowA is greater, -1 if RowB, 0 if equal
 */
sortTable.compareRow = function(RowA, RowB) {
    var valA, valB;
    if ('function' != typeof sortTable[sortTable.sortFunc]) {
        sortTable.sortFunc = 'string';
    }
    valA = sortTable[sortTable.sortFunc](RowA.cells[sortTable.sortCol]);
    valB = sortTable[sortTable.sortFunc](RowB.cells[sortTable.sortCol]);

    return valA == valB ? 0 : sortTable.sortDir * (valA > valB ? 1 : -1);
};

/**
 * Strip all HTML, no exceptions
 * @param html
 * @returns {string}
 */
sortTable.stripTags = function(html) {
    return html.replace(/<\/?[a-z][a-z0-9]*\b[^>]*>/gi, '');
};

/**
 * Helper function that converts a table cell (TD) to a comparable value
 * Converts innerHTML to a timestamp, 0 for invalid dates
 *
 * @param Cell A TD DOM object
 * @returns {Number}
 */
sortTable.date = function(Cell) {
    // If okDate library is available, Use it for advanced Date processing
    if (typeof okDate !== 'undefined') {
        var kDate = okDate(sortTable.stripTags(Cell.innerHTML));
        return kDate ? kDate.getTime() : 0;
    } else {
        return (new Date(sortTable.stripTags(Cell.innerHTML))).getTime() || 0;
    }
};

/**
 * Helper function that converts a table cell (TD) to a comparable value
 * Converts innerHTML to a JS Number object
 *
 * @param Cell A TD DOM object
 * @returns {Number}
 */
sortTable.number = function(Cell) {
    return Number(sortTable.stripTags(Cell.innerHTML).replace(/[^-\d.]/g, ''));
};

/**
 * Helper function that converts a table cell (TD) to a comparable value
 * Converts innerHTML to a lower case string for insensitive compare
 *
 * @param Cell A TD DOM object
 * @returns {String}
 */
sortTable.string = function(Cell) {
    return sortTable.stripTags(Cell.innerHTML).toLowerCase();
};

/**
 * Helper function that converts a table cell (TD) to a comparable value
 *
 * @param Cell A TD DOM object
 * @returns {String}
 */
sortTable.raw = function(Cell) {
    return Cell.innerHTML;
};

/**
 * Helper function that converts a table cell (TD) to a comparable value
 * Captures the last space-delimited token from innerHTML
 *
 * @param Cell A TD DOM object
 * @returns {String}
 */
sortTable.last = function(Cell) {
    return sortTable.stripTags(Cell.innerHTML).split(' ').pop().toLowerCase();
};

/**
 * Helper function that converts a table cell (TD) to a comparable value
 * Captures the value of the first childNode
 *
 * @param Cell A TD DOM object
 * @returns {String}
 */
sortTable.input = function(Cell) {
    for (var i = 0; i < Cell.children.length; i++) {
        if ('object' == typeof Cell.children[i]
            && 'undefined' != typeof Cell.children[i].value
        ) {
            return Cell.children[i].value.toLowerCase();
        }
    }

    return sortTable.string(Cell);
};

/**
 * Helper function that prevents sorting by always returning null
 *
 * @param Cell A TD DOM object
 * @returns null
 */
sortTable.none = function(Cell) {
    return null;
};

/**
 * Return the click handler appropriate to the specified Table and column
 *
 * @param Table Table to sort
 * @param col   Column to sort by
 * @returns {Function} Click Handler
 */
sortTable.getClickHandler = function(Table, col) {
    return function() {
        sortTable(Table, col);
    };
};

/**
 * Attach sortTable() calls to table header cells' onclick events
 * If the table(s) do not have a THead node, one will be created around the
 *  first row
 */
sortTable.init = function() {
    var THead, Tables, Handler;
    if (document.querySelectorAll) {
        Tables = document.querySelectorAll('table.js-sort-table');
    } else {
        Tables = document.getElementsByTagName('table');
    }

    for (var i = 0; i < Tables.length; i++) {
        // Because IE<8 doesn't support querySelectorAll, skip unclassed tables
        if (!document.querySelectorAll && null === Tables[i].className.match(/\bjs-sort-table\b/)) {
            continue;
        }

        // Prevent repeat processing
        if (Tables[i].attributes['data-js-sort-table']) {
            continue;
        }

        // Ensure table has a tHead element
        if (!Tables[i].tHead) {
            THead = document.createElement('thead');
            THead.appendChild(Tables[i].rows[0]);
            Tables[i].insertBefore(THead, Tables[i].children[0]);
        } else {
            THead = Tables[i].tHead;
        }

        // Attach click events to table header
        for (var rowNum = 0; rowNum < THead.rows.length; rowNum++) {
            for (var cellNum = 0, colNum = 0; cellNum < THead.rows[rowNum].cells.length; cellNum++) {
                // Skip headers marked "js-sort-none"
                if (THead.rows[rowNum].cells[cellNum].className.match(/\bjs-sort-none\b/)) {
                    continue;
                }
                // Define which column the header should invoke sorting for
                THead.rows[rowNum].cells[cellNum].setAttribute('data-js-sort-colNum', colNum);
                Handler = sortTable.getClickHandler(Tables[i], colNum);
                window.addEventListener
                    ? THead.rows[rowNum].cells[cellNum].addEventListener('click', Handler)
                    : window.attachEvent && THead.rows[rowNum].cells[cellNum].attachEvent('onclick', Handler);
                colNum += THead.rows[rowNum].cells[cellNum].colSpan;
            }
        }

        // Mark table as processed
        Tables[i].setAttribute('data-js-sort-table', 'true')
    }

    // Add default styles as the first style in head so they can be easily overwritten by user styles
    var element = document.createElement('style');
    document.head.insertBefore(element, document.head.childNodes[0]);
    var sheet = element.sheet;
    sheet.insertRule('table.js-sort-table.js-sort-asc thead tr > .js-sort-active:not(.js-sort-none):after {content: "\\25b2";font-size: 0.7em;padding-left: 3px;line-height: 0.7em;}', 0);
    sheet.insertRule('table.js-sort-table.js-sort-desc thead tr > .js-sort-active:not(.js-sort-none):after {content: "\\25bc";font-size: 0.7em;padding-left: 3px;line-height: 0.7em;}', 0);
};

// Run sortTable.init() when the page loads
window.addEventListener
    ? window.addEventListener('load', sortTable.init, false)
    : window.attachEvent && window.attachEvent('onload', sortTable.init)
    ;

// Shim for IE11's lack of NodeList.prototype.forEach
if (typeof NodeList.prototype.forEach !== "function") {
    NodeList.prototype.forEach = Array.prototype.forEach;
}

index.phpEdit

<?php
//including the database connection file
include_once("config.php");
//fetching data in descending order (lastest entry first)
//$result = mysql_query("SELECT * FROM users ORDER BY id DESC"); // mysql_query is deprecated
$result = mysqli_query($mysqli, "SELECT * FROM books ORDER BY id DESC"); // using mysqli_query instead
?>
<html>
<head>
    <title>Our's Books</title>
    <link rel="stylesheet" href="style.css">
    <script type="text/javascript" src="sort-table.js"></script>
</head>
<body>
    <h1>Our's Books</h1>
    <a href="add.html" style="font-size: 2em;">Add a Book</a><br/><br/>
    <table width='80%' border=0 class="js-sort-table">
	<thead>
        <tr bgcolor='#CCCCCC'>
            <th class="js-sort-text">Title</th>
            <th class="js-sort-text">Author</th>
            <th class="js-sort-text">Owner</th>
            <th class="js-sort-number">ISBN</th>
            <th>Update</th>
        </tr>
	</thead>
        <?php
        //while($res = mysql_fetch_array($result)) { // mysql_fetch_array is deprecated, we need to use mysqli_fetch_array 
        while($res = mysqli_fetch_array($result)) {
            echo "<tr>";
            echo "<td>".$res['title']."</td>";
            echo "<td>".$res['author']."</td>";
            echo "<td>".$res['owner']."</td>";
            echo "<td>".$res['isbn']."</td>";
            echo "<td><a href=\"edit.php?id=$res[id]\">Edit</a> | <a href=\"delete.php?id=$res[id]\" onClick=\"return confirm('Are you sure you want to delete this book?')\">Delete</a></td>";
        }
        ?>
    </table>
</body>
</html>

edit.phpEdit

<?php
// including the database connection file
include_once("config.php");
if(isset($_POST['update']))
{
    $id = $_POST['id'];
    $title=$_POST['title'];
    $author=$_POST['author'];
    $owner=$_POST['owner'];
    $isbn=$_POST['isbn'];
    // checking empty fields
    if(empty($title) || empty($author)) {
        if(empty($title)) {
            echo "<font color='red'>Title can't be blank!</font><br/>";
        }
        if(empty($author)) {
            echo "<font color='red'>Author cannot be blank!</font><br/>";
        }
    } else {
        //updating the table
        $result = mysqli_query($mysqli, "UPDATE books SET title='$title',author='$author',owner='$owner',isbn='$isbn' WHERE id=$id");
        //redirectig to the display page. In our case, it is index.php
        header("Location: index.php");
    }
}
?>
<?php
//getting id from url
$id = $_GET['id'];
//selecting data associated with this particular id
$result = mysqli_query($mysqli, "SELECT * FROM books WHERE id=$id");
while($res = mysqli_fetch_array($result))
{
    $title = $res['title'];
    $author = $res['author'];
    $owner = $res['owner'];
    $isbn = $res['isbn'];
}
?>
<html>
<head>
    <title>Edit Book</title>
    <link rel="stylesheet" href="style.css">
</head>
<body>
    <a href="index.php" style="font-size: 2em;">Home</a>
    <br/><br/>
    <form name="form1" method="post" action="edit.php">
        <table border="0">
            <tr> 
                <td>Title</td>
                <td><input type="text" name="title" value="<?php echo $title;?>"></td>
            </tr>
            <tr> 
                <td>Author</td>
                <td><input type="text" name="author" value="<?php echo $author;?>"></td>
            </tr>
            <tr> 
                <td>Owner</td>
                <td><input type="text" name="owner" value="<?php echo $owner;?>"></td>
            </tr>
			<tr> 
                <td>ISBN</td>
                <td><input type="text" name="isbn" value="<?php echo $isbn;?>"></td>
            </tr>
            <tr>
                <td><input type="hidden" name="id" value=<?php echo $_GET['id'];?>></td>
                <td><input type="submit" name="update" value="Update"></td>
            </tr>
        </table>
    </form>
</body>
</html>

delete.phpEdit

<?php
//including the database connection file
include("config.php");
//getting id of the data from url
$id = $_GET['id'];
//deleting the row from table
$result = mysqli_query($mysqli, "DELETE FROM books WHERE id=$id");
//redirecting to the display page (index.php in our case)
header("Location:index.php");
?>

config.phpEdit

<?php
$databaseHost = 'localhost';
$databaseName = 'books';
$databaseUsername = 'drupal';
$databasePassword = 'password';
$mysqli = mysqli_connect($databaseHost, $databaseUsername, $databasePassword, $databaseName); 
?>

add.phpEdit

<html>
<head>
    <title>Add Book</title>
    <link rel="stylesheet" href="style.css">
</head>
<body>
<?php
//including the database connection file
include_once("config.php");

if(isset($_POST['Submit'])) {
    $title = $_POST['title'];
    $author = $_POST['author'];
    $owner = $_POST['owner'];
    $isbn = $_POST['isbn'];
    // checking empty fields
    if(empty($title) || empty($author) ) {
        if(empty($title)) {
            echo "<font color='red'>You must enter a title!</font><br/>";
        }
        if(empty($author)) {
            echo "<font color='red'>You must enter an author!</font><br/>";
        }
        //link to the previous page
        echo "<br/><a href='javascript:self.history.back();'>Go Back</a>";
    } else {
        // if all the fields are filled (not empty)
        //insert data to database
        $result = mysqli_query($mysqli, "INSERT INTO books(title,author,owner,isbn) VALUES('$title','$author','$owner','$isbn')");
        //display success message
        echo "<font color='green' style='font-size: 2em;'>Book added successfully.";
        echo "<br/><a href='index.php' style='font-size: 2em;'>View Result</a>";
    }
}
?>
</body>
</html>

add.htmlEdit

<html>
<head>
    <title>Add Book</title>
    <link rel="stylesheet" href="style.css">
</head>
<body>
    <a href="index.php" style="font-size: 2em;">Home</a>
    <br/><br/>
    <form action="add.php" method="post" name="form1">
        <table width="25%" border="0">
            <tr> 
                <td>Title</td>
                <td><input type="text" name="title"></td>
            </tr>
            <tr> 
                <td>Author</td>
                <td><input type="text" name="author"></td>
            </tr>
            <tr> 
                <td>Owner</td>
                <td><input type="text" name="owner"></td>
            </tr>
	    <tr> 
                <td>ISBN</td>
                <td><input type="text" name="isbn"></td>
            </tr>
            <tr> 
                <td></td>
                <td><input type="submit" name="Submit" value="Add"></td>
            </tr>
        </table>
    </form>
</body>
</html>
Retrieved from ‘http:///index.php?title=PHP_CRUD&oldid=98’