CRUD with PHP and MySQL

CRUD with PHP and MySQL

In this tutorial we learn create, read, and update data Using PHP and MySQL. The programmers called it CRUD. CRUD means CREATE, READ, UPDATE and DELETE.

Now we will focus on how to create new database record with PHP and MySQL.

It is much better if you’ll create a separate file for connecting PHP to MySQL Database and just include it to any of your PHP files that needs it.

For example, I will create a file named “config.php” (Configuration and then opening of the database) in our phpcrud folder and inside this file are the following codes:

<?php
$dbhost = "localhost";

$dbuser = "root";

$dbpass = "";

$dbname = "phpcrud";

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

mysql_select_db($dbname) or die ('Unable to select database!');</pre>

Just to explain the above code…$dbhost = “localhost”;

-$dbhost is the MySQL server that you are using. If you’re using your computer as your testing server, its value is usually “localhost”.

$dbuser = “root”;

$dbpass = “”;

-These are the MySQL username and password that you have created or configured. The “root” and blank (“”) password are the usual settings when you haven’t configured it in your localhost. But you can changed them. For example, you are using PHPMyAdmin to manage your database, just find the privileges link.

$dbname = “phpcrud “;

-It is the name of your database.

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die (‘Error connecting to mysql’);

-It is the syntax for connecting to MySQL database. mysql_connect($dbhost, $dbuser, $dbpass) will do but you may add the or die (‘Error connecting to mysql’) for debugging purposes. The $conn variable is used for closing the database connection later.

mysql_select_db($dbname) or die (‘Unable to select database!’);

-This is the command for selecting the database you want to be connected with. In our example above, the $dbname (database name) is “phpcrud”.

Sample table and data, you can run this script in your phpcrud database:

CREATE TABLE IF NOT EXISTS `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `firstname` varchar(32) NOT NULL,
 `lastname` varchar(32) NOT NULL,
 `username` varchar(32) NOT NULL,
 `password` varchar(32) NOT NULL,
 `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=52 ;

INSERT INTO `users` (`id`, `firstname`, `lastname`, `username`, `password`, `modified`) VALUES
(28, 'John', 'Dalisay', 'john', 'john123', '2011-09-23 14:36:19'),
(39, 'aaa', 'aaa', 'aaa', 'aaa', '2011-09-23 15:44:04'),
(40, 'bbb', 'bbb', 'bbb', 'bbb', '2011-09-23 15:44:13'),
(41, 'ccc', 'ccc', 'ccc', 'ccc', '2011-09-23 15:44:32'),
(46, 'XXXDDD', '', '', '', '2011-09-23 17:13:15'),
(47, 'www', 'www', 'www', 'www', '2011-09-23 17:21:38'),
(48, 'HEHEHEHEHE', '', '', '', '2011-09-23 18:50:10'),
(49, 'www', 'www', 'www', 'www', '2011-09-23 19:28:24'),
(50, 'EEEE', 'EEEE', 'EEEE', 'EEEE', '2011-09-24 05:01:36'),
(51, 'asdf', 'asdf', 'asdf', 'asdf', '2011-10-04 18:44:19');</pre>

Our insert.php will have the following code:

<html>
<head>
<title>Create - PHP CRUD</title>
</head>
<body>

<?php
if(isset($_POST['save'])){
//include database configuration

include 'config.php';

extract($_REQUEST);

//sql insert statement

$query=mysql_query("insert into users SET firstname='$firstname', lastname='$lastname', username='$username', password='$password'") or die(mysql_error());

//insert query to the database

if($query){

//if successful query

echo "New record was saved.";

}

}
?>

<!--we have our html form here where user information will be entered-->

<form action='#' method='post' border='0'>
<table>
<tr>
<td>Firstname</td>
<td><input type='text' name='firstname' /></td>
</tr>
<tr>
<td>Lastname</td>
<td><input type='text' name='lastname' /></td>
</tr>
<tr>
<td>Username</td>
<td><input type='text' name='username' /></td>
</tr>
<tr>
<td>Password</td>
<td><input type='password' name='password' /></td>
<tr>
<td></td>
<td>
<input type='submit' value='Save' name="save" />
</td>
</tr>
</table>
</form>
</body>
</html>

When the form is filled up and submitted, it will look like this:

phpcrud1

Now we will simply display data from MySQL database to HTML table with the help of PHP.

Our show_data.php will have the following code:

<?php

//include database configuration

include 'config.php';

//selecting records

$sql="select firstname, lastname, username from users";

//query the database

$rs=mysql_query($sql) or die($sql.">>".mysql_error());

//count how many records found

$num=mysql_num_rows($rs);

if($num>0){ //check if more than 0 record found

?>

<table border='1'>

<tr>

<th>Firstname</th>

<th>Lastname</th>

<th>Username</th>

</tr>

<?php

//retrieve our table contents

while($row=mysql_fetch_array($rs)){

//extract row

//this will make $row['firstname'] to

//just $firstname only

extract($row);

//creating new table row per record

?>

<tr>

<td><?php echo $firstname; ?></td>

<td><?php echo $lastname; ?></td>

<td><?php echo $username; ?></td>

</tr>

<?php

}

?>

</table>

<?php

}else{ //if no records found

echo "No records found.";

}

?>

Our output will look like:
phpcrud2
Now we’re going to do a code that update a record from our MySQL database using PHP.

First, we have to display our data to a table and put a new column called “action”. On the action column we’re gonna have the edit link for each record. Our update.php will have the following code:

<?php

//include database configuration

include 'config.php';

