edu Toidayhoc

Category: mysqli

  • Khóa học miễn phí MySQLi – Introduction nhận dự án làm có lương

    MySQLi – Introduction

    MySQLi is an extension to MySQL API available in PHP and is introduced from PHP 5.0 onwards. It is also known as MySQL improved extension. Motivation behind MySQLi was to take advantage of new features available in MySQL 4.1.3 onwards. It provides numerous benefits over MySQL extension.

    • MySQL provides an object oriented interface. It provides both object oriented and procedural approach to handle database operations.

    Object Oriented Interface

    <?php
   $mysqli = mysqli_connect("localhost", "user", "password", "database-name");

   $result = mysqli_query($mysqli, "SELECT ''Welcome to MySQLi'' AS _msg FROM DUAL");
   $row = mysqli_fetch_assoc($result);
   echo $row[''_msg''];
?>

    Procedural Approach

    <?php
   $mysqli = new mysqli("localhost", "user", "password", "database-name");

   $result = $mysqli→query("SELECT ''Welcome to MySQLi'' AS _msg FROM DUAL");
   $row = $result→fetch_assoc();
   echo $row[''_msg''];
?>

    • MySQLi supports prepared statments.

    • MySQLi supports multiple statments.

    • MySQLi supports transactions.

    • MySQLi provides enhanced debugging capabilities.


    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí MySQLi – Select Database nhận dự án làm có lương

    MySQLi – Select Database

    Once you get connected with the MySQL server, it is required to select a database to work with. This is because there might be more than one database available with the MySQL Server.

    Selecting MySQL Database from the Command Prompt

    It is very simple to select a database from the mysql> prompt. You can use the SQL command use to select a database.

    Example

    Here is an example to select a database called TUTORIALS −

    [root@host]# mysql -u root -p
Enter password:******
mysql> use TUTORIALS;
Database changed
mysql>

    Now, you have selected the TUTORIALS database and all the subsequent operations will be performed on the TUTORIALS database.

    NOTE − All the database names, table names, table fields name are case sensitive. So you would have to use the proper names while giving any SQL command.

    Selecting a MySQL Database Using PHP Script

    PHP uses mysqli_select_db function to select the database on which queries are to be performed. This function takes two parameters and returns TRUE on success or FALSE on failure.

    Syntax

    mysqli_select_db ( mysqli $link , string $dbname ) : bool

    Sr.No. Parameter & Description
    1

    $link

    Required – A link identifier returned by mysqli_connect() or mysqli_init().
    2

    $dbname

    Required – Name of the database to be connected.

    Example

    Try the following example to select a database −

    Copy and paste the following example as mysql_example.php −

    <html>
   <head>
      <title>Selecting MySQL Database</title>
   </head>
   <body>
   <?php
      $dbhost = ''localhost
      $dbuser = ''root
      $dbpass = ''root@123
      $conn = mysqli_connect($dbhost, $dbuser, $dbpass);

      if(! $conn ) {
         die(''Could not connect: '' . mysqli_error($conn));
      }
      echo ''Connected successfully<br />

      $retval = mysqli_select_db( $conn, ''TUTORIALS'' );

      if(! $retval ) {
         die(''Could not select database: '' . mysqli_error($conn));
      }
      echo "Database TUTORIALS selected successfullyn";
      mysqli_close($conn);
   ?>
   </body>
</html>

    Output

    Access the mysql_example.php deployed on apache web server and verify the output.

    Database TUTORIALS selected successfully

    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí MySQLi – Connection nhận dự án làm có lương

    MySQLi – Connection

    MySQL Connection Using MySQL Binary

    You can establish the MySQL database using the mysql binary at the command prompt.

    Example

    Here is a simple example to connect to the MySQL server from the command prompt −

    [root@host]# mysql -u root -p
Enter password:******

    This will give you the mysqli command prompt where you will be able to execute any SQL command. Following is the result of above command −

    The following code block shows the result of above code −

    Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 2854760 to server version: 5.0.9

Type ''help;'' or ''h'' for help. Type ''c'' to clear the buffer.

    In the above example, we have used root as a user but you can use any other user as well. Any user will be able to perform all the SQL operations, which are allowed to that user.

    You can disconnect from the MySQL database any time using the exit command at mysql> prompt.

    mysql> exit
