13 - Database concepts in PHP

When we are creating any website, it is mandatory to store complete information about entities. Accurate and relevant data is the most important source of information. We can store this data in any type of DBMS like MySql, Oracle, DB2, Sybase or SQL SERVER. We can also manage data by providing data efficiency and integrity rules.

If we store data in a traditional file system, it would not be appropriate for managing data.

Database concepts:

Databases are sets of data organized in such a way that the information can be easily accessed, managed, and updated.

Databases terms:

     Table: it contains data in structured format. In form of rows and columns.

     Key: It is a unique value that is sufficient to identify any record in a table.

     Join: When we want to access information from more than one table, we should join two tables using join operation.

    Query: In SQL this is a statement that is written to gather information from a database.

    Schema: Schema is the way to relate or map multiple tables.

Introduction to MySQL

In 1979 Monty Widenius is the inventor of Mysql.

As we are using PHP scripting language for web site development, it is recommended that we should use MYSQL DBMS for storing and managing data. It is very good and freely available. This database is now become the most popularly used database because of its following characteristics:

  • Very fast and consistent performance
  • Highly reliable
  • Easy to use
  • Freely available (Open source)
  • Can run on various platforms like Linux, Windows, HP-UX
  • Scalability
  • Robust
  • Web data strength
  • Data security
  • Comprehensive application development
  • Lowest cost

 

SQL statements:

In any type of RDBMS the statements can be categorized in the following types:

  • DDL statements

     These types of statements are written to create or alter database objects. Like CREATE, DROP and ALTER statements

  • DML statements

     These types of statements are written to modify the state of database objects like INSERT, UPDATE, INSERT INTO statements.

  • DCL statements

     These types of statements are used to provide or revoke rights on database objects like GRANT and REVOKE statements.

  • DQL statements

     These types of SQL statements are used to fetch records from a database table like SELECT statement.

 

Prepared statements

Prepared statements are the queries  those are compiled once on the server level and parameters are then passed to the server instead of building the query each.

Benefits of prepared statements:

   Higher performance

        SQL queries are compiled once on the server and reused.

    Higher security

        Prepared statements are not vulnerable to SQL injection attacks. Data is passed using placeholders in the query instead of directly in the query.

    More portable

        Different databases have different methods of escaping data.

        Prepared statements can be used in a specific order:

             1. Compile the statement

             2. Execute the query with or without parameters

             3. Retrieve the results

        Example:

                // open connection

                $db=new PDO(‘Mysql:./mydb.db’);

                // compile statment

                $stmt=$db-> prepare(‘SELECT * FROM users WHERE user_id=?’);

                //execute query

                $stmt->execute(array($id));

                // retrieve the result

                $row=$stmt->fetch();

Introduction to stored procedures

These are precompiled sets of database queries, stored on the server. Stored procedures are much faster than regular database queries. For eg:

CREATE PROCEDURE ‘proc()’
BEGIN
    INSERT INTO currenttime VALUES (NOW());
END

$pdo->exec(‘CALL proc()’);

Retrieving result

CREATE PROCEDURE ‘proc()’
BEGIN
    INSERT INTO currenttime VALUES (NOW());
END

foreach ($pdo->query(‘CALL proc()’) as $row)
{
}

Requirements to connect PHP and MySql

If we want to use PHP with MySQL, then first of all we need to install a webserver (Mostly Apache) in case of LAMP. Then we should install PHP and MySQL both.

We can check that PHP and MySQL both are installed on our web server by using the following code:

    <?php

       Phpinfo();

    ?>

Save above code with the filename phpinfo.php and open it in a browser. If it will display complete details of PHP installation, and there is a section about MySQL, then it is confirmed that MySQL  is installed.

When we want to connect with MySQL database, we need some credentials to complete the process like:

  • Database name
  • Host name
  • User name
  • Password

We can use the following code to make a connection with MySQL database. In the following code mysql_connect() function is used to make a connection, which receives three parameters(hostname, username, password).

After creation of connection successfully, we use mysql_select() function to connect with database.

