One-Page SQL Tutorial: User Management

We will see how to create an SQL table to store a list of registered participants, and how to write all the queries necessary to manage the user file. Thus, the bulk of SQL commands will be implemented in practice.

Database queries are written to PHP. All versions of PHP and MySQL support these commands.

Resume Summary
  1. New User Database.
  2. SQL user table.
  3. Clear Table.
  4. Add Column.
  5. Delete Column.
  6. Add User.
  7. Changing information.
  8. Delete User.
  9. Search for User.
  10. Check your login.
  11. Check in last record.
  12. Registration form.

New Database

The configuration file, for example, sete.php, contains database access data:

$HOST = "localhost";
$USER = "nomutilisateur";
$PASS = "motdepasse";
$NAME ="nombase";
$PREFIX = "MyApp_";

If the database is created automatically by the server, replace the values with the values provided by the server. Otherwise, we choose our names.
The user name is usually the same as the database name. This is software that connects to the database, not the user.

The table name is added to set.php as an instance of the user table:

$users = "nomtable";

For simplicity, the code for opening a connection to the database is also placed in the same set.php file:

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

Thus, we include the config in each file that uses the database to automatically connect to it.
The $ base resource will be used by all SQL commands.

If the database was created by the server, proceed to the next step, if you do not create the database, with this command:

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

SQL Users Table

In our simplified example, the login and password of each user are recorded, as well as a unique identification number that is used to bind the user table to other tables.

Here is the table, in the first row the name of the columns, and in the second - the data type:

same login passcode
to integrate warchar warchar

The table creation request will:

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

The PHP command becomes:

$handler = mysqli_query($base, $query)
    or die("Connexion impossible à $users");

Clear Table

DROP TABLE $users

Add Column

If you want to add additional information, such as save email messages, you can modify the table by adding a column whose name and data type are:

ALTER TABLE $users ADD emails varchar(64)

Delete Column

To clear only a field in an already created and populated table:

ALTER TABLE $users DROP COLUMN emails

The column "email" is deleted, the data in the column is also deleted .

Add User

The data is provided by the form in the variables obtained by the PHP script:

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

We submit the following request:

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

Change of information

The user wants to change his login, this only information can be changed in the table:

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

To modify multiple data, you can separate them with a comma:

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

Delete User

By administrator order based on ID:

DELETE FROM $users WHERE id=$id

You can use a name named WHERE = $ login.

Search for User

Knowing the user's login, I want to access other data.

SELECT * FROM $users WHERE login=$login

The data is extracted by the PHP mysql_fetch_assoc command.
Example:

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

Check login

Each user uses a unique name to log in. Before any registration, you should check if the proposed name is already used, and therefore no longer exists in the database.

The mysql_num_rows function returns the number of columns selected by the query. This number is 0 when you are looking for a string, but it is not .
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 string in the database.

Check In Last Entry

Each user ID is a number that automatically increments with each new registration.

The number of the last registered user is not stored in memory between sessions, it must be found in the database.

SELECT id FROM $users ORDER BY id DESC LIMIT 1

- Select id only: SELECT id
- We classify by id and in descending order: ORDER BY ID DESC
- Only one entry is saved: LIMIT 1

Therefore, the first entry is returned to the list of identifiers classified in descending order.
The value found has yet to be incremented to create 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 the user.

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

Simplified script register.php:

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


// controle du login
isLoginUsed($login) die("Login déjà utilisé");


// création de l'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;

// ajout de l'utilisateur
$query="INSERT INTO $users(id, login, password) VALUES('$id','$login','$password')";
mysqli_query($base, $query);
?>

This script is purely didactic and serves to understand user file management. You can avoid the need to execute full-fledged scripts using the PHP framework.