Web Programming TutorialsMYSQL Fundamentals

MYSQL Fundamentals

Today we are going to have an introduction to MYSQL Fundamentals. Most of the websites that we use everyday such as facebook and twitter use a lot of PHP and MYSQL. So let us learn some important concepts of the MYSQL database.

    • What is MYSQL?
      • MYSQL is the world’s most widely used open source relational database.
      • SQL stands for Structured Query Language.
      • It provides multi-user access to multiple databases i.e. one user can have access to multiple databases.
      • MYSQL is a part of LAMP. LAMP stands for Linux/Apache/MYSQL/PHP.
      • It ships with no GUI tools but there are many 3rd party tools such as php Myadmin and HeidiSQL. This means graphical tools are not present in MYSQL to create databases, but it comes with a command Line user access.
      • It is very fast. reliable and easy to use.
      • MYSQL is an ideal database for both small and large databases.
    • MYSQL Queries:
      • The basic operations in any database or file are create, read, update and delete.
      • So mostly we use CRUD (create, read, update delete) in any database.
      • There are 4 basic SQL queries
        • SELECT (to read)
        • INSERT ( to create)
        • UPDATE (to update)
        • DELETE (to delete)
      • Examples of the queries are shown below:
        • SELECT first_name FROM employees; – here we are selecting the whole first_name column from employees table.
        • INSERT INTO employees (first_name,last_name,department) VALUES (‘Kevin’,’Jones’,’IT’); – here we are inserting the values ‘Kevin’, ‘Jones’ and ‘IT’ in the first_name, last_name and department columns of employees table respectively.
        • UPDATE employees SET first_name=’Patrick’, last_name=’Edwards’ WHERE id=4; – here we are updating the first_name and last_name field of employees table that has an id 4.
        • DELETE FROM employees WHERE id=5; – here we are deleting a row having value of id field as 5 in the employees table.
      • Remember if you want to change or delete or select anything i.e. whichever field you use after WHERE clause, should be unique (eg. Primary key).
    • Connecting to a database:
      • To use the database with any programming language it should be linked with it.
      • There are 3 methods to connect a MYSQL database with the PHP:
        1. Use of MYSQL function/API
        2. MYSQL intelligent way
        3. PDO (PHP Data Object)
      • The first way, Use of MYSQL function/API is outdated.
      • The second way, MYSQL intelligent uses mysqli() method.
      • The third way, PDO is a very secure method.
    • Selecting data using MYSQL Intelligent method:
      • Follow the code written below:
<?php
 $connect=mysqli_connect(“localhost”,”peter”,”abc123”,”my_db”);
 //check connection
 if(mysqli_connect_errno())
 {
	echo “Failed to connect to MYSQL: ”.mysqli_connect_error();
 }

 $result=mysqli_query($connect,”SELECT * FROM employees”);
 while($row=mysqli_fetch_array($result))
	echo $row[‘first_name’].” ”.$row[‘last_name’];
 echo “<br>”
?>
      • Here we have selected data using the MYSQL Intelligent method.
      • A variable $connect is used to refer to the connection.
      • mysqli_connect() method is used to establish the connection with database in MYSQL. It contains the server name on which it is working or you can use localhost, if you have installed server on your machine. Next it has username and password if any and then the database name.
      • To check the connection mysqli_connect_errno() method is used and mysqli_connect_error() method is used to get the error message.
      • mysqli_query() method is used to provide the query statement. It’s parameters are the connection object and the query. The result of the query is stored in the variable $result.
      • The result is then displayed in a while loop.
      • mysqli_fetch_array() method is used to fetch data from the result variable.
    • Inserting data using mysqli method:
      • Follow the code written below:
<?php
 $connect=mysqli_connect(“example.com”,”peter”,”abc123”,”mydb”);

 //check connection
 if(mysqli_connect_errno())
 {
    echo “Failed to connect to MYSQL: ”.mysqli_connect_error(); 
 }
 mysqli_query($connect,”INSERT INTO employees(first_name,last _name ,age) VALUES (‘Peter’,”Griffin”,35)”);
 mysqli_query($connect, “UPDATE employees SET  last_name=’Patrick’ ,last_name=’Edwards’ WHERE id=4”)
?>
      • Here we have seen the establishment of connection, checking of connection and inserting into the employees table in the previous point. So let us learn about UPDATE query.
      • For any query we have mysqli_query() method.
      • In the above update query, the values of first_name and last_name fields having id as 4 are changed to “Patrick” and “Edwards” respectively.
    • Selecting data using PDO method:
      • PDO is more flexible: You can use PDO with multiple type databases.
      • It is more secure: It uses prepared statements.
      • It protects against SQL injection and other threats.
      • Follow the code written below:
$id=2;
try
{
 $conn=new PDO(‘mysql:host=localhost;dbname=mydatabase’,’Peter123’, ‘mypassword’);

$conn=>setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt=$conn->prepare(‘SELECT * FOM employees WHERE id=:id’); 
//:id is the placeholder for the actual id

$stmt->execute(array(‘id’=>$id));

while($row=$stmt->fetch())
{
	print_r($row);
}
}
catch(PDOException $e)
{
 	Echo ‘ERROR:’. $e->getMessage();
}
    • This is a procedure to use PDO method for connecting database to PHP.

 

Thus we went through some introduction to MYSQL Fundamentals.

Previous articleJavascript and Jquery Summary
Next articlePHP OOPs

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Exclusive content

- Advertisement -

Latest article

21,501FansLike
4,106FollowersFollow
106,000SubscribersSubscribe

More article

- Advertisement -