Hosting Solutions
mobile_logo Table of Contents
seal_frontseal_back

MySQL Database Tables Creation Script Logic

Learn to script table creation into your MySQL database to establish all of the software system's tables using PHP and SQL syntax. We are establishing all of the data storage tables that will be required for specific applications on the social networking website. <?php include_once("php_includes/db_conx.php"); $tbl_users = "CREATE TABLE IF NOT EXISTS users ( id INT(11) NOT NULL AUTO_INCREMENT, username VARCHAR(16) NOT NULL, email VARCHAR(255) NOT NULL, password VARCHAR(255) NOT NULL, gender ENUM('m','f') NOT NULL, website VARCHAR(255) NULL, country VARCHAR(255) NULL, userlevel ENUM('a','b','c','d') NOT NULL DEFAULT 'a', avatar VARCHAR(255) NULL, ip VARCHAR(255) NOT NULL, signup DATETIME NOT NULL, lastlogin DATETIME NOT NULL, notescheck DATETIME NOT NULL, activated ENUM('0','1') NOT NULL DEFAULT '0', PRIMARY KEY (id), UNIQUE KEY username (username,email) )"; $query = mysqli_query($db_conx, $tbl_users); if ($query === TRUE) { echo "<h3>user table created OK :) </h3>"; } else { echo "<h3>user table NOT created :( </h3>"; } //////////////////////////////////// $tbl_useroptions = "CREATE TABLE IF NOT EXISTS useroptions ( id INT(11) NOT NULL, username VARCHAR(16) NOT NULL, background VARCHAR(255) NOT NULL, question VARCHAR(255) NULL, answer VARCHAR(255) NULL, PRIMARY KEY (id), UNIQUE KEY username (username) )"; $query = mysqli_query($db_conx, $tbl_useroptions); if ($query === TRUE) { echo "<h3>useroptions table created OK :) </h3>"; } else { echo "<h3>useroptions table NOT created :( </h3>"; } //////////////////////////////////// $tbl_friends = "CREATE TABLE IF NOT EXISTS friends ( id INT(11) NOT NULL AUTO_INCREMENT, user1 VARCHAR(16) NOT NULL, user2 VARCHAR(16) NOT NULL, datemade DATETIME NOT NULL, accepted ENUM('0','1') NOT NULL DEFAULT '0', PRIMARY KEY (id) )"; $query = mysqli_query($db_conx, $tbl_friends); if ($query === TRUE) { echo "<h3>friends table created OK :) </h3>"; } else { echo "<h3>friends table NOT created :( </h3>"; } //////////////////////////////////// $tbl_blockedusers = "CREATE TABLE IF NOT EXISTS blockedusers ( id INT(11) NOT NULL AUTO_INCREMENT, blocker VARCHAR(16) NOT NULL, blockee VARCHAR(16) NOT NULL, blockdate DATETIME NOT NULL, PRIMARY KEY (id) )"; $query = mysqli_query($db_conx, $tbl_blockedusers); if ($query === TRUE) { echo "<h3>blockedusers table created OK :) </h3>"; } else { echo "<h3>blockedusers table NOT created :( </h3>"; } //////////////////////////////////// $tbl_status = "CREATE TABLE IF NOT EXISTS status ( id INT(11) NOT NULL AUTO_INCREMENT, osid INT(11) NOT NULL, account_name VARCHAR(16) NOT NULL, author VARCHAR(16) NOT NULL, type ENUM('a','b','c') NOT NULL, data TEXT NOT NULL, postdate DATETIME NOT NULL, PRIMARY KEY (id) )"; $query = mysqli_query($db_conx, $tbl_status); if ($query === TRUE) { echo "<h3>status table created OK :) </h3>"; } else { echo "<h3>status table NOT created :( </h3>"; } //////////////////////////////////// $tbl_photos = "CREATE TABLE IF NOT EXISTS photos ( id INT(11) NOT NULL AUTO_INCREMENT, user VARCHAR(16) NOT NULL, gallery VARCHAR(16) NOT NULL, filename VARCHAR(255) NOT NULL, description VARCHAR(255) NULL, uploaddate DATETIME NOT NULL, PRIMARY KEY (id) )"; $query = mysqli_query($db_conx, $tbl_photos); if ($query === TRUE) { echo "<h3>photos table created OK :) </h3>"; } else { echo "<h3>photos table NOT created :( </h3>"; } //////////////////////////////////// $tbl_notifications = "CREATE TABLE IF NOT EXISTS notifications ( id INT(11) NOT NULL AUTO_INCREMENT, username VARCHAR(16) NOT NULL, initiator VARCHAR(16) NOT NULL, app VARCHAR(255) NOT NULL, note VARCHAR(255) NOT NULL, did_read ENUM('0','1') NOT NULL DEFAULT '0', date_time DATETIME NOT NULL, PRIMARY KEY (id) )"; $query = mysqli_query($db_conx, $tbl_notifications); if ($query === TRUE) { echo "<h3>notifications table created OK :) </h3>"; } else { echo "<h3>notifications table NOT created :( </h3>"; } ?>
thumb

PHP Videos

Social Network Development

Introduction and Building the Design TemplateDatabase Creation and PHP MySQLi Connection Script TutorialMySQL Database Tables Creation Script LogicEstablish htaccess and phpini for apache web site server configurationCreate the Main JavaScript Module and Insert Other Modules TutorialSign Up Form and Email Activation PHP MySQL JavaScript Programming TutorialLog In Form Log Out Script Cookies Sessions User ProfileCron Job Automate Social Network Web Site Maintenance TutorialForgot Account Password Form Email Script Ajax TutorialFriend Add Block System Ajax TutorialSocial Network Header Logic Dynamic Links Example ScriptFriend Lists and Social Network Notifications TutorialUser Profile Photo File Upload HTML Form PHP Parse Script MySQLPhoto Gallery CMS TutorialConversation System Social Network Ajax PHP MySQL TutorialSend SMS Text Messages to Mobile Phone PHP Tutorial