PHP - create Database and tables in MySql

the php mysqli_query() function with the create database query is used to create a new database in mysql. this method return true on success false on failure.

syntax:

         mysqli_query( connection, query)
  • connection : required. specifies the mysql connection to use.
  • query : required. specifies the query string.

in the example below, create a database named "bmw_spider":

 <?php
    
          $_host = "localhost";
               
          $_username = "steve_jobs";
               
          $_password = "15s23t45";              
                        
               
     // "mysqli procedural style use to connect mysql database server"
                
 $connection_obj = mysqli_connect( $_host, $user_name, $pass_word);

  if( $connection_obj ){ echo  "connect to database success";
                  
                 }else {              
     
  echo  "connection to database  error -- : ". mysqli_connect_error(); 
  
                   }
         
           //create a  database
           
    $query =  "create database bmw_spider";
        
     if ( mysqli_query( $connection_obj, $query) ){
        
                          echo  " the bmw_spider database created successfully";
                      
            }else{                     
                          echo  "error  :- ". mysqli_error($connection_obj);                     
                     }       
  ?>

table primary keys and auto increment fields

the primary key  constraint uniquely identifies each record in a database table.

a primary key must contain unique values.it makes searching of record faster.

if the primary key consists of multiple columns,the combination of values in these columns must be unique.

a primary key column can't contain null values.

auto-increment allows a unique number to be generated when a new record is inserted into a table.

notea primary key column often has auto_increment attribute that generates a unique sequence for the key automatically.the primary key of the next row is greater than the previous one.

        $query =  "create table      users

                            (
                             userid  int      not null auto_increment,
                             
                             firstname       varchar(255)     not null,
                             
                             lastname       varchar(255),
                             
                             salary            float,  
                             
                             primary key(userid) 
                            )";

create table statement is used to create a new table in a database.


PHP - create table in MySql

the php mysqli_query() function with the create table mysql query is used to create a new table in mysql. this method return true on success false on failure.

 <?php
    
          $host = "localhost";
               
          $user_name = "steve";
               
          $pass_word = "12steve";
               
          $db_name = "images";               
               
     // ":--mysqli procedural style use to connect mysql database server"
                
  $connection_obj = mysqli_connect( $host, $user_name, $pass_word, $db_name);

  if( $connection_obj ){  echo  " <p>connect to database success </p>";
                  
                 }else {              
     
  echo  "connection fail  error --- : ". mysqli_connect_error(); 
  
                   }
         
           //create a  table
           
    $query = "create table      users(
    
                             userid  int      not null      auto_increment,
                             
                             firstname       varchar(255)     not null,
                             
                             lastname       varchar(255),
                             
                             salary            float,  
                             
                             primary key(userid) )";
        
     if ( mysqli_query( $connection_obj, $query) ){
        
                          echo  " create users table done";
                      
            }else{                     
                          echo  "error  :- ". mysqli_error($connection_obj);                     
                     }       
  ?>