Chapter 4 DEPARTMENT OF INFORMATIONTECHNOLOGY Advanced internet programing

  • Slides: 52
Download presentation
Chapter 4 DEPARTMENT OF INFORMATIONTECHNOLOGY Advanced internet programing BY: Yitayew Solomon 2017 E-mail: yitayewsolomon

Chapter 4 DEPARTMENT OF INFORMATIONTECHNOLOGY Advanced internet programing BY: Yitayew Solomon 2017 E-mail: yitayewsolomon [email protected] com 3/9/2021 1

Outline of the chapter α α α α α Introduction Connecting PHP and MYSQL

Outline of the chapter α α α α α Introduction Connecting PHP and MYSQL server Creating database and table Adding records to database Selecting records from database Updating database records Deleting records from database Crating login page Record manipulation through validated form 3/9/2021 2

PHP My. SQL Introduction What is My. SQL? § My. SQL is a database

PHP My. SQL Introduction What is My. SQL? § My. SQL is a database system used on the web and runs on a server § My. SQL is ideal for both small and large applications § My. SQL is very fast, reliable, and easy to use § My. SQL supports standard SQL § My. SQL compiles on a number of platforms § My. SQL is free to download and use § My. SQL is developed, distributed, and supported by Oracle Corporation § My. SQL is named after co-founder Monty Widenius's daughter: 3/9/2021 3

 The data in My. SQL is stored in tables. A table is a

The data in My. SQL is stored in tables. A table is a collection of related data, and it consists of columns and rows. QUERIES ü A query is a question or a request. We can query a database for specific information and have a record set returned. Look at the following query (using standard SQL): SELECT Items? ? . Select * form student where id = 10; 3/9/2021 4

PHP Connect to the My. SQL Server v. Use the PHP mysqli_connect () function

PHP Connect to the My. SQL Server v. Use the PHP mysqli_connect () function to open a new connection to the My. SQL server. Open a Connection to the My. SQL Server üBefore we can access data in a database, we must open a connection to the My. SQL server. üIn PHP, this is done with the mysqli_connect() function. Syntax v mysqli_connect(“host”, “username”, “password”, “dbname”); 3/9/2021 5

Parameter Description Host Optional. Either a host name or an IP address username Optional.

Parameter Description Host Optional. Either a host name or an IP address username Optional. The My. SQL user name password Optional. The password to log in with dbname Optional. The default database to be used when performing queries Note: There are more available parameters, but the ones listed above are the most important. 3/9/2021 6

PHP Create Database and Tables 3/9/2021 7

PHP Create Database and Tables 3/9/2021 7

In the following example we store the connection in a variable ($con) for later

In the following example we store the connection in a variable ($con) for later use in the script: 3/9/2021 8

PHP Create Database and Tables • A database holds one or more tables. Create

PHP Create Database and Tables • A database holds one or more tables. Create a Database The CREATE DATABASE statement is used to create a database table in My. SQL. • We must add the CREATE DATABASE statement to the mysqli_query() function to execute the command. 3/9/2021 9

How to create DB by using code? ? ? The following example creates a

How to create DB by using code? ? ? The following example creates a database named "my_db": 3/9/2021 10

 How to Create a Table § The CREATE TABLE statement is used to

How to Create a Table § The CREATE TABLE statement is used to create a table in My. SQL. § We must add the CREATE TABLE statement to the mysqli_query() function to execute the command. § The following example creates a table named ", "student", with three columns. The column names will be “firstname", “lastname" and “age": 3/9/2021 11

Creates a table named "student", with three columns 3/9/2021 12

Creates a table named "student", with three columns 3/9/2021 12

Creating database and table manually 1. Creating database • To create database and table

Creating database and table manually 1. Creating database • To create database and table manually we follow the following step: • Step 1: write localhost/phpmyadmin on web address of the browser. • Step 2: click on database. • Step 3: set the database name and click on create. 3/9/2021 13

Cont. … 3/9/2021 14

Cont. … 3/9/2021 14

2. Creating table Cont. …. Step 1: open the database that you have created

