Introduction:
In this section, I will guide you how to create database tables for our project by using SQL commands sent from web server to MySQL server. We do this with setup.php file which you should type and load into your browser before calling up any other files; otherwise, you’ll get numerous MySQL errors.
setup.php
<!DOCTYPE html>
<html> <head> <title>Setting up database</title> </head> <body> <h3>Setting up...</h3> <?php require_once 'functions.php';createTable(‘members’,
‘user VARCHAR(16),
pass VARCHAR(16),
INDEX(user(6))’);
createTable(‘messages’,
‘id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
auth VARCHAR(16),
recip VARCHAR(16),
pm CHAR(1),
time INT UNSIGNED,
message VARCHAR(4096),
INDEX(auth(6)),
INDEX(recip(6))’);
createTable(‘friends’,
‘user VARCHAR(16),
friend VARCHAR(16),
INDEX(user(6)),
INDEX(friend(6))’);
createTable(‘profiles’,
‘user VARCHAR(16),
text VARCHAR(4096),
INDEX(user(6))’);
?>
<br>…done.
</body>
</html>
The tables created are short and sweet, and have the following names and columns:
- members: username user (indexed), password pass
- messages: ID id (indexed), author auth (indexed), recipient recip, message type pm, message message
- friends: username user (indexed), friend’s username friend
- profiles: username user (indexed), “about me” text
Because the function createTable first checks whether a table already exists, this program can be safely called multiple times without generating any errors.
It is very likely that you will need to add many more columns to these tables if you choose to expand on this project. If so, you may need to issue a MySQL DROP TABLE command before re-creating a table.
After successfully creating the MySQL tables, the result is below:
Fig. 1. members table
Fig. 2. messages table
Fig. 3. friends table
Fig. 4. profile table
Congratulations! You have just successfully created tables for our project. These are considered as our database to store users account, messages and profile.
Conclusion:
In this section, I have shown you how to create MySQL tables. By excuting several simple SQL commands we easily created them. It is a bit of cake, isn’t it?
* Ref: