Creating Database and Updating Data with PHP

PrevMain 
php tutorial part3

This tutorial walks you through the basic functions of inserting, updating, and deleting records from a MySQL database using PHP.

NOTE: Throughout the tutorial, “owner” and “password” will be used as the MySQL username and password. Replace these with whatever username and password you set up when you installed MySQL!

Creating a MySQL Database

First we’re going to need to create a MySQL database and populate it. Access your MySQL database that we installed in Tutorial Part 1 and enter the following commands:

CREATE DATABASE tut_users;
USE tut_users;
CREATE TABLE users (
	username varchar(50),
	password varchar(50),
	email varchar(50)
);

That may seem like gibberish to you, but those are commands that create a MySQL database and create a table within it. You’re not going to need to know the inner workings and nuts and bolts of MySQL for this tutorial, but there are a few things you should know about what we’ve just done. The CREATE DATABASE and USE functions are fairly self-explanatory- we’ve created a database and then switched to using it. The CREATE TABLE function here, however, may need some explanation: we’ve created a table and populated it with three columns: username, password, and email. The varchar statement next to each of those indicates how many characters each entry can hold- 50 should be more than enough for all three fields.

This may seem difficult to grasp, but it will become clearer as we move into practical usage in PHP. For now, let’s make our login script a little more robust and check against the database!

Adding records to our database via PHP

Wait a minute- we can’t check against our database because it’s empty. That’s no good. Let’s create a form that will add users to our database that we can check against.

<?php
if (isset($_POST['submit'])) {
	$username = $_POST["username"];
	$password = $_POST["password"];
	$email = $_POST["email"];
	$dbc = mysqli_connect('localhost', 'owner', 'password', 'tut_users');
	$query = "INSERT INTO users (username, password, email) VALUES ('$username', '$password', '$email')";
	$result = mysqli_query ($dbc, $query);
	mysqli_close($dbc);
	echo "New user $username added!";
}
?>
<html>
<title> Add User Page </title>
<body>
	<form method="post" action="adduser.php">
	<br />
	Username:  <input type="text" size="50" name="username" />
	<br />
	Password: <input type="text" size="50" name="password" />
	<br />
	Email: <input type="text" size="50" name="email" />
	<input type="submit" value="Add User" name="submit" />
	</form>
</body>
</html>

There’s a whole lot of new information here. The mysqli_connect function opens a connection to the database as an object, $dbc; that object is what you use when you want to pass queries or results to the MySQL server. The $query string is the MySQL command that we’re going to enter: you’ll notice that it looks similar in syntax to the MySQL commands we entered before. In this case, we’re inserting the values we got from the form into the table “tut_users” that we created earlier on in the tutorial. We create the string, use the mysql_query function to run the query on the MySQL server, and then close the connection to the database.

While MySQL does close connections on its own, the automatic connection closing isn’t the smartest or most efficient. In general, it’s simply good habit to get used to closing MySQL connections on your own when you’re done with them, as it ensures that connections are open as often as possible.

Save the file as adduser.php in your web directory, and go ahead and run it. You should end up with something that looks like this:

add user form

You’ll find that you’re able to add users, and upon adding users the system echoes the username added. Go ahead and add three or four users- or twenty. When you’ve had your fill of creating users, let’s move on to the next step!

Selecting And Deleting Records From DB

While adding users is great, occasionally we need to delete users: if, for example, an employee is fired and no longer needs access to the database, we can’t have them just sitting around. To delete users, however, we need to first know how many there are and what their usernames are. For that, we’re going to use a SELECT command! Let’s take a look at our code:

<?
if (isset($_POST['submit'])) {
	$username = $_POST['username'];
	$dbc = mysqli_connect('localhost', 'owner', 'password', 'tut_users');
	$query = "DELETE FROM users WHERE username='$username'";
	$result1 = mysqli_query($dbc, $query);
	echo "$username erased!";
	mysqli_close($dbc);
}
?>
<html>
<title> Delete User Page </title>
<body>
	<form method="post" action="deleteuser.php">
	<?
	$dbc = mysqli_connect('localhost', 'owner', 'password', 'tut_users');
	$query = "SELECT username FROM users ";
	$result = mysqli_query ($dbc, $query);

	while ($row = mysqli_fetch_row($result)) {
		echo "<input type =\"radio\" name =\" username\" value =$row[0] /> $row[0]";
		echo "<br />";
	} 
	mysqli_close($dbc);
	?>
	<input type="submit" value="Delete User" name="submit" />
	</form>
