CSE4004 – Web Technologies
Introduction to MySQL Database
MySQL Database Introduction 2
• MySQL is an open-source relational database management system
(RDBMS). It is the most popular database system used with PHP. MySQL
is developed, distributed, and supported by Oracle Corporation.
• The data in a MySQL database are stored in tables which consists of
columns and rows.
• MySQL is a database system that runs on a server.
• MySQL is ideal for both small and large applications.
• MySQL is very fast, reliable, and easy to use database system.It uses
standard SQL
• MySQL compiles on a number of platforms
November 23, 2024
Displaying Data from MySQL on the Web 3
November 23, 2024
Data 4
• Structured Data
o The structured form of data gets organized into a repository (formatted) that
acts as a typical database. Structured data works with all kinds of data that
one can store in the SQL database in a table that consists of columns and
rows. Relational data is one of the most commendable examples of
Structured Data.
• Semi-Structured Data
o Type of information and data that does not get stored in a relational type of
database but has organizational properties that facilitate an easier analysis.
XML data is an example of semi-structured data.
• UnStructured Data
o Type of data structure that does not exist in a predefined organized manner.
A few examples of the unstructured data structure are Text, PDF, Media
logs, Word, etc
November 23, 2024
Databases 5
• Structured collection of data.
– Tables
– Fields
– Query
– Reports
• Essentially a much more sophisticated implementation of the flat files.
• Relational Database
– Stores data in separate tables instead of a single store.
– Relationships between tables are set using common fields
– This provides a faster, more flexible database system.
November 23, 2024
MySQL Database 6
• The world's most popular open source database because of its
consistent fast performance, high reliability and ease of use
• Open Source License:- free
– GNU General Public License
– Free to modify and distribute but all modification must be available
in source code format
• Commercial:- not free
– Fully paid up professional support
• used by Google, Facebook Nokia, YouTube, Yahoo,Zappos.com, etc.
November 23, 2024
Basic DDL,DML and DCL syntax 7
November 23, 2024
DDL syntax 8
• CREATE DATABASE databasenam CREATE DATABASE Student_data;
• CREATE TABLE table_name (column1 datatype column2 datatype,
column3 datatype, ....);
CREATE TABLE Student ( StudendId int, LastName varchar(255),
FirstName varchar(255), Address varchar(255), Mark int );
• DROP object object_name DROP TABLE Student;
DROP DATABASE Student_data;
• ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE Student ADD Total int;
November 23, 2024
DDL syntax 9
• TRUNCATE TABLE table_name
TRUNCATE TABLE Student;
• ALTER DATABASE "Old_DatabaseName" RENAME TO New_DatabaseName";
ALTER DATABASE "Student_data" RENAME TO "Employee_data";
• Select * from Table_Name;(DQL)
November 23, 2024
MySQL Data Types 10
November 23, 2024
Create Table 11
• CREATE TABLE Table_Name (column_specifications)
• Example
mysql> CREATE TABLE student
-> (
-> student_ID INT UNSIGNED NOT NULL,
-> name VARCHAR(20) NOT NULL,
-> major VARCHAR(50),
-> grade VARCHAR(5)
-> );
Query OK, 0 rows affected (0.00 sec)
November 23, 2024
Display Table Structure 12
mysql> show tables;
+--------------------+
| Tables_in_MyDB |
+--------------------+
| student |
+--------------------+
1 row in set (0.00 sec)
mysql> describe student;
+---------------+----------------------+------+------+----------+--------+
| Field | Type | Null | Key | Default | Extra |
+---------------+----------------------+-------+-----+-----------+-------+
| student_ID | int(10) unsigned | | |0 | |
| name | varchar(20) | | | | |
| major | varchar(50) | YES | | NULL | |
| grade | varchar(5) | YES | | NULL | |
+---------------+----------------------+-------+------+----------+-------+
4 rows in set (0.00 sec)
November 23, 2024
Modify Table Structure 13
ALTER TABLE table_name Operations
mysql> alter table student add primary key (student_ID);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe student;
+---------------+--------------------- +-------+------+----------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+----------------------+-------+------+----------+-------+
| student_ID | int(10) unsigned | | PRI | 0 | |
| name | varchar(20) | | | | |
| major | varchar(10) | YES | | NULL | |
| grade | varchar(5) | YES | | NULL | |
+---------------+----------------------+-------+------+-----------+-------+
4 rows in set (0.00 sec)
November 23, 2024
Insert Record 14
INSERT INTO table_name SET col_name1=value1,
col_name2=value2, col_name3=value3, …
Example
mysql> INSERT INTO student SET student_ID=101, name='Shannon',
major='BCB', grade='A';
Query OK, 1 row affected (0.00 sec)
November 23, 2024
Retrieve Record 15
SELECT what_columns
FROM table or tables
WHERE condition
Example
mysql> SELECT major, grade FROM student WHERE
name='Shannon';
+-------+-------+
| major| grade|
+-------+-------+
| BCB | A |
+-------+-------+
1 row in set (0.00 sec)
mysql> SELECT * FROM student;
November 23, 2024
Update Record 16
UPDATE table_name SET which columns to change
WHERE condition
Example
mysql> UPDATE student SET grade='B' WHERE name='Shannon';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM student WHERE name=‘Shannon’;
+------------+---------------+--------+--------+
| name | student_ID | major | grade |
+------------+---------------+--------+--------+
| Shannon | 101 | BCB | B |
+------------+---------------+--------+--------+
1 row in set (0.00 sec)
November 23, 2024
Delete Record 17
DELETE FROM table_name WHERE condition
Example
mysql> DELETE FROM student WHERE name='Shannon';
Query OK, 1 row affected (0.00 sec)
Mysql> DELETE FROM student;
Will delete ALL student records!
November 23, 2024
Drop Table 18
DROP TABLE table_name
Example
mysql> drop table student;
Query OK, 0 rows affected (0.00 sec)
Logout MySQL
mysq> quit;
November 23, 2024
MySQl and PHP 19
• There are three ways of working with MySQl and PHP
• MySQLi (object-oriented)
• MySQLi (procedural)
• PDO
November 23, 2024
MySQl and PHP object-oriented connectivity 20
November 23, 2024
MySQl and PHP procedural connectivity 21
November 23, 2024
MySQl and PHP PDO(PHP Data Object) 22
November 23, 2024
How to connect php with mysql in xampp 23
November 23, 2024
Click Admin 24
CSE4004 WT November 23, 2024
PHP MyAdmin 25
November 23, 2024
Details Entry HTML Form 26
November 23, 2024
Details Entry Form HTML Code (sample_register.html)
27
<html>
<head><title>A Sample Tutorial for database connection.</title></head>
<body bgcolor="#32e692">
<div align="center"><!--<h1>Details Entry Form</h1>--></div>
<form action="details_entry.php" method="post">
<table border="1" align="center">
<tr><td> <label>Enter First Name</label> </td>
<td> <input type="text" name="first_name"> </td> </tr>
<tr> <td><label>Enter Last Name</label> </td>
<td> <input type="text" name="last_name"> </td> </tr>
<tr><td> <label>Gender</label></td>
<td><input type="radio" name="gender" value="male">Male
<input type="radio" name="gender" value="female">Female</td> </tr>
<tr><td> <label>Enter Email</label></td>
<td><input type="email" name="email"></td> </tr>
<tr><td> <label>Enter Phone</label></td>
<td><input type="phone" name="phone"></td> </tr>
<tr> <td colspan="2" align="center" ><input type="submit" name="save" value="Submit" style="font-
size:20px"></td>
</tr> </table></form></body></html>
November 23, 2024
MYSQL database connection with html form (details_entry.php)
28
<?php
$server_name="localhost";
$username="root";
$password="";
$database_name="vcb";
$conn=mysqli_connect($server_name,$username,$password,$database_name);
if(!$conn)
{ die("Connection Failed:" . mysqli_connect_error());
}
if(isset($_POST['save']))
{ $first_name = $_POST['first_name'];
$last_name = $_POST['last_name'];
$gender = $_POST['gender'];
$email = $_POST['email'];
$phone = $_POST['phone'];
CSE4004 WT November 23, 2024
MySQl and PHP PDO(PHP Data Object) 29
$sql_query = "INSERT INTO student
(first_name,last_name,gender,email,phone)
VALUES ('$first_name','$last_name','$gender','$email','$phone')";
if (mysqli_query($conn, $sql_query))
{
echo "New Details Entry inserted successfully !";
}
else {
echo "Error: " . $sql . "" . mysqli_error($conn);
}
mysqli_close($conn);
}?>
November 23, 2024
Front end data entry 30
November 23, 2024
Backend data stored in student table 31
November 23, 2024
PHP MySQL CRUD Application 32
• What is CRUD
CRUD is an acronym for Create, Read, Update, and Delete. CRUD operations
are basic data manipulation for database. We've already learned how to
perform create (i.e. insert), read (i.e. select), update and delete operations.
November 23, 2024
PHP XML 33
• SimpleXML is a PHP extension that allows us to easily manipulate
and get XML data.
• XML is a mark-up language to share the data across the web, XML
is for both human read-able and machine read-able.
• SimpleXML provides an easy way of getting an element's name,
attributes and textual content if you know the XML document's
structure or layout.
• An XML document is plain text and contains tags delimited by < and
>
November 23, 2024
PHP XML
34
CSE4004 WT November 23, 2024
Types of XML
35
• Tree based -Tree-based parsers holds the entire document in Memory and
transforms the XML document into a Tree structure
EX: SimpleXML, DOM
• Event based – Event-based parsers do not hold the entire document in
Memory, instead, they read in one node at a time.
Ex: XMLReader, XML Expat Parser
• XML Parse Extensions
– Simple XML parser-tree based XML parser and it will parse the simple XML file.
Simple XML parse will call simplexml_load_file() method to get access to the xml
from specific path.
– DOM XML parser-DOM Parser also called as a complex node parser, Which is
used to parse highly complex XML file. It is used as interface to modify the XML
file.
– XML parser-XML parsing is based on SAX parse. It is more faster the all above
parsers
– XML Reader-XML Reader parse also called as Pull XML parse. It is used to read
the XML file in a faster way.
CSE4004 WT November 23, 2024
How to Read XML using PHP 36
• $xml = simplexml_load_file(‘samp.xml');” uses the simplexml_load_file
function to load the file name samp.xml and assign the contents to the
array variable $xml.
• “$list = $xml->record;” gets the contents of the record node.
• “for ($i = 0; $i < count(…)…” is the for loop that reads the numeric
array and outputs the results
• “$list[$i]->attributes()->man_no;” reads the man_no attribute of the
element
• “$list[$i]->name;” reads the value of the name child element
• “$list[$i]->position;” reads the value of the position child element
November 23, 2024
How to Create an XML document using PHP 37
• “$dom = new DOMDocument();” creates an instance of DOMDocument
class.
• “$dom->encoding = 'utf-8';” sets the document encoding to utf-8
• “$dom->xmlVersion = '1.0';” specifies the version number 1.0
• “$dom->formatOutput = true;” ensures that the output is well
formatted
• “$root = $dom->createElement('Movies');” creates the root node
named Movies
• “$attr_movie_id = new DOMAttr('movie_id', '5467');” defines the movie
id attribute of Movies node
November 23, 2024
How to Create an XML document using PHP 38
• “$child_node_element_name = $dom->createElement('ElementName',
'ElementValue')” creates the child node of Movies node. ElementName
specifies the name of the element
• “$root->appendChild($movie_node);” appends the movie_node
elements to the root node Movies
• “$dom->appendChild($root);” appends the root node to the XML
document.
• “$dom->save($xml_file_name);” saves the XML file in the root
directory of the web server.
November 23, 2024
PHP AJAX
39
• AJAX = Asynchronous JavaScript and XML.
• AJAX is a technique for creating fast and dynamic web pages.
• AJAX allows web pages to be updated asynchronously by
exchanging small amounts of data with the server behind the scenes.
This means that it is possible to update parts of a web page, without
reloading the whole page.
• Examples of applications using AJAX: Google Maps, Gmail,
Youtube, and Facebook tabs.
November 23, 2024
PHP AJAX
40
November 23, 2024
AJAX is Based on Internet Standards
41
AJAX is based on internet standards, and uses a combination of:
– XMLHttpRequest object (to exchange data asynchronously with a
server)
– JavaScript/DOM (to display/interact with the information)
– CSS (to style the data)
– XML (often used as the format for transferring data)
November 23, 2024