Leaderboard
Popular Content
Showing content with the highest reputation on 08/22/2018 in all areas
-
Check out our new Interactive Web Developer course (created in 2016) that covers this, and much more: shop.killervideostore.com This is a revised version of my previous tutorial (http://www.killersites.com/community/index.php?/topic/1969-basic-php-system-vieweditdeleteadd-records/) which uses MySQLi rather than regular MySQL to connect to the database. MySQLi, often called MySQL Improved, has several advantages over regular MySQL, including support for prepared statements (which helps prevent SQL injection, a common security issue) and object-oriented code. I've also provided a modified view.php file that shows one way to do basic pagination. I have also recorded a 8 part video tutorial (a bit over an an hour worth of video) showing how to build this system and explaining it as I go. It's available in the KillerSites University (http://www.webmentor.org - subscription required) under PHP > PHP CRUD Videos. --- (Anyone with PHP knowledge is welcome to comment on the code. If there are issues I haven't noticed, please let me know. Do realize that it is intended for beginners, so I didn't want to do anything too advanced that might lead to confusion. Yes, I realize I could use OOP, or could separate some of these out into methods, etc. etc.) OK... Here's some code for you to play with. It's a basic system that allows you to: -- view existing records -- edit existing records -- delete existing records -- add new records Online demo: http://www.falkencreative.com/forum/records-mysqli/view.php Basically, just imagine that you are in charge of a sports team, and you want to keep a list of all your player's contact information. The code I've created could be a starting point for that (it only includes fields for their first name/last name, but could obviously could be expanded to use more fields). This is just a basic starting point for projects that require view/edit/delete functionality. I know it may seem a lot to understand at first, but read all the comments in the code -- I try to explain what I am doing step by step. I'm also happy to help with any questions (please post questions in a new topic.) How to create a system that allows a user to add/edit/remove data in a database seems to be a commonly asked topic, so I may adapt this into an actual tutorial at some point in the future. DATABASE: -- You'll need to create a database (I named mine 'records' but it can be changed) using PHPMyAdmin -- Save the included sql file on your desktop as a .txt file -- Once you've created the database, make sure the database is selected, then click the "import" tab -- Select the .txt file on your desktop, and import it into your database. PHPMyAdmin will create all of the necessary tables/import some test data for you to play with SQL file: -- -- Table structure for table `players` -- CREATE TABLE `players` ( `id` int(11) NOT NULL auto_increment, `firstname` varchar(32) NOT NULL, `lastname` varchar(32) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; -- -- Dumping data for table `players` -- INSERT INTO `players` VALUES(1, 'Bob', 'Baker'); INSERT INTO `players` VALUES(2, 'Tim', 'Thomas'); INSERT INTO `players` VALUES(3, 'Rachel', 'Roberts'); INSERT INTO `players` VALUES(4, 'Sam', 'Smith'); Save these php files all in the same folder in a place where you can run them using your server (I'm assuming you are using something like WAMP for the server? I'm not sure if Dreamweaver includes something like that by default.) connect-db.php <?php // server info $server = 'localhost'; $user = 'root'; $pass = 'root'; $db = 'records2'; // connect to the database $mysqli = new mysqli($server, $user, $pass, $db); // show errors (remove this line if on a live site) mysqli_report(MYSQLI_REPORT_ERROR); ?> view.php (non-paginated -- will just display one long list of members) <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd"> <html> <head> <title>View Records</title> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> </head> <body> <h1>View Records</h1> <p><b>View All</b> | <a href="view-paginated.php">View Paginated</a></p> <?php // connect to the database include('connect-db.php'); // get the records from the database if ($result = $mysqli->query("SELECT * FROM players ORDER BY id")) { // display records if there are records to display if ($result->num_rows > 0) { // display records in a table echo "<table border='1' cellpadding='10'>"; // set table headers echo "<tr><th>ID</th><th>First Name</th><th>Last Name</th><th></th><th></th></tr>"; while ($row = $result->fetch_object()) { // set up a row for each record echo "<tr>"; echo "<td>" . $row->id . "</td>"; echo "<td>" . $row->firstname . "</td>"; echo "<td>" . $row->lastname . "</td>"; echo "<td><a href='records.php?id=" . $row->id . "'>Edit</a></td>"; echo "<td><a href='delete.php?id=" . $row->id . "'>Delete</a></td>"; echo "</tr>"; } echo "</table>"; } // if there are no records in the database, display an alert message else { echo "No results to display!"; } } // show an error if there is an issue with the database query else { echo "Error: " . $mysqli->error; } // close database connection $mysqli->close(); ?> <a href="records.php">Add New Record</a> </body> </html> view-paginated.php <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd"> <html> <head> <title>View Records</title> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> </head> <body> <h1>View Records</h1> <?php // connect to the database include('connect-db.php'); // number of results to show per page $per_page = 3; // figure out the total pages in the database if ($result = $mysqli->query("SELECT * FROM players ORDER BY id")) { if ($result->num_rows != 0) { $total_results = $result->num_rows; // ceil() returns the next highest integer value by rounding up value if necessary $total_pages = ceil($total_results / $per_page); // check if the 'page' variable is set in the URL (ex: view-paginated.php?page=1) if (isset($_GET['page']) && is_numeric($_GET['page'])) { $show_page = $_GET['page']; // make sure the $show_page value is valid if ($show_page > 0 && $show_page <= $total_pages) { $start = ($show_page -1) * $per_page; $end = $start + $per_page; } else { // error - show first set of results $start = 0; $end = $per_page; } } else { // if page isn't set, show first set of results $start = 0; $end = $per_page; } // display pagination echo "<p><a href='view.php'>View All</a> | <b>View Page:</b> "; for ($i = 1; $i <= $total_pages; $i++) { if (isset($_GET['page']) && $_GET['page'] == $i) { echo $i . " "; } else { echo "<a href='view-paginated.php?page=$i'>$i</a> "; } } echo "</p>"; // display data in table echo "<table border='1' cellpadding='10'>"; echo "<tr> <th>ID</th> <th>First Name</th> <th>Last Name</th> <th></th> <th></th></tr>"; // loop through results of database query, displaying them in the table for ($i = $start; $i < $end; $i++) { // make sure that PHP doesn't try to show results that don't exist if ($i == $total_results) { break; } // find specific row $result->data_seek($i); $row = $result->fetch_row(); // echo out the contents of each row into a table echo "<tr>"; echo '<td>' . $row[0] . '</td>'; echo '<td>' . $row[1] . '</td>'; echo '<td>' . $row[2] . '</td>'; echo '<td><a href="records.php?id=' . $row[0] . '">Edit</a></td>'; echo '<td><a href="delete.php?id=' . $row[0] . '">Delete</a></td>'; echo "</tr>"; } // close table> echo "</table>"; } else { echo "No results to display!"; } } // error with the query else { echo "Error: " . $mysqli->error; } // close database connection $mysqli->close(); ?> <a href="records.php">Add New Record</a> </body> </html> </html> records.php (create a new record/edit existing records) <?php /* Allows the user to both create new records and edit existing records */ // connect to the database include("connect-db.php"); // creates the new/edit record form // since this form is used multiple times in this file, I have made it a function that is easily reusable function renderForm($first = '', $last ='', $error = '', $id = '') { ?> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd"> <html> <head> <title> <?php if ($id != '') { echo "Edit Record"; } else { echo "New Record"; } ?> </title> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> </head> <body> <h1><?php if ($id != '') { echo "Edit Record"; } else { echo "New Record"; } ?></h1> <?php if ($error != '') { echo "<div style='padding:4px; border:1px solid red; color:red'>" . $error . "</div>"; } ?> <form action="" method="post"> <div> <?php if ($id != '') { ?> <input type="hidden" name="id" value="<?php echo $id; ?>" /> <p>ID: <?php echo $id; ?></p> <?php } ?> <strong>First Name: *</strong> <input type="text" name="firstname" value="<?php echo $first; ?>"/><br/> <strong>Last Name: *</strong> <input type="text" name="lastname" value="<?php echo $last; ?>"/> <p>* required</p> <input type="submit" name="submit" value="Submit" /> </div> </form> </body> </html> <?php } /* EDIT RECORD */ // if the 'id' variable is set in the URL, we know that we need to edit a record if (isset($_GET['id'])) { // if the form's submit button is clicked, we need to process the form if (isset($_POST['submit'])) { // make sure the 'id' in the URL is valid if (is_numeric($_POST['id'])) { // get variables from the URL/form $id = $_POST['id']; $firstname = htmlentities($_POST['firstname'], ENT_QUOTES); $lastname = htmlentities($_POST['lastname'], ENT_QUOTES); // check that firstname and lastname are both not empty if ($firstname == '' || $lastname == '') { // if they are empty, show an error message and display the form $error = 'ERROR: Please fill in all required fields!'; renderForm($firstname, $lastname, $error, $id); } else { // if everything is fine, update the record in the database if ($stmt = $mysqli->prepare("UPDATE players SET firstname = ?, lastname = ? WHERE id=?")) { $stmt->bind_param("ssi", $firstname, $lastname, $id); $stmt->execute(); $stmt->close(); } // show an error message if the query has an error else { echo "ERROR: could not prepare SQL statement."; } // redirect the user once the form is updated header("Location: view.php"); } } // if the 'id' variable is not valid, show an error message else { echo "Error!"; } } // if the form hasn't been submitted yet, get the info from the database and show the form else { // make sure the 'id' value is valid if (is_numeric($_GET['id']) && $_GET['id'] > 0) { // get 'id' from URL $id = $_GET['id']; // get the recod from the database if($stmt = $mysqli->prepare("SELECT * FROM players WHERE id=?")) { $stmt->bind_param("i", $id); $stmt->execute(); $stmt->bind_result($id, $firstname, $lastname); $stmt->fetch(); // show the form renderForm($firstname, $lastname, NULL, $id); $stmt->close(); } // show an error if the query has an error else { echo "Error: could not prepare SQL statement"; } } // if the 'id' value is not valid, redirect the user back to the view.php page else { header("Location: view.php"); } } } /* NEW RECORD */ // if the 'id' variable is not set in the URL, we must be creating a new record else { // if the form's submit button is clicked, we need to process the form if (isset($_POST['submit'])) { // get the form data $firstname = htmlentities($_POST['firstname'], ENT_QUOTES); $lastname = htmlentities($_POST['lastname'], ENT_QUOTES); // check that firstname and lastname are both not empty if ($firstname == '' || $lastname == '') { // if they are empty, show an error message and display the form $error = 'ERROR: Please fill in all required fields!'; renderForm($firstname, $lastname, $error); } else { // insert the new record into the database if ($stmt = $mysqli->prepare("INSERT players (firstname, lastname) VALUES (?, ?)")) { $stmt->bind_param("ss", $firstname, $lastname); $stmt->execute(); $stmt->close(); } // show an error if the query has an error else { echo "ERROR: Could not prepare SQL statement."; } // redirec the user header("Location: view.php"); } } // if the form hasn't been submitted yet, show the form else { renderForm(); } } // close the mysqli connection $mysqli->close(); ?> delete.php <?php // connect to the database include('connect-db.php'); // confirm that the 'id' variable has been set if (isset($_GET['id']) && is_numeric($_GET['id'])) { // get the 'id' variable from the URL $id = $_GET['id']; // delete record from database if ($stmt = $mysqli->prepare("DELETE FROM players WHERE id = ? LIMIT 1")) { $stmt->bind_param("i",$id); $stmt->execute(); $stmt->close(); } else { echo "ERROR: could not prepare SQL statement."; } $mysqli->close(); // redirect user after delete is successful header("Location: view.php"); } else // if the 'id' variable isn't set, redirect the user { header("Location: view.php"); } ?>1 point
-
Hello everyone. I have been doing html for a year now, I am going through the JavaScript Beginners course here. I am back in school to get my bachelors degree and am a junior there. I am taking the C# class and working on my final project. I couldn't create an App on my own with out having to research everything online, but I do understand programming a lot better now. I am taking this course online at my school and the book used is very confusing. I like the videos here and the explanations given. I hope to get a solid understanding of the basics of programming and work on Web Dev on the side. I currently work for a large telecom company and install, configure, and maintain phone systems for small companies. Digital and IP PBX systems. I have been in the telecom industry since 1998 and would like to expand my knowledge into the coding world. Thanks!1 point