</body>
</html>

There’s quite a bit of new information here, so let’s take it in. First off, we’ve introduced two new MySQL commands: SELECT and DELETE. These do what you might expect them to: SELECT selects records from the database to display them, and DELETE deletes records that match a specified filter. You’ll notice the two of them share a similar syntax, and in fact SELECT commands can take a WHERE clause as well: this allows you to create customized MySQL queries to only select certain records from the database (for example, if you only wanted to show users that begin with A or who have a specific name).

We use that WHERE clause to select only the records that we want to delete; in this case, we use the radio button form to send back a selected username to the DELETE query, which only deletes records where the USERNAME column matches the username of the radio button. It’s extremely important to note at this juncture that MySQL transactions are permanent! The moment that query runs, there’s no way to get back whatever row it was that you deleted. Test all delete queries with a SELECT query first; if the SELECT query returns precisely the row you want to delete, you know it’s safe to switch it to a DELETE query.

This goes for all MySQL functions, including INSERT and UPDATE, which we’ll get to in a moment. In general, it’s always a very good idea to keep incremental backups and total backups of MySQL databases, just in case something unforeseen happens- there are few safety nets in MySQL, and there’s not a single DBA or programmer out there who hasn’t accidentally hosed a database with a typo. Make backups- you’ll thank yourself later!

Save the file as deleteuser.php and run it. You’ll get something looking like this:

change password form

Modifying MySQL Records

Suppose Johnson from accounting wants to modify his password- he left it on a sticky-note under his keyboard and now everyone can access our Super Secret database. We could, of course, delete his record and have him create a new account with a new password, but that’s time-consuming and ridiculous. Let’s come up with a change password page instead!

<?php
if (isset($_POST['submit'])) {
	$username = $_POST["username"];
	$oldpass = $_POST["oldpass"];
	$newpass = $_POST["newpass"];
	$dbc = mysqli_connect('localhost', 'owner', 'password', 'tut_users');
	$query = "SELECT password FROM users WHERE username='$username'";
	$result = mysqli_query ($dbc, $query);
	$row = mysqli_fetch_row($result);

	if ($row[0] == $oldpass) {
		$query = "UPDATE users SET password='$newpass' WHERE username='$username'";
		$result = mysqli_query($dbc, $query);
		echo "Password successfully changed!";
	} else {
		echo "Error! Password mismatch.";
	}
	mysqli_close($dbc);
}
?>
<html>
<title> Change Password Page </title>
<body>
	<form method="post" action="changepass.php">
	<br />
	Username: <input type="text" size="50" name="username" />
	<br />
	Old Password:  <input type="text" size="50" name="oldpass" />
	<br />
	New Password: <input type="text" size="50" name="newpass" />
	<br />
	<input type="submit" value="Change Password" name="submit" />
	</form>
</body>
</html>

We’ve introduced a new function here called the UPDATE function. The UPDATE function is syntactically similar to the SELECT and DELETE commands, but slightly different: you put the table name fist and then the field you want to update. As with the DELETE command, you need to be absolutely sure that your script is doing what you need it to do! Nobody wants a typo to turn their “Change Password” page into “Change everybody’s username to Johnson’s password” page.

Similarly, we don’t want anyone to be able to change anybody else’s password. As a result, we have username and password fields; the user inputs the username whose password they want to change, and if the password matches the current password on file then the system allows the change. We do this through the use of a SELECT command; we SELECT only the passwords that match that particular username, and then we use the same function as we did in the Change User page to retrieve the password and match it against the password entered.

You’ll get something that looks like this:

delete user form

The alert reader might notice that we used a SELECT command to make sure that users can’t change other people’s passwords, and that with some tweaking that same SELECT command could be used to authenticate in other places as well- namely, as a login system. That’s true, and it’s something that we’ll cover in the next chapter, PHP session headers.

The alert reader may also note that this is an amazingly insecure / inefficient user management system: namely, its use of plaintext passwords. This is true, and we’ll discuss this and ways around it in our later chapter on security. For now, however, we’re simply using this as a fun, practical way to learn about MySQL commands and how to work them in PHP.

Now then – onto the login!

PrevMain