Online training and video tutorials
Enter     Registration
     Contacts

 
     
 
Max Azimov
St. Petersburg

Articles [97]

User's video [259]





PHP Create MySQL Tables
 
A database table has its own unique name and consists of columns and rows.
 
Create a MySQL Table Using MySQLi and PDO
 
The CREATE TABLE statement is used to create a table in MySQL.
 
We will create a table named "MyGuests", with five columns: "id", "firstname", "lastname", "email" and "reg_date":
  1. CREATE TABLE MyGuests (
  2. id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  3. firstname VARCHAR(30) NOT NULL,
  4. lastname VARCHAR(30) NOT NULL,
  5. email VARCHAR(50),
  6. reg_date TIMESTAMP
  7. )
  8.  
Notes on the table above:
 
The data type specifies what type of data the column can hold. For a complete reference of all the available data types, go to our Data Types reference.
 
After the data type, you can specify other optional attributes for each column:
  • NOT NULL - Each row must contain a value for that column, null values are not allowed
  • DEFAULT value - Set a default value that is added when no other value is passed
  • UNSIGNED - Used for number types, limits the stored data to positive numbers and zero
  • AUTO INCREMENT - MySQL automatically increases the value of the field by 1 each time a new record is added
  • PRIMARY KEY - Used to uniquely identify the rows in a table. The column with PRIMARY KEY setting is often an ID number, and is often used with AUTO_INCREMENT
 
Each table should have a primary key column (in this case: the "id" column). Its value must be unique for each record in the table.
 
The following examples shows how to create the table in PHP:
 
Example (MySQLi Object-oriented)
  1. <?php
  2. $servername = "localhost";
  3. $username = "username";
  4. $password = "password";
  5. $dbname = "myDB";
  6. // Create connection
  7. $conn = new mysqli($servername, $username, $password, $dbname);
  8. // Check connection
  9. if ($conn->connect_error) {
  10.     die("Connection failed: " . $conn->connect_error);
  11. // sql to create table
  12. $sql = "CREATE TABLE MyGuests (
  13. id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
  14. firstname VARCHAR(30) NOT NULL,
  15. lastname VARCHAR(30) NOT NULL,
  16. email VARCHAR(50),
  17. reg_date TIMESTAMP
  18. )";
  19. if ($conn->query($sql) === TRUE) {
  20.     echo "Table MyGuests created successfully";
  21. } else {
  22.     echo "Error creating table: " . $conn->error;
  23. }
  24. $conn->close();
  25. ?>
 
Example (MySQLi Procedural)
  1. <?php
  2. $servername = "localhost";
  3. $username = "username";
  4. $password = "password";
  5. $dbname = "myDB";
  6. // Create connection
  7. $conn = mysqli_connect($servername, $username, $password, $dbname);
  8. // Check connection
  9. if (!$conn) {
  10.     die("Connection failed: " . mysqli_connect_error());
  11. }
  12. // sql to create table
  13. $sql = "CREATE TABLE MyGuests (
  14. id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
  15. firstname VARCHAR(30) NOT NULL,
  16. lastname VARCHAR(30) NOT NULL,
  17. email VARCHAR(50),
  18. reg_date TIMESTAMP
  19. )";
  20. if (mysqli_query($conn, $sql)) {
  21.     echo "Table MyGuests created successfully";
  22. } else {
  23.     echo "Error creating table: " . mysqli_error($conn);
  24. }
  25. mysqli_close($conn);
  26. ?>
 
Example (PDO)
  1. <?php
  2. $servername = "localhost";
  3. $username = "username";
  4. $password = "password";
  5. $dbname = "myDBPDO";
  6. try {
  7.     $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  8.     // set the PDO error mode to exception
  9.     $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  10.     // sql to create table
  11.     $sql = "CREATE TABLE MyGuests (
  12.     id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
  13.     firstname VARCHAR(30) NOT NULL,
  14.     lastname VARCHAR(30) NOT NULL,
  15.     email VARCHAR(50),
  16.     reg_date TIMESTAMP
  17.     )";
  18.     // use exec() because no results are returned
  19.     $conn->exec($sql);
  20.     echo "Table MyGuests created successfully";
  21.     }
  22. catch(PDOException $e)
  23.     {
  24.     echo $sql . "<br>" . $e->getMessage();
  25.     }
  26. $conn = null;
  27. ?>


2016-12-10
539 views




 
     
 
Map      Distance education, online learning courses. Project PLANETSTUDY - distance learning online.
© 2012 PlanetStudy

Catalog.299.ru

The site is a media provider
Яндекс.Метрика
 
English USA
Australia Русский