What is Mysql:

MySQL is one of the most popular relational database system being used on the Web today. It is freely available and easy to install, however if you have installed Wampserver it already there on your machine. MySQL database server offers several advantages:

  • MySQL is easy to use, yet extremely powerful, secure, and scalable.
  • MySQL runs on a wide range of operating systems, including UNIX, Microsoft Windows, Apple Mac OS X, and others.
  • MySQL supports standard SQL (Structured Query Language).
  • MySQL is ideal database solution for both small and large applications.
  • MySQL is developed, and distributed by Oracle Corporation.
  • MySQL is very fast and secure. It includes data security layers that protect sensitive data from intruders.

MySQL database stores data into tables like other relational database. A table is a collection of related data, and it is divided into rows and columns.

Each row in a table represents a data record that are inherently connected to each other such as information related to a particular person, whereas each column represents a specific field such as ‘firstname’, ‘lastname’, ’email’, etc. The structure of a simple MySQL table that contains person’s general information may look something like this:

Database Connection:

In order to access the data inside a MySQL database, you first need to open a connection to the MySQL database server. In PHP you can easily do this using the mysql_connect() function. All communication between PHP and the MySQL database server takes place through this connection. The basic syntax of the mysql_connect() function is given with:

Ex:

Database Create:

Since all tables are stored in a database, so first we have to create a database before creating tables. The CREATE DATABASE statement is used to create a database in MySQL.

Let’s make a SQL query using the CREATE DATABASE statement, after that we will execute this SQL query through passing it to the mysql_query() function to finally create our database. The following example creates a database named “dbname”.

Ex:

$create = mysql_query( ‘dbname’, $conn );
if(! $create )
{
  die('Could not create database: ' . mysql_error());
}
echo "Database created successfully\n";
mysql_close($create);

Database Insert:

INSERT INTO statement with appropriate values, after that we will execute this SQL query through passing it to the mysql_query() function to insert data in table. Here’s an example, which adds a record to the persons table by specifying values for the  ‘firstname’, ‘lastname’ and ’email’ fields:

Ex:

$insert= “INSERT INTO tablename(firstname,lastname,email)VALUES(‘vignesh’,’prabu’,’email’)”;
if($insert) {
echo “Insert successfully”;  }
else { “Error..!”};

Database Delete:

DELETE statement and WHERE clause, after that we will execute this SQL query through passing it to the mysql_query() function to delete the tables records. Consider the following “tablename” table inside the “dbname” database:

Ex:

$delete = "DELETE FROM tablename WHERE id=3";

if($delete) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record”; 
}

Database Select:

SELECT statement, after that we will execute this SQL query through passing it to the mysql_query() function to select the tables records. Consider the following “tablename” table inside the “dbname” database:

Ex:

$select = "SELECT id, firstname, lastname FROM tablename";


if ($result) {
       while($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "
";
    }
} else {
    echo "0 results";
}

Database Update:

UPDATE statement and WHERE clause, after that we will execute this SQL query through passing it to the mysql_query() function to update the tables records. Consider the following “tablename” table inside the “dbname” database:

Ex:

$update= "UPDATE tablename SET lastname='ganesh' WHERE id=2";

if ($update) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " ;}

Database Limit:

The LIMIT clause is used to constrain the number of rows returned by the SELECT statement. This feature is very helpful for optimizing the page loading time as well as to enhance the readability of a website. For example you can divide the large number of records in multiple pages using pagination, where limited number of records will be loaded on every page from the database when a user request for that page by clicking on pagination link.

The LIMIT clause accepts one or two parameters which must be a nonnegative integer:

  • When two parameters are specified, the first parameter specifies the offset of the first row to return i.e. the starting point, whereas the second parameter specifies the number of rows to return. The offset of the first row is 0 (not 1).
  • When only one parameter is given, it specifies the maximum number of rows to return from the beginning of the result set.

For example, to retrieve the first three rows, you can use the following query:

Ex:

SELECT * FROM tablename LIMIT 3;
To retrieve the rows 2-4 (inclusive) of a result set, you can use the following query:

Ex:

SELECT * FROM tablename LIMIT 1, 3;
Let's make a SQL query using the SELECT statement and LIMIT clause, after that we will execute this SQL query through passing it to the mysql_query() function to get the limited number of records. Consider the following "tablename" table inside the "dbname" database:

Ex:

$link = mysql_connect("localhost", "root", "", "dbname");
   
  // Check connection
  if($link === false){
      die("ERROR: Could not connect. " . mysql_connect_error());
  }
 

$sql = "SELECT * FROM tablename LIMIT 1, 3";
  if($result = mysql_query($link, $sql)){
      if(mysql_num_rows($result) > 0){
          echo "";
              echo "";
                  echo "";
                  echo "";
                  echo "";
                  echo "";
              echo "";
          while($row = mysql_fetch_array($result)){
              echo "";
                  echo "";
                  echo "";
                  echo "";
                  echo "";
              echo "";
          }
          echo "
person_idfirstnamelastnameemail 
" . $row['person_id'] . "" . $row['firstname'] . "" . $row['lastname'] . "" . $row['email'] . "
";
          // Close result set
        mysql_free_result($result);
      } else{
          echo "No records matching your query were found.";}
  } else{
      echo "ERROR: Could not able to execute $sql. " . mysql_error($link);
  }

Database Ordering result:

The ORDER BY clause can be used in conjugation with the SELECT statement to see the data from a table ordered by a specific field. The ORDER BY clause lets you define the field name to sort against and the sort direction either ascending or descending.

Syntax:

SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC

Ex:

< ?php
  
  $link = mysql_connect("localhost", "root", "", "dbname");
   
  // Check connection
  if($link === false){
      die("ERROR: Could not connect. " . mysql_connect_error());
  }
   
  // Attempt select query execution with order by clause
  $sql = "SELECT * FROM tablename ORDER BY firstname";
  if($result = mysql_query($link, $sql)){
      if(mysql_num_rows($result) > 0){
          echo "";
              echo "";
                  echo "";
                  echo "";
                  echo "";
                  echo "";
              echo "";
          while($row = mysql_fetch_array($result)){
              echo "";
                  echo "";
                  echo "";
                  echo "";
                  echo "";
              echo "";
          }
          echo "
person_idfirstnamelastnameemail 
" . $row['person_id'] . "" . $row['firstname'] . "" . $row['lastname'] . "" . $row['email'] . "
";
          // Close result set
          mysql_free_result($result);
      } else{
          echo "No records matching your query were found.";
      }
  } else{
      echo "ERROR: Could not able to execute $sql. " . mysql_error($link);
  }
   
  // Close connection
  mysql_close($link);
  ?>