Urdaneta City University Elective 102: Server Side Coding
College of Computer Studies Module 7: PHP MySql
PHP MYSQL
OBJECTIVES At the end of the module, students are expected to:
1. Use database administration tool
2. Describe database functions
3. Combine PHP with MySQLi database
4. Using SQL to create and drop databases and tables
5. Using SQL to insert, delete, and update data
What is MySQL?
MySQL is a database.
The world most popular open-source database.
Companies using MySQL are Yahoo!, Google, Cisco, NASA and HP.
The data in MySQL is stored in database objects called tables.
PHPMyAdmin
An open-source browser-based MySQL manager.
Used for creating databases, tables and inserting and deleting data
Basic SQL
SQL can create new databases
SQL can create new tables in a database
SQL can insert records in a database
SQL can update records in a database
SQL can retrieve data from a database
SQL can delete records from a database
Creating Database
The CREATE DATABASE statement is used to create a database.
Syntax:
CREATE DATABASE database_name
Creating Tables
The CREATE TABLE statement is used to create a table in a database.
Syntax:
CREATE TABLE table_name (
column_name1 data_type,
column_name2 data_type,
column_name3 data_type, .... );
Inserting Records
The INSERT INTO statement is used to insert a new row in a table.
dnnelcaguin 1
Urdaneta City University Elective 102: Server Side Coding
College of Computer Studies Module 7: PHP MySql
Syntax:
INSERT INTO table_name VALUES (value1, value2, value3,...);
Updating Records
The UPDATE statement is used to update existing records in a table.
Syntax:
UPDATE table_name SET column1=value, column2=value2,... WHERE
some_column=some_value;
Deleting Records
The DELETE statement is used to delete rows in a table.
Syntax:
DELETE FROM table_name WHERE some_column=some_value;
MySQL Database
Use mysqli_connect() to open connection to the database.
Syntax:
mysqli_connect(servername,username,password,database);
Parameter Description
Servername Optional. Specifies the server to connect to. Default value is
"localhost:3306"
Username Optional. Specifies the username to log in with. Default value is the name
of the user that owns the server process
Password Optional. Specifies the password to log in with. Default is ""
Database name Specifies the database name.
<?php
$dbconnect = mysqli_connect(‘localhost’,’root’,’’,’dbname’) or
die(mysqli_error());
//localhost hostname
//root username
//passpassword
//dbnamedatabase name
//die()... Terminates the program, if there are errors
//mysqli_error()error id
?>
dnnelcaguin 2
Urdaneta City University Elective 102: Server Side Coding
College of Computer Studies Module 7: PHP MySql
Query – it is a question or a request. For example: querying a database.
mysqli_query();
This function is used to send a query or command to a MySQL connection.
If you are done, close the database by using mysqli_close() function.
Example:
mysqli_close($dbconnect);
Inserting/Storing data to the database
$query = “INSERT INTO tblitems(‘name’,’price’) VALUES (‘Liquid
Eraser’,50.75)”;
$query = ‘INSERT INTO tblitems SET name=“Liquid Eraser”, price=50.75’;
mysqli_query($query,$dbconnect);
Updating data to the database
$query = “UPDATE tblitems SET name=’all Pen’ where empid= ’2’”;
mysqli_query($query,$dbconnect);
Deleting data to the database
$query = “DELETE FROM tblitems where ItemID=2”;
mysqli_query($query,$dbconnect);
Querying the mysql database using
mysqli_query()
<?php
$query = ‘SELECT * FROM tbllist’;
$result = mysqli_query($query,$dbconnect)’
//*means all fields of the table tbllist
//tbbllist is a table inside the database
?>
dnnelcaguin 3
