It4552 - Web Programming: Chapter 6.1. Using Mysql With PHP

Objectives
❖ To understand the advantages of using databases

to store Web data
❖ To learn how to prepare a MySQL database for use
with PHP
❖ To learn how to store, retrieve, and update data in
IT4552 – Web programming a MySQL database
Chapter 6.1. Using MySQL

with PHP
1 2
1 2
Content What is a database?

1. Database and MySQL Overview ❖ A set of data organized into one or more
2. Basic SQL commands computer files.
3. Creating a table ❖ Using files for product inventory is a type of
database
4. Inserting data to a table
❖ Generally the term is reserved for more formal
5. Retrieving data from a table database systems like access, Oracle or MySQL.
6. Updating data for a table
3 4
3 4
1
Advantages of Databases
Over Files
Relational Database?
❖ Faster access ❖ A database is a collection of tables with defined
relationships between them
❖ Better concurrent access
❖ Columns define attributes of the data
❖ Easier changes to data and scripts ▪ All data in a column must have the same data type
❖ Increased security ❖ A record is stored in a row
table name
Employees
First Name Last Name Phone
Nadia Li 2687
Madhu Charu 7856
row Ajuma
Wade
Kinsaka
Randal
4489
5257
Helen Clark 2147
column
5 6
5 6
Which Database System Using A Query Language

❖ PHP works with a variety of databases that ❖ When using a database, use a separate query
include: language to work with database
▪ Oracle ❖ Within MySQL, use Structured Query Language
▪ Access
(SQL), to access database
▪ Ingres
▪ SQL Server
▪ MySQL
❖ Will use MySQL since simple to use, free and very
popular.
Send
SQL Query MySQL
PHP
Script Query Database
Results
7 8
7 8
2
Content 2. Basic SQL commands
1. Database and MySQL Overview - Connecting to MySQL from the Command Line
2. Basic SQL commands mysql –u username -p
3. Creating a table
E.g.:
>mysql –u root
5. Retrieving data from a table
- To EXIT MySQL:
EXIT;
9 10
2. Basic SQL Commands (2) 2. Basic SQL Commands (2)

❖ SQL statements end with a semicolon ❖ Use database databasename
❖ View databases USE databasename;
SHOW DATABASES; ❖ Display all tables in a database
❖ Creating a database SHOW TABLES;
CREATE DATABASE trii; ❖ View column details for a table
❖ Importing a database: DESC tablename;
mysql -uusername -ppassword
databasename < filename.sql
E.g.:
mysql -uroot trii < trii.sql
11 12
3
Creating a Database Instance Content
❖ Once you have access to a server with MySQL 1. Database and MySQL Overview
installed, need to get a database instance created 2. Basic SQL commands
for you.
▪ Usually created by a database administrator 3. Creating a table
▪ Creates a database instance, userid and password. 4. Inserting data to a table
13 14
13 14
3. Creating a table MySQL Data Types

❖ Once database instance is created need to create ❖ TEXT
your tables. ▪ hold a large amount of character data
▪ Use SQL CREATE TABLE command ▪ Use space inefficiently since it reserves space for up to 65,535
characters.
❖ CHAR(N)
▪ hold a fixed length string of up to N characters (N must be less
than 256).
❖ VARCHAR(N)
▪ hold a variable length string of up to N characters
▪ removes any unused spaces on the end of the entry.
15 16
15 16
4
MySQL Data Types (2) Some additional CREATE TABLE Options
❖ INT ❖ Can specify some additional options in CREATE
▪ hold an integer with a value from about –2 billion to about 2 billion. TABLE:
❖ INT UNSIGNED
▪ hold an integer with a value from 0 to about 4 billion.
❖ SMALLINT
▪ hold an integer with a value from –32,768 to 32,767.
❖ SMALLINT UNSIGNED
▪ hold an integer with a value from 0 to 65,535.
❖ DECIMAL(N,D)
▪ a number that supports N total digits, of which D digits are to the right of
the decimal point.
17 18
17 18
Issuing CREATE TABLE From PHP Script Full Script

Segment 1. <html><head><title>Create Table</title></head><body>
2. <?php
1. $connect = mysql_connect($server, $user, $pass); 3. $server = 'localhost';
2. if ( !$connect ) { 4. $user = 'phppgm';
3. die ("Cannot connect to $server using $user"); 5. $pass = 'mypasswd';
4. } else { 6. $mydb = 'mydatabase';
5. mysql_select_db('MyDatabaseName'); Connect to 7. $table_name = 'Products';
6. $SQLcmd = 'CREATE TABLE Products( MySQL 8. $connect = mysql_connect($server, $user, $pass);
ProductID INT UNSIGNED NOT NULL
9. if (!$connect) {
AUTO_INCREMENT PRIMARY KEY,
10. die ("Cannot connect to $server using $user");
Product_desc VARCHAR(50), Cost INT,
Weight INT, Numb INT)'; 11. } else {
7. mysql_query($SQLcmd, $connect); 12. $SQLcmd = "CREATE TABLE $table_name (
8. mysql_close($connect); ProductID INT UNSIGNED NOT NULL
9. }
Issue the SQL query AUTO_INCREMENT PRIMARY KEY,
to the database. Product_desc VARCHAR(50),
Cost INT, Weight INT, Numb INT)";
Updated API: https://www.w3schools.com/php/php_mysql_connect.asp 19 20
19 20
5
Full Script (2) Script Browser Output
13. mysql_select_db($mydb);
14. if (mysql_query($SQLcmd, $connect)){
15. print '<font size="4" color="blue" >Created Table';
16. print "<i>$table_name</i> in database<i>$mydb</i><br></font>";
17. print "<br>SQLcmd=$SQLcmd";
18. } else {
19. die ("Table Create Creation Failed SQLcmd=$SQLcmd");
20. }
21. mysql_close($connect);
22. }
23. ?></body></html>
21 22
21 22
MySQL Visual Designer Tools Content