<?php
mysql_connect(“localhost”,”username”,”password”) or die(mysql_error());
mysql_select_db(“database_name”) or die(mysql_error());
?>

We can also define these credentials in variables and then we can use those variables to make a connection such as:

    $host=”localhost”;

    $username=”root”;

    $password=”guest@123”;

    $db_name=”product”;

Here we assumed that no password is required to make a connection.

<?php
 $db_connect=mysql_connect($host,$username,$password) or die(mysql_error());
 $db_found=mysql_select_db($db_name) or die(mysql_error());
?>

After completing the task, we can close MySQL connection using the following statement:

    mysql_close($db_connect);

Example:

<?php
$hostname="localhost";
$username="root";
$password="guest@123";
$db_name="product";

$db_connect=mysql_connect($localhost,$username,$password);
$db_found=mysql_select_db($database,$db_connect);
if($db_found)
{
    $query="SELECT * FROM PRODUCTDETAILS";
    $result=mysql_query($query);
    while($db_set=mysql_fetch_assoc($result))
    {
    echo $db_set['product_id'];
    echo "<br/>";
    echo $db_set['product_name'];
    echo "<br/>";
    echo $db_set['quantity'];
    echo "<br/>";
    echo $db_set['price'];
    }
    mysql_close($db_connect);
}
else
{
    echo "Database not found";
    mysql_close($db_connect);
}
?> 

Fetching data set in PHP

When we want to fetch data from Mysql database through PHP query, the result would be an integer value that represents whether the query was successful or not? In PHP we can use following functions to fetch the data from database:

  • mysql_fetch_row: It returns row as an enumerator array
  • mysql_fetch_object: It returns row as an object
  • mysql_fetch_array: It returns row as an associative array
  • mysql_result: It returns one cell of data

Creating MySql database using PHP

We can create MySql database directly using PHP script. If we want to create database in mysql, we need to have full privileges on CREATE and DROP statements of mysql. Following functions can be used for the same:

  • mysql_create_db(): It is used to create a database with the name specified.
  • mysql_drop_db() : this function is used to delete the specified database.
  • mysql_query(): It is used to pass table definition and for drop also.
<?php 

   $link= mysql_connect('localhost','root','guest@123');
   mysql_create_db('mydb',$link);
   mysql_select_db('mydb');

   $query="CREATE TABLE mytable(id int not null auto_increment primary key, name varchar(25))";

   $result=mysql_query($query);
   $drop=mysql_drop_db('mydb');
?>

Inserting rows in a database table using PHP code:

Insertdemo.html

<html> 
  <head> 
    <title>Inserting Records In Database Table</title> 
  </head> 
  <body> 
    <H2>Inserting records using PHP</H2> 
    <caption> <h3> Enter product details</h3></caption>
    <table border=0 align=left>
      <form ACTION="inserting.php" method="post"> 
         <tr>    <td>Enter product id:</td><td><input type=text name=productid></td></tr><br>
         <tr>    <td>Enter product name</td><td><input type=text name=productname></td></tr><br>
         <tr>    <td>Enter product quantity</td><td><input type=text name=productquantity></td></tr><br>
         <tr>    <td>Enter product price</td><td><input type=text name=productprice></td></tr><br><br>
              <td></td><td><input type=button name=productname value=submit></td>
      </form>
    </table> 
  </body> 
</html>

 

Inserting.php

<?php
  $hostname="localhost";
  $username="root";
  $password="guest@123";
  $db_name="product";
  $db_connect=mysql_connect($localhost,$username,$password);
  if(!$db_connect)
  {
     die('could not connect with database'.mysql_error());
  }
  $db_found=mysql_select_db($database,$db_connect);
  if($db_found)
  {
    $sql="INSERT INTO productdetails(productid,name,quantity,price) VALUES('$_POST[productid]','$_POST[productname]','$_POST[productquantity]','$_POST[productprice]')";
    if(!mysql_query($sql,$con))
    {
        die('Error'.mysql_error());
    }
    echo "record added successfully";
    mysql_close($db_connect);
  }
?>

 

Like us on Facebook

Wideskills