2. Creating table Cont. …. Step 1: open the database that you have created previously. Step 2: after opening the database fill the name of the table and number of columns and click on GO. 3/9/2021 15

Primary Keys and Auto Increment Fields • • Each table in a database should

Primary Keys and Auto Increment Fields • • Each table in a database should have a primary key field. A primary key is used to uniquely identify the rows in a table. Each primary key value must be unique within the table. Furthermore, the primary key field cannot be null because the database engine requires a value to locate the record. The following example sets the PID field as the primary key field. The primary key field is often an ID number, and is often used with the AUTO_INCREMENT setting. AUTO_INCREMENT automatically increases the value of the field by 1 each time a new record is added. To ensure that the primary key field cannot be null, we must add the NOT NULL setting to the field: 3/9/2021 16

Example $sql = "CREATE TABLE Persons ( PID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(PID),

Example $sql = "CREATE TABLE Persons ( PID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(PID), Firstname CHAR(15), Lastname CHAR(15), Age INT )"; 3/9/2021 17

How to add records in to MYSQL? ? PHP My. SQL Insert Into The

How to add records in to MYSQL? ? PHP My. SQL Insert Into The INSERT INTO statement is used to insert new records in a table. Insert Data Into a Database Table The INSERT INTO statement is used to add new records to a database table. 3/9/2021 18

Syntax It is possible to write the INSERT INTO statement in two forms. The

Syntax It is possible to write the INSERT INTO statement in two forms. The first form doesn't specify the column names where the data will be inserted, only their values: INSERT INTO table_name VALUES (value 1, value 2, value 3, . . . ) The second form specifies both the column names and the values to be inserted: INSERT INTO table_name (column 1, column 2, column 3, . . . ) VALUES (value 1, value 2, value 3, . . . ) 3/9/2021 19

Insert Data From a Form Into a Database output 3/9/2021 20

Insert Data From a Form Into a Database output 3/9/2021 20

Insert. php 3/9/2021 21

Insert. php 3/9/2021 21

Cont. … § When a user clicks the submit button in the HTML form

Cont. … § When a user clicks the submit button in the HTML form in the example above, the form data is sent to "insert. php". § The "insert. php" file connects to a database, and retrieves the values from the form with the PHP $_POST variables. § Then, the mysqli_query() function executes the INSERT INTO statement, and a new record will be added to the “student" table. 3/9/2021 22

 PHP My. SQL Select § The SELECT statement is used to select data

PHP My. SQL Select § The SELECT statement is used to select data from a database. Select/search Data From a Database Table § The SELECT statement is used to select data from a database. Syntax SELECT column_name(s) FROM table_name 3/9/2021 23