❖ phpMyAdmin (web-app) 1. Database and MySQL Overview
❖ MySQL Workbench (Win, Linux, Mac) 2. Basic SQL commands
❖ SQLyog 3. Creating a table
❖… 4. Inserting data to a table
23 24
23 24
6
❖ Once database is created will need to insert data A Full Example
❖ Use the SQL INSERT command
❖ Consider an application that allows end-user to enter
inventory data:
Item Description: <input type="text" size="20"
maxlength="20" name="Item">
Weight: <input type="text" size="5"
maxlength="20" name="Weight">
Cost: <input type="text" size="5"
maxlength="20" name="Cost">
Number Available:<input type="text" size="5"
maxlength="20" name="Quantity">
25 26
25 26
1. <html><head><title>Insert Results</title></head><body>
2. <?php
3. $host = 'localhost'; Script Output
4.
5.
$user = 'phppgm';
$passwd = 'mypasswd';
Receiving PHP Script
6. $database = 'mydatabase';
7. $connect = mysql_connect($host, $user, $passwd);
8. $table_name = 'Products';
9. $query = "INSERT INTO $table_name VALUES
('0','$Item','$Cost','$Weight','$Quantity')";
10. print "The Query is <i>$query</i><br>";
11. mysql_select_db($database);
12. print '<br><font size="4" color="blue">';
13. if (mysql_query($query, $connect)){
14. print "Insert into $database was successful!</font>";
15. } else {
16. print "Insert into $database failed!</font>";
17. } mysql_close ($connect);
18. ?></body></html>
27 28
27 28
7
Content 5. Retrieving data from a table
1. Database and MySQL Overview ❖ Two major ways to retrieve data:
2. Basic SQL commands ▪ Retrieving all elements from a table
▪ Searching for specific records in a table
3. Creating a table
❖ To retrieve all data, use following SQL command
29 30
29 30
5.1. Using mysql_fetch_row()

5. Retrieving Data (2) ❖ Use the mysql_fetch_row() function to retrieve
data on row at a time
❖ To retrieve all data, use following SQL command
❖ For example
1. $connect = mysql_connect('Localhost', 'phppgm',
'mypasswd');
2. $SQLcmd = 'SELECT * FROM Products';
3. mysql_select_db('MyDatabase');
4. $results_id = mysql_query($SQLcmd, $connect);
31 32
31 32
8
1. <html><head><title>Table Output</title></head><body>
2. <?php
3. $host= 'localhost';
A Script Example A Script Example (2)
4. $user = 'phppgm';
5. $passwd = 'mypasswd';
6. $database = 'phppgm'; 18. while ($row = mysql_fetch_row($results_id)){
7. $connect = mysql_connect($host, $user, $passwd);
19. print '<tr>';
9. print '<font size="5" color="blue">'; 20. foreach ($row as $field) {
10. print "$table_name Data</font><br>"; 21. print "<td>$field</td> ";
11. $query = "SELECT * FROM $table_name"; 22. }
12. print "The query is <i>$query </i><br>"; 23. print '</tr>';
24. }
14. $results_id = mysql_query($query, $connect);
15. if ($results_id) { 25. } else { die ("Query=$query failed!"); }
16. print '<table border=1>'; 26. mysql_close($connect);
17. print '<th>Num<th>Product<th>Cost<th>Weight<th>Count'; 27. ?> </table></body></html>
33 34
33 34
5.2. Searching For Specific Records

Script Output ❖ Use the SELECT SQL statement with a WHERE
clause
▪ SELECT * FROM TableName WHERE
(test_expression);
Specify a test expression