//selecting records

$query=mysql_query("select id, firstname, lastname, username from users");

//count how many records found

$num=mysql_num_rows($query);

if($num>0){ //check if more than 0 record found

?>

<table border='1'>

<tr>

<th>Firstname</th>

<th>Lastname</th>

<th>Username</th>

<th>Action</th>

</tr>

<?php

//retrieve our table contents

while($row=mysql_fetch_array($query)){

//extract row

//this will make $row['firstname'] to

//just $firstname only

extract($row);

//creating new table row per record

?>

<tr>

<td><?php echo $firstname; ?></td>

<td><?php echo $lastname; ?></td>

<td><?php echo $username; ?></td>

<!--we will have the edit link here-->

<td>

<a href="edit.php?id=<?php echo $id; ?>">Edit</a>

</td>

</tr>

<?php

}

?>

</table>

<?php

}else{ //if no records found

echo "No records found.";

}

?>

The code above should look like this on our browser:
phpcrud3

Then once the user clicked an edit link for a record, the user will be redirected to the edit screen which is our edit.php, we’ll have the following code:

<?php

if(isset($_REQUEST['id'])){

include('config.php');

if(isset($_REQUEST['edit'])){

extract($_REQUEST);

//update the record if the form was submitted

$query=mysql_query("update users set firstname='$firstname', lastname='$lastname', username='$username', password='$password' where id='$id'") or die(mysql_error());

if($query){

//this will be displayed when the query was successful

echo "<div>Record was edited.</div>";

}

}

$id=$_REQUEST['id'];

//this query will select the user data which is to be used to fill up the form

$query=mysql_query("select * from users where id='$id'") or die(mysql_error());

$num=mysql_num_rows($query);

//just a little validation, if a record was found, the form will be shown

//it means that there's an information to be edited

if($num>0){

$row=mysql_fetch_assoc($query);

extract($row);

?>

<!--we have our html form here where new user information will be entered-->

<form action='' method='post' border='0'>

<table>

<tr>

<td>Firstname</td>

<td><input type='text' name='firstname' value='<?php echo $firstname;  ?>' /></td>

</tr>

<tr>

<td>Lastname</td>

<td><input type='text' name='lastname' value='<?php echo $lastname;  ?>' /></td>

</tr>

<tr>

<td>Username</td>

<td><input type='text' name='username'  value='<?php echo $username;  ?>' /></td>

</tr>

<tr>

<td>Password</td>

<td><input type='password' name='password'  value='<?php echo $password;  ?>' /></td>

<tr>

<td></td>

<td>

<!-- so that we could identify what record is to be updated -->

<input type='hidden' name='id' value='<?php echo $id ?>' />

<!-- we will set the action to edit -->

<input type='submit' value='Edit' name="edit" />

</td>

</tr>

</table>

</form>

<?php

}else{

echo "<div>User with this id is not found.</div>";

}

}

else{

echo "<div> You are not authorized to view this page";

}

echo "<a href='index.php'>Back To List</a>";

?>

The code above should look like this:
phpcrud4

Now we’re gonna do a code that can delete record from your database.

This code uses JavaScript as prompt to the user if he really want to delete certain record. A pop up will appear once the user clicked the delete link. On our index.php, we’ll have the following code:

<html>

<head>

<title>Delete Record</title>

</head>

<body>

<?php

//include database configuration

include 'config.php';

//check if an action was set, we use GET this time since we get the action data from the url

isset($_GET['action']) ? $action=$_GET['action'] : $action="";

if($action=='delete'){ //if the user clicked ok, run our delete query

$id=$_REQUEST['id'];

$query = mysql_query("DELETE FROM users WHERE id='$id'") or die(mysql_error());

if($query){

//this will be displayed when the query was successful

echo "<div>Record was deleted.</div>";

}

}

//selecting records

$query2=mysql_query("select * from users") or die(mysql_error());

//count how many records found

$num=mysql_num_rows($query2);

if($num>0){ //check if more than 0 record found

echo "<table border='1'>";//start table

//creating our table heading

echo "<tr>";

echo "<th>Firstname</th>";

echo "<th>Lastname</th>";

echo "<th>Username</th>";

echo "<th>Action</th>"; //we're gonna add this column for delete action

echo "</tr>";

//retrieve our table contents

while($row=mysql_fetch_array($query2)){

//extract row

//this will make $row['firstname'] to

//just $firstname only

extract($row);

//creating new table row per record

echo "<tr>";

echo "<td>{$firstname}</td>";

echo "<td>{$lastname}</td>";

echo "<td>{$username}</td>";

//we will have the delete link here, you can also put your edit link here, but for this tutorial we will just include the delete link

echo "<td>";

echo "<a href='#' onclick='delete_user( {$id} );'>Delete</a>";

echo "</td>";

echo "</tr>";

}

echo "</table>";//end table

}else{ //if no records found

echo "No records found.";

}

?>

<script type='text/javascript'>

function delete_user( id ){

//this script helps us to

var answer = confirm('Are you sure?');

if ( answer ){ //if user clicked ok

//redirect to url with action as delete and id to the record to be deleted

window.location = 'index.php?action=delete&id=' + id;

}

}

</script>

</body>

</html>

Our code should look like this on the browser:
phpcrud5

That’s it! Happy coding! :)

About the author

PHP | MVC | Codeignitor | Zend | Yii | Smarty | Android | Laravel Expert Senior Software Developer.I love my job and feel happy to working on new ideas and technologies.

Leave a Comment

Comment (required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Name (required)
Email (required)