Example selects all the data stored in the "student" table § (The * character

Example selects all the data stored in the "student" table § (The * character selects all the data in the table): 3/9/2021 24

Display the Result in an HTML Table by using this code output 3/9/2021 25

Display the Result in an HTML Table by using this code output 3/9/2021 25

PHP My. SQL UPDATE Query • The UPDATE statement is used to update the

PHP My. SQL UPDATE Query • The UPDATE statement is used to update the records in a My. SQL database table. Updating Database Table Data • The UPDATE statement is used to change or modify the existing records in a database table. • It is typically used in conjugation with the WHERE clause to apply the changes to only those records that matches specific criteria. • The basic syntax of the UPDATE statement can be given with: UPDATE table_name SET column 1=value, column 2=value 2, . . . WHERE column_name = some_value • Let's make a SQL query using the UPDATE statement and WHERE clause, after that we will execute this SQL query through passing it to the mysqli_query() function to update the tables records. • Consider the following " student" table inside the " My_db" database: 3/9/2021 26

Code for update form output 3/9/2021 27

Code for update form output 3/9/2021 27

Update. php 3/9/2021 28

Update. php 3/9/2021 28

PHP My. SQL DELETE Query The DELETE statement is used to delete the records

PHP My. SQL DELETE Query The DELETE statement is used to delete the records from a My. SQL database table. Deleting Database Table Data • Just as you insert records into tables, you can delete records from table using the DELETE statement. It is typically used in conjugation with the WHERE clause to delete only those records that matches specific criteria. • The basic syntax of the DELETE statement can be given with: • DELETE FROM table_name WHERE column_name=some_ value or It's DELETE FROM <table> WHERE <condition> 3/9/2021 29

Deleting record with out form 3/9/2021 30

Deleting record with out form 3/9/2021 30

Delete records from database with HTML form ü In some cases a company’s stores

Delete records from database with HTML form ü In some cases a company’s stores data in the My. SQL database. ü But if users or administrators wants to remove un necessary data from the database, then programmers design a HTML form for the administrator to remove the record from the database. Example. ü I have created a database called “my_db” with a table named “student’ with 3 fields “fname” , “lname” and “age”. ü I have created a HTML file with a HTML form called “deleteform. php/html”. ü I have created a php file called “delete. php”. 3/9/2021 31

con’t… • Here are the codes for deleteform. php/html: • A HTML form with

con’t… • Here are the codes for deleteform. php/html: • A HTML form with 1 text field and two delete and reset button create here. User has to input the name into the “name” text field. When the submit button is clicked, the form will direct us to the “delete. php” file: Output 3/9/2021 32

Con’t… For delete. php: • First we have to connect to database. i. e.

Con’t… For delete. php: • First we have to connect to database. i. e. mysql_connect(“localhost", “root", "") or die("Connection Failed"); mysql_select_db(“my_db")or die("Connection Failed"); Then we create a PHP variable to hold the value from the text field “fname” of the HTML form. $fname= $_POST[‘fname']; 3/9/2021 33

Now we create the query for deleting all of the records which equals to

Now we create the query for deleting all of the records which equals to the name from the text field “fname” of the HTML form. $query = “DELETE FROM student WHERE fname= '$fname'"; Now we run the query with the “mysql_query( )” PHP function and to check whether the records have deleted successfully, I have put an “if” condition there to echo out the result. if(mysql_query($query)) { echo "deleted"; } else { echo "fail"; } 3/9/2021 34

Con’t… • All together for delete. php: 3/9/2021 35

Con’t… • All together for delete. php: 3/9/2021 35

Design login page Login form code > 3/9/2021 36

Design login page Login form code > 3/9/2021 36

Output 3/9/2021 37

Output 3/9/2021 37

Insert. php used for to insert username and password 3/9/2021 38

Insert. php used for to insert username and password 3/9/2021 38

Insert username and password 3/9/2021 39

Insert username and password 3/9/2021 39

Login page code 3/9/2021 40

Login page code 3/9/2021 40

Cont. … loginsert 2. php 3/9/2021 Exercise: write a php code that changes username

Cont. … loginsert 2. php 3/9/2021 Exercise: write a php code that changes username and password of administrator. 41

How to write Data INTO Database with validated form? Validated form: 3/9/2021 42

How to write Data INTO Database with validated form? Validated form: 3/9/2021 42

Output Form 3/9/2021 43

Output Form 3/9/2021 43

Vinsert. php 3/9/2021 44

Vinsert. php 3/9/2021 44

Searching records from database in the form of table 3/9/2021 45

Searching records from database in the form of table 3/9/2021 45

Cont. … output Exercise: search specific record from database by using validated form!!!! 3/9/2021

Cont. … output Exercise: search specific record from database by using validated form!!!! 3/9/2021 46

Update database record from validated form 3/9/2021 47

Update database record from validated form 3/9/2021 47

output 3/9/2021 48

output 3/9/2021 48

Vupdate. php 3/9/2021 49

Vupdate. php 3/9/2021 49

Deleting database record from validated form output 3/9/2021 50

Deleting database record from validated form output 3/9/2021 50

Vdelete. php 3/9/2021 51

Vdelete. php 3/9/2021 51

<<END OF CHAPTER 4>> READ MORE!!!! 3/9/2021 52

<<END OF CHAPTER 4>> READ MORE!!!! 3/9/2021 52