Week 9 : Relational Databases

Introduction

Following up on the previous week's post, here is some more advanced php stuff to learn from. In this post we will cover forms, session, cookies, useful variables & more.

MySQL

MySQL is an open source DBMS which is widely used in php applications for its various advantages. PHP comes with in-built functions to use a mysql connection which is both fast & reliant. Here are some of MySql commands which one has to use in order to Retrieve, Manipulate and Delete data in a MySql Table.

Insert
Inserts a new row into a specified value with specified values
Example:

INSERT INTO table_name (column1,column2,column3) VALUES (value1,value2,value3);

Select / From
Retrieves data from a table in the database.
Example:

SELECT * FROM table_name; //Retrieves all the columns from a table
SELECT col1,col2,col3 FROM table_name; //Retrieves the specified columns from the table.


Delete
Deletes data from a table in the database.
Example:

DELETE FROM table_name; //Deletes all the columns from a tableSELECT col1,col2,col3 DELETE FROM table_name WHERE col1= val; //Deletes all rows where the column is equals to val(can use other WHERE statements aswell)

Drop Table
Removes a table from the database.
Example:

DROP  TABLE table_name; //Deletes all the columns from a tableSELECT col1,col2,col3 DELETE DROP TABLE table_name WHERE col1= val; //Deletes all rows where the column is equals to val(can use other WHERE statements aswell)

Create Table

Creates a new table in the database.

Example:

CREATE TABLE table_name (

id NOT NULL AUTO_INCRIMENT PRIMARY KEY,

col_name VARCHAR(50) NOT NULL

)

Order By

Orders a resultset by  the specified column & parameters

Example:

SELECT * FROM table_name ORDER BY col_name DESC;

Where

Used to filter a resultset, similar to an if statement

Example:

SELECT * FROM table_name WHERE col_name > 10;

Like
Is used to filter a resultset, similar to an if statement. It is mostly used to search for particular values in a column. This can be done by using the % operators with the LIKE keyword.
Example:

SELECT * FROM table_name WHERE col_name  LIKE '%search_string%';

Connecting to a MySql Server using PHP

Step1: 
The first thing that we should do in order to use a MySql Database is to connect to the server where the database is connected. In order to do this in PHP, we have to make us of the mysql_connect() functions which is presented as an example below.

mysql_connect("localhost","username","password");

Step2:
Since a Database server can contain more than one database/schema, we need to specify which database/schema we are going to be using. We do this by calling the mysql_select_d() function. Here's an example.

mysql_select_db("database_name");

Step3:
It's time to actually send a command to the MySql database and start having fun with the data. We can send all sort of mysql queries using the mysql_query() command. This command returns a result which contains, if any, the data requested. Follow the example below in order to send a query to the server.

$resultset = mysql_query("SELECT * FROM table_name")

Step4:
The next step is to check whether the query returned any results. To do this use mysql_num_rows() function like the example below:

$count = mysql_num_rows($resultset);

Step5: 
Now that we have the resultset & we know that it has results, we need to iterate each result in order to do our logic. We can do this by using mysql_fetch_array() which gives us an array filled with the resulted rows as arrays or else using mysql_fetch_object() which turns the resultset into an object. 


while($row = mysql_fetch_array($resultset)){
//do something with $row
}


// while($row = mysql_fetch_object($resultset){
//Do something with $row
}