One page SQL tutorial : Users management

We'll see how to create an SQL table to store the list of registered users, and how to write all the necessary requests to the management of a user file. Most SQL commands will be so put into practice.

Queries to the database are written in PHP. All versions of PHP and MySQL support these commands.

Summary

Creating the database of users.
SQL table of users.
Deleting the table.
Adding a column.
Deleting a column.
Adding a user.
Replacing information.
Removing a user.
Retrieving a user.
Checking a login.
Retrieving the last record.
Registration form.

Creating the database

In the configuration file, called config.php for example, you put the data to access the database:

$HOST = "localhost";
$USER = "username";
$PASS = "password";
$NAME ="basename";
$PREFIX = "MyApp_";

If the database is created automatically by the server, replace the values by the values provided by the server. Otherwise chooses your own names and password.
The username is usually the same as that of the database. This is the software which connects to the base and not the net surfer.

Adds in config.php the table names, in this case the users table:

$users = "tablename";

For simplicity, we also place the code to open the database connection in the same file config.php

$base = mysqli_connect($HOST, $USER, $PASS);
mysqli_select_db($base, $dbname);

Thus we include in each file using the base config.php to be automatically connected.
The $base resource will be used by all SQL commands.

If the database was created by the server, go to the next step, otherwise create the database with this command:

mysqli_query($base, "CREATE DATABASE $NAME");

SQL table of users

In our simple example, we record the login and password for each user, and a unique identification number used to connect the table of users to other tables.

Here is the table, on the first line the name of the columns and on the second, type of data:

id	login	password
integer	varchar	varchar

The query to create the table is:

$query="CREATE TABLE  $users(
           id integer PRIMARY KEY NOT NULL,  
           login varchar(12) UNIQUE NOT NULL,
           password varchar(12) NOT NULL
       )";

The PHP command is:

$handler = mysqli_query($base, $query)
    or die("Enable to connect to $users");

Deleting the table

DROP TABLE $users

Adding a column

If you want to add additional information, for example, storing the emails, it is possible to modify the table by adding a column of wich you specify its name and data type:

ALTER TABLE $users ADD emails varchar(64)

Deleting a column

To delete only one field in a table already created and filled:

ALTER TABLE $users DROP COLUMN emails

The column "email" is deleted and the data in the column are as well.

Adding a user

Data supplied by the form in variables retrieved by a PHP script:

$id = "123";
$login = "me";
$password = "xxx";

We pass the following query:

INSERT INTO $users(id, login, password) 
  VALUES('$id','$login','$password')

Replacing information

The user wants to change the login, you can change only the information in one column:

UPDATE $users SET login='$login' WHERE id=$id

To edit multiple columns, they are separated by a comma:

UPDATE $users SET login='$login', password='$password' WHERE id=$id

Removing a user

Commissioned by the administrator based on the identifier:

DELETE FROM $users WHERE id=$id

Can be based on the name with: WHERE login=$login.

Retrieving a user

Knowing the login of a user, you want to access other data.

SELECT * FROM $users WHERE login=$login

Data is retrieved by the mysql_fetch_assoc PHP function.
Example:

$handler = 
   mysqli_query($base, "SELECT * FROM $users WHERE login = '$login'");
$data = mysqli_fetch_assoc($handler);
$id = $data['id'];
$password = $data['password'];

Checking a login

Each user uses a unique name for the login. Before applying, you should check if the proposed name is not already used, and therefore does not already exist in the database.

The mysql_num_rows function returns the number of columns selected by a query. This number is 0 when attempting a line and it does not exist.
Hence the following function:

function isLoginUsed($login)
{
   global $base;
   global $users;
   $handler = 
     mysqli_query($base, "SELECT * FROM $users WHERE login = '$login'");
   if($handler == false) return 0;
   return(mysqli_num_rows($handler) == 0);
}

The function returns true if the login exists and occupies a row in the database.

Retrieving the last record

The identifier of each user is a number that automatically increments with each new registration.

The number of the last registered user is not retained in memory between two sessions, it must be retrieved in the database.

SELECT id FROM $users ORDER BY id DESC LIMIT 1

- Only the id is selected: SELECT id
- Results are sorted by id and in descending order: ORDER BY id DESC
- Only one record is returned: LIMIT 1

We therefore incorporates the first record in the list of identifiers sorted in descending order.
It remains to increment the value found for creating a new identifier.

$handler = 
   mysqli_query($base, "SELECT id FROM $users ORDER BY id DESC LIMIT 1");
$data = mysqli_fetch_assoc($handler);
$id = $data['id'];
$id = intval($id) + 1;

Registration form

It sends the data to a PHP script that adds a user.

<form method="post" action="register.php">
  <input type="text" name="login" value="" >
  <input type="password" name="pass" value="">
</form>

The simplified register.php script:

<?php
$login=$_POST['login'];
$password =$_POST['pass'];

// login check
isLoginUsed($login) die("Login already used");

// creating an id
$handler = 
  mysqli_query($base, "SELECT id FROM $users ORDER BY id DESC LIMIT 1");
$data = mysqli_fetch_assoc($handler);
$id = $data['id'];
$id = intval($id) + 1;

// adding the user
$query="INSERT INTO $users(id, login, password) VALUES('$id','$login','$password')";
mysqli_query($base, $query);
?>

This script is purely educational and for understanding the management of a user file. You can save yourself the trouble of achieving complete scripts by using a PHP framework.