Bye

    MySQL Connection Using PHP Script

    PHP provides mysqli contruct or mysqli_connect() function to open a database connection. This function takes six parameters and returns a MySQL link identifier on success or FALSE on failure.

    Syntax

    $mysqli = new mysqli($host, $username, $passwd, $dbName, $port, $socket);

    Sr.No. Parameter & Description
    1

    $host

    Optional − The host name running the database server. If not specified, then the default value will be localhost:3306.
    2

    $username

    Optional − The username accessing the database. If not specified, then the default will be the name of the user that owns the server process.
    3

    $passwd

    Optional − The password of the user accessing the database. If not specified, then the default will be an empty password.
    4

    $dbName

    Optional − database name on which query is to be performed.
    5

    $port

    Optional − the port number to attempt to connect to the MySQL server..
    6

    $socket

    Optional − socket or named pipe that should be used.

    You can disconnect from the MySQL database anytime using another PHP function close().

    Syntax

    $mysqli→close();

    Example

    Try the following example to connect to a MySQL server −

    Copy and paste the following example as mysql_example.php −

    <html>
   <head>
      <title>Connecting MySQL Server</title>
   </head>
   <body>
      <?php
         $dbhost = ''localhost
         $dbuser = ''root
         $dbpass = ''root@123
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass);

         if($mysqli→connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli→connect_error);
            exit();
         }
         printf(''Connected successfully.<br />'');
         $mysqli→close();
      ?>
   </body>
</html>

    Output

    Access the mysql_example.php deployed on apache web server and verify the output.

    Connected successfully.

    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

  • Khóa học miễn phí MySQLi – Create Tables nhận dự án làm có lương

    MySQLi – Create Table

    To begin with, the table creation command requires the following details −

    • Name of the table
    • Name of the fields
    • Definitions for each field

    Syntax

    Here is a generic SQL syntax to create a MySQL table −

    CREATE TABLE table_name (column_name column_type);

    Now, we will create the following table in the TUTORIALS database.

    create table tutorials_tbl(
   tutorial_id INT NOT NULL AUTO_INCREMENT,
   tutorial_title VARCHAR(100) NOT NULL,
   tutorial_author VARCHAR(40) NOT NULL,
   submission_date DATE,
   PRIMARY KEY ( tutorial_id )
);

    Here, a few items need explanation −

    • Field Attribute NOT NULL is being used because we do not want this field to be NULL. So, if a user will try to create a record with a NULL value, then MySQL will raise an error.

    • Field Attribute AUTO_INCREMENT tells MySQL to go ahead and add the next available number to the id field.

    • Keyword PRIMARY KEY is used to define a column as a primary key. You can use multiple columns separated by a comma to define a primary key.

    Creating Tables from Command Prompt

    It is easy to create a MySQL table from the mysql> prompt. You will use the SQL command CREATE TABLE to create a table.

    Example

    Here is an example, which will create tutorials_tbl −

    root@host# mysql -u root -p
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> CREATE TABLE tutorials_tbl(
   → tutorial_id INT NOT NULL AUTO_INCREMENT,
   → tutorial_title VARCHAR(100) NOT NULL,
   → tutorial_author VARCHAR(40) NOT NULL,
   → submission_date DATE,
   → PRIMARY KEY ( tutorial_id )
   → );
Query OK, 0 rows affected (0.16 sec)
mysql>

    NOTE − MySQL does not terminate a command until you give a semicolon (;) at the end of SQL command.

    Creating Tables Using PHP Script

    PHP uses mysqli query() or mysql_query() function to create a MySQL table. This function takes two parameters and returns TRUE on success or FALSE on failure.

    Syntax

    $mysqli→query($sql,$resultmode)

    Sr.No. Parameter & Description
    1

    $sql

    Required – SQL query to create a MySQL table.
    2

    $resultmode

    Optional – Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

    Example

    Try the following example to create a table −

    Copy and paste the following example as mysql_example.php −

    <html>
   <head>
      <title>Creating MySQL Table</title>
   </head>
   <body>
      <?php
         $dbhost = ''localhost
         $dbuser = ''root
         $dbpass = ''root@123
         $dbname = ''TUTORIALS
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

         if($mysqli→connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli→connect_error);
            exit();
         }
         printf(''Connected successfully.<br />'');

         $sql = "CREATE TABLE tutorials_tbl( ".
            "tutorial_id INT NOT NULL AUTO_INCREMENT, ".
            "tutorial_title VARCHAR(100) NOT NULL, ".
            "tutorial_author VARCHAR(40) NOT NULL, ".
            "submission_date DATE, ".
            "PRIMARY KEY ( tutorial_id )); ";
         if ($mysqli→query($sql)) {
            printf("Table tutorials_tbl created successfully.<br />");
         }
         if ($mysqli→errno) {
            printf("Could not create table: %s<br />", $mysqli→error);
         }
         $mysqli→close();
      ?>
   </body>
</html>

    Output

    Access the mysql_example.php deployed on apache web server and verify the output.

    Connected successfully.
Table tutorials_tbl created successfully.

    Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc