This is a revised version of my previous tutorial (http://www.killersit...eteadd-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.killersites.com/university - 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

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

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

I just copy/pasted the text, but the table appears and the buttons are in blue in the browser.

My host does have PHPmyAdmin and I also made the same database 'records' with table 'players'. So that's not the problem. Another difference is that my local host is running Xamp with PHP5.3.1 and my host has PHP5.2.4. I don't know if that matters.

I guess in the "view-paginated.php" the link to: "edit.php" should be "records.php" ?

I suggest to include in the delete function to include "Are you sure?" to avoid accidently lost of records.

Correct - that should be "records.php" - I've updated the code above.

Yes, a more fully functional system might include some sort of confirmation functionality to prevent records from being accidentally deleted. I was trying to keep this system as simple as possible, so I haven't included everything.

the $row[0] is a variable that holds the id of the record, creating a url that includes the id, like this: "records.php?id=12". On the records page, you can use $_GET[] to get the id and know which record you want to edit.

Great little tutorial, was just what i needed. How would you do pagination for big amounts of information, like you for instance do on this very forum.
(Where you don't necessarily have every page from 1 to 100 listed, but only the first couple of pages and then the jump to last button?)

Im sorry if you have already covered this in one of your video tutorials and i just missed it.

Great little tutorial, was just what i needed. How would you do pagination for big amounts of information, like you for instance do on this very forum.(Where you don't necessarily have every page from 1 to 100 listed, but only the first couple of pages and then the jump to last button?)

Im sorry if you have already covered this in one of your video tutorials and i just missed it.

It would just be a matter of counting the total number of pages and if it was over a certain number, choosing to only display a limited number of pagination items. I don't believe it's something that I covered, but shouldn't be that hard to implement with the code I've provided.

Hi, I use some text to be written in the mysql-database that contains amongst others ä, ü ö etc., but these are not saved that way?In your script you use the utf-8 (<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>) which should show these characters as they are, but it does not? How can I change this?

This looks good, nice work ben. Do you think i could take the view code and put it into a class. Then call the class to display a database?

To be honest, I'd handle this code very differently, now that I have had more experience with PHP. I would actually split this up into multiple files so it follows the MVC pattern, splitting it up into a file for the model (which would control all access to the database), view (which would probably be a couple different files for the different possible views) and controller (which would handle choosing when to access the database and which views to display.)

I'm new to php and I'm having some trouble getting your tutorial working.

the view-paginated screen isn't working, could be my initial php setup that is at fault. Checked phpinfo() and from what I can see everything is setup.

Here is a snippet from my log.

[06-Mar-2012 18:08:59] PHP Warning: include(connect-db.php) [<a href='function.include'>function.include</a>]: failed to open stream: No such file or directory in C:\Program Files (x86)\Apache Software Foundation\Apache2.2\htdocs\ttt\view-paginated.php on line 13

Great tutorial. I m using it right now for my project. Actually I replaced the text boxes of First and last name by TEXTAREA. Actually I want to input information like one page or more. Bt I am unable to do it. Please help me out. I edited Records.php to replace textboxes by textarea. I am able to insert small amonut of data like 2-3 line. But when I am trying insert more its not happening. I am not able to Insert and Edit the large amount of data. Please help me out If anyone knows how to do it. I attached the Edited records.php file. Other files are use as its is.

The "varchar(32)" indicates the type of the column in the database, and it's currently limited to 32 characters. I would suggest using PHPMyAdmin or similar to modify the database, changing those "varchar" columns to use a "text" type instead that doesn't have character limits.

Attached Files

Hi Ben,I am totally new to php & found this which has helped me greatly to understand it.Thanks.I have used your MySQL first example in MySQL basic to see how it all works but am having issues with the "delete" & the "edit".I was hoping you could help me with it?

The "delete" is just not doing anything when it is clicked.

<?php
// connect to the database
include('connect-db.php');
// check if the 'id' variable is set in URL, and check that it is valid
if (isset($_GET['cust_no']) && is_numeric($_GET['cust_no']))
{
// get id value
$cust_no = $_GET['cust_no'];
// delete the entry
$result = mysql_query("DELETE FROM customer WHERE id=$cust_no")
or die(mysql_error());
// redirect back to the view page header("Location: view.php");
}
else
// if id isn't set, or isn't valid, redirect back to view page
{
header("Location: view.php");
}
?>

In the "edit.php" I am getting a:Parse error: syntax error, unexpected T_ELSE on line 96, which would appear to be the last "else" in the code below.(Hope I have laid this out right & in the proper order, if not, my appologies.the table is: customer.the elements of the table are: cust_no, name,address, phone_no.

To help you out with deleting a record, I would probably need to see your view file. Most likely the link to the delete file is incorrect and doesn't include the right "cust_no" in the URL.

For the edit record, I think this line is the problem:

// get the 'id' value from the URL (if it exists), making sure that it is valid (checking that it is numeric/larger than 0) if (isset($_GET['cust_no']) && is_numeric($_GET['cust_no']) && $_GET['cust_no'] > 0)

Looks like it is one long line, and the "//" at the start of the line means that it is all commented out. You need to have the "if" statement on its own line, like this:

// get the 'id' value from the URL (if it exists), making sure that it is valid (checking that it is numeric/larger than 0)
if (isset($_GET['cust_no']) && is_numeric($_GET['cust_no']) && $_GET['cust_no'] > 0)

also, I believe you have an extra, unnecessary "}" at the end of the file.