SQL Module 3: Mastering PHP-MySQL Connection

PHP and SQL Module 3

Objectives Review Basic SQL commands: Create Database, Create Table, Insert and Select Connect an SQL Database to PHP Execute SQL Commands in PHP

SQL Standard Query Language

SQL • SQL stands for Structured Query Language • SQL is a standard language for accessing databases. • MySQL, SQL Server, Access, Oracle, Sybase, DB2, and other database systems. • SQL lets you access and manipulate databases • SQL is an ANSI (American National Standards Institute) standard

SQL is a Standard - BUT.... • Although SQL is an ANSI (American National Standards Institute) standard, there are many different versions of the SQL language. • However, to be compliant with the ANSI standard, they all support at least the major commands (such as SELECT, UPDATE, DELETE, INSERT, WHERE) in a similar manner. • Note: Most of the SQL database programs also have their own proprietary extensions in addition to the SQL standard!

Using SQL in Your Web Site • To build a web site that shows some data from a database, you will need the following: • An RDBMS database program (i.e. MS Access, SQL Server, MySQL) • A server-side scripting language, like PHP or ASP • SQL • HTML / CSS

Creating an SQL Database: • CREATE DATABASE database_name • Eg. Create database friends;

Database Tables A database most often contains one or more tables. Each table is identified by a name (e.g. "Customers" or "Orders"). Tables contain records (rows) with data.

The CREATE TABLE Statement CREATE TABLE table_name(column_name1 data_type,column_name2 data_type,column_name3 data_type,....)

Example: CREATE TABLE Friends(idnumber int,LastName varchar(255),FirstName varchar(255),Age varchar(255),Gender varchar(255))

SQL Constraints • Constraints are used to limit the type of data that can go into a table. • Constraints can be specified when a table is created (with the CREATE TABLE statement) or after the table is created (with the ALTER TABLE statement). • NOT NULL • UNIQUE • PRIMARY KEY • FOREIGN KEY • CHECK • DEFAULT

Friends

The SQL SELECT Statement • The SELECT statement is used to select data from a database. • The result is stored in a result table, called the result-set. • SQL SELECT Syntax • SELECT column_name(s)FROM table_name • and • SELECT * FROM table_name

and • SELECT * FROM table_name WHERE [Conditions] • Eg. Select * From ekek where lastname = ‘Gargar’;

Examples: SELECT * FROM NAMES SELECT Lastname,Firstname From Names SELECT * FROM Names Where Lastname = ‘Agcopra’ SELECT * FROM Names Where Firstname like ‘A%’; Select * From Names Where Age > 25 AND Gender = ‘M’

PHP Standard Query Language

Pre-requisites Know your HTMLS Know common PHP Commands and Structures Master your SQL

Some Steps Check if there is an PHP-SQL connection Use a Database Get a Table and Execute Queries Extract Data from the Queries. Close your Database after use.

1. Check if there is a CONNECTION <?php $connection = mysql_connect(‘localhost’,’root’,’password’) or die (‘Unable to Connect’); if($connection!=NULL) { echo "SQL is Connected to PHP"; }

If there is no connection:

2. Use a Database: mysql_select_db('friends') or die ('Unable to select a database!');

3. Get a Table and Execute a Query $query = 'Select * FROM names'; $result = mysql_query($query) or die (‘error in query’); ('Error in query: $query. ' . msql_error());

4. Extraction of Data • There are 3 different ways to extract data: • Mysql_fetch_row() • Mysql_fetch_assoc() • Mysql_fetch_object()

Extract the Table version Amysql_fetch_row() 1] echo "<ol>"; 2] 3] if(mysql_num_rows($result) > 0) 4] { 5] while($row = mysql_fetch_row($result)) 6] { 7] echo "<li> $row[1], $row[2] </li>"; 8] } 9] } 10]echo "</ol>";

Extract the Table version Bmysql_fetch_assoc() 1] echo "<ol>"; 2] 3] if(mysql_num_rows($result) > 0) 4] { 5] while($row = mysql_fetch_assoc($result)) 6] { 7] echo "<li> $row[‘lastname’], $row[‘firstname’] </li>"; 8] } 9] } 10]echo "</ol>";

Extract the Table version Cmysql_fetch_object() 1] echo "<ol>"; 2] 3] if(mysql_num_rows($result) > 0) 4] { 5] while($row = mysql_fetch_object($result)) 6] { 7] echo "<li> $row->lastname, $row->firstname </li>"; 8] } 9] } 10]echo "</ol>";

5. Close Database mysql_free_result($result); mysql_close($connection); ?>

A Basic Connection: <?php $connection = mysql_connect(‘localhost’,’root’,’password’); mysql_select_db('friends') ; $query = 'Select * FROM names'; $result = mysql_query($query); echo "<ol>"; if(mysql_num_rows($result) > 0) { while($row = mysql_fetch_row($result)) { echo "<li> $row[1], $row[2] </li>"; } } echo "</ol>"; mysql_free_result($result); mysql_close($connection); ?>

Review: PHP-SQL Commands

Group Work 1st Activity for the Semi-Finals

Step 1: Setup a Database • Make a Database named DB_31_[your block] • Make a Table named Employees • Data Entity and Attributes: • Idnum • Lastname • Firstname • Department (Admin, Logistics, Sales, Accounting) • Years • Gender

Step 2: Schema 1st Page: The Site will Ask for the Lastname Does the record exist? Error Page: The Site give a feedback False True 2nd Page: The Site will Show the Record

Submission Show your work on next Tuesday.

SQL Module 3: Mastering PHP-MySQL Connection