to evaluate
Specify the table name
to look at.
The asterisk (“*”)
means
look at all table
columns.
35 36
35 36
9
Consider the following
Selected WHERE CLAUSE Test example …
Operators ❖ The following example searches a hardware
inventory database for a specific part name
Operator SQL Query Example Meaning entered by the user.
= SELECT * FROM Products Retrieve those rows from the Products
WHERE table that have a Product_desc column ❖ The form uses the following key HTML form
(Product_desc = 'Hammer'); with a value equal to Hammer. element definition.
> SELECT * FROM Products Retrieve those rows from the Products ▪ <input type="text" name="Search"
WHERE (Cost > '5'); table that have a Cost column with a size="20">
value greater than 5.
< SELECT * FROM Products Retrieve those rows from the Products
WHERE (Numb < '3'); table that have a Numb column with a
value less than 3.
<= SELECT * FROM Products Retrieve those rows from the Products
WHERE (Cost <= '3'); table that have a Cost column with a
value less than or equal to 3.
>= SELECT * FROM Products Retrieve those rows from the Products
WHERE (Weight >= '10'); table that have a Weight column with a
value greater than or equal to 10 .
Slide 8-37
38
37 38
PHP Source
PHP Source (2)
1. <html><head><title>Search Results</title></head><body>
2. <?php
3. $host= 'localhost'; 15. if ($results_id) {
4. $user = 'phppgm'; 16. print '<br><table border=1>';
5. $passwd = 'mypasswd'; 17. print '<th>Num<th>Product<th>Cost<th>Weight <th>Count';
6. $database = 'phppgm'; 18. while ($row = mysql_fetch_row($results_id)) {
7. $connect = mysql_connect($host, $user, $passwd); 19. print '<tr>';
8. $table_name = 'Products'; 20. foreach ($row as $field) {
9. print '<font size="5" color="blue">'; 21. print "<td>$field</td> ";
10. print "$table_name Data</font><br>"; 22. }
11. $query = "SELECT * FROM $table_name WHERE 23. print '</tr>';
(Product_desc = '$Search')"; 24. }
12. print "The query is <i>$query</i> <br>"; 25. } else { die ("query=$Query Failed");}
13. mysql_select_db($database); 26. mysql_close($connect);
14. $results_id = mysql_query($query, $connect); 27. ?> </body></html>
39 40
39 40
10
Would have the following output … Content
1. Database and MySQL Overview
2. Basic SQL commands
3. Creating a table
41 42
41 42
For Example …
6. Updating data for a table ❖ The following searches the Products table for
values of Product_desc equal to Hammer.
❖ Use SQL UPDATE command when needing to update a UPDATE Products
database record: SET Cost=2
Specify the name of WHERE Product_desc = 'Hammer'
UPDATE Table_name the table to update.
SET col1=chng_express1,col2=chng_express2, ...
WHERE test_expression
Optionally specify Specify one or more table column to

a WHERE clause receive the results of an expression.
Optionally specify a WHERE
and test expression. 44
43
43 44
11
For Example … A Full Example …
❖ The following looks through the Products table for ❖ Consider the following example
values of Product_desc equal to Hammer. ▪ Displays current inventory
❖ When it finds it, it decrements the Count column ▪ Asks end-user to decrement value for 1 item
value by 1. ▪ Uses the following HTML
Hammer: <input type="radio" name="Product"
value="Hammer">
UPDATE Products Screwdriver: <input type="radio"
SET Count=Count-1 name="Product"
WHERE 'Product_desc=Hammer' value="Screwdriver”>
Wrench: <input type="radio" name="Product"
value="Wrench">
45 46
45 46
Full Example
1. <html><head><title>Product Update
A Full Example (2)
Results</title></head><body>
2. <?php
3. $host= 'localhost';
15. if ($results_id){
4. $user = 'phppgm';
5. $passwd = 'mypasswd'; 16. Show_all($connect, $database,$table_name);
6. $database = 'phppgm'; 17. } else {
7. $connect = mysql_connect($host, $user, $passwd); 18. print "Update=$query failed";
19. }
9. print '<font size="5" color="blue">';
10. print "Update Results for Table 20. mysql_close($connect);
$table_name</font><br>\n";
11. $query = "UPDATE $table_name
SET Numb = Numb-1
WHERE (Product_desc = '$Product')";
12. print "The query is <i> $query </i> <br><br>\n";
47 48
47 48
12
A Full Example (3) Would output the following:
21. function Show_all($connect, $database, $table_name){
22. $query = "SELECT * from $table_name";
24. print '<table border=1><th> Num </th>
<th>Product</th><th>Cost</th>
<th>Weight</th><th>Count</th>';
26. while ($row = mysql_fetch_row($results_id)) {
27. print '<tr>';
28. foreach ($row as $field){
29. print "<td>$field</td> ";
30. }
31. print '</tr>';
32. }
33. }
34. ?> </body></html>
49
Slide 8-50
49 50
Question?
51
51
13

It4552 - Web Programming: Chapter 6.1. Using Mysql With PHP

Uploaded by

Copyright:

Available Formats

It4552 - Web Programming: Chapter 6.1. Using Mysql With PHP

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

It4552 - Web Programming: Chapter 6.1. Using Mysql With PHP

Uploaded by

Copyright:

Available Formats

Objectives

❖ To understand the advantages of using databases

Chapter 6.1. Using MySQL

Content What is a database?

Which Database System Using A Query Language

2. Basic SQL Commands (2) 2. Basic SQL Commands (2)

3. Creating a table MySQL Data Types

Issuing CREATE TABLE From PHP Script Full Script

MySQL Visual Designer Tools Content

5.1. Using mysql_fetch_row()

5.2. Searching For Specific Records

Specify a test expression

Optionally specify Specify one or more table column to

You might also like