ICT 5 Web Development - Chapter 6.1: Using MySQL with PHP

pdf
Số trang ICT 5 Web Development - Chapter 6.1: Using MySQL with PHP 13 Cỡ tệp ICT 5 Web Development - Chapter 6.1: Using MySQL with PHP 371 KB Lượt tải ICT 5 Web Development - Chapter 6.1: Using MySQL with PHP 0 Lượt đọc ICT 5 Web Development - Chapter 6.1: Using MySQL with PHP 0
Đánh giá ICT 5 Web Development - Chapter 6.1: Using MySQL with PHP
4.1 ( 4 lượt)
Nhấn vào bên dưới để tải tài liệu
Đang xem trước 10 trên tổng 13 trang, để tải xuống xem đầy đủ hãy nhấn vào bên trên
Chủ đề liên quan

Nội dung

Vietnam and Japan Joint ICT HRD Program Objectives ‹ To understand the advantages of using i d databases t b to t store t Web W b data d t ‹ To learn how to prepare a MySQL database for use with PHP ‹ To learn how to store, retrieve, and update data in a MySQL database ICT 5 Web Development Chapter 6.1. Using MySQL with PHP Nguyen Thi Thu Trang trangntt--fit@mail.hut.edu.vn trangntt 2 Content What is a database? 1. Database and MySQL Overview 2. Basic SQL commands 3. Creating a table 4. Inserting data to a table 5. Retrieving data from a table 6. Updating data for a table ‹A set of data organized into one or more computer t files. fil ‹ Using files for product inventory is a type of database ‹ Generally the term is reserved for more formal database systems like access, Oracle or MySQL. 3 4 1 Advantages of Databases Over Files Relational Database? ‹ Faster access ‹ Better concurrent access ‹ Easier changes to data and scripts ‹ Increased security ‹ ‹ A database is a collection of tables with defined relationships between them Columns define attributes of the data – All data in a column must have the same data type ‹ A record is stored in a row table name row First Name Nadia Madhu Ajuma Wade Helen column 5 Which Database System Employees Last Name Phone Li 2687 Charu 7856 Kinsaka 4489 Randal 5257 Clark 2147 6 Using A Query Language ‹ PHP works with a variety of databases d t b that th t include: i l d ‹ When using a database, use a separate t query language l to t work k with database ‹ Within MySQL, use Structured Query Language (SQL), to access database – Oracle – Access – Ingres – SQL Server – MySQL PHP Script ‹ Will use MySQL since simple to use, free and very popular. 7 Send SQL Query Query Results MySQL Database 8 2 2. Basic SQL commands Content - Connecting to MySQL from the Command Line mysql -uusername -p 1. Database and MySQL Overview 2. Basic SQL commands 3. Creating a table 4. Inserting data to a table 5. Retrieving data from a table 6. Updating data for a table E.g.: >mysql -uroot - To EXIT MySQL: EXIT; EXIT ; 9 2. Basic SQL Commands (2) ‹ ‹ SQL statements end with a semicolon View databases SHOW DATABASES; ‹ ‹ ‹ Creating a database CREATE DATABASE trii; ‹ 2. Basic SQL Commands (2) Importing a database: ‹ Use database databasename USE databasename databasename; ; Display all tables in a database SHOW TABLES; View column details for a table DESC tablename tablename; ; mysql -uusername -ppassword databasename < filename.sql E.g.: mysql -uroot trii < trii.sql 3 Content Creating a Database Instance ‹ Once you have access to a server with ith M MySQL SQL iinstalled, t ll d need d tto gett a database instance created for you. – Usually created by a database administrator – Creates a database instance, userid and password. 1. Database and MySQL Overview 2. Basic SQL commands 3. Creating a table 4. Inserting data to a table 5. Retrieving data from a table 6. Updating data for a table 13 14 MySQL Data Types 3. Creating a table ‹ Once database instance is created need d to t create t your tables. t bl ‹ TEXT – hold a large amount of character data – Use space inefficiently since it reserves space for up to 65,535 characters. – Use SQL CREATE TABLE command ‹ 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 4 Some additional CREATE TABLE Options MySQL Data Types (2) ‹ ‹ Can specify some additional options in i CREATE TABLE TABLE: INT – hold an integer with a value from about –2 billion to about 2 billion. ‹ INT UNSIGNED ‹ SMALLINT ‹ SMALLINT UNSIGNED ‹ DECIMAL(N,D) – hold an integer with a value from 0 to about 4 billion. – hold an integer with a value from –32,768 to 32,767. – hold an integer with a value from 0 to 65,535. – a number that supports N total digits, of which D digits are to the right of the decimal point. 17 Issuing CREATE TABLE From PHP Script Segment 1. $connect = mysql_connect($server, $user, $pass); 2. if ( !$connect ) { 3. die ("Cannot connect to $server using $user"); 4. } else { Connect to 5. mysql_select_db('MyDatabaseName'); MySQL 6. $SQLcmd = 'CREATE TABLE Products( ProductID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, Product desc VARCHAR(50), Product_desc VARCHAR(50) Cost INT INT, Weight INT, Numb INT)'; 7. mysql_query($SQLcmd, $connect); Issue the SQL query 8. mysql_close($connect); to the database. 9. } 19 18 Full Script 1. Create Table 2. Created Table'; print "$table_name in database$mydb
"; >$mydb
"; 16. 17. print "
SQLcmd=$SQLcmd"; 18. } else { 19. die ("Table Create Creation Failed SQLcmd=$SQLcmd"); 20. } 21. mysql_close($connect); 22. } 23. ?> ?> 21 MySQL Visual Designer Tools 22 Content ‹ phpMyAdmin (web (web--app) ‹ MySQL Workbench (Win, Linux, Mac) ‹ SQLyog ‹… 23 1. Database and MySQL Overview 2. Basic SQL commands 3. Creating a table 4. Inserting data to a table 5. Retrieving data from a table 6. Updating data for a table 24 6 4. Inserting data to a table A Full Example ‹ Once database is created will need to i insert td data t ‹ Use the SQL INSERT command ‹ Consider an application that allows end end-user to enter inventory data: Item Description: Weight: Cost: Number Available: 25 1. 2. 3. 4. 5. 6. 7. 8. 9. Insert Results $query
"; 11. mysql_select_db($database); 12. print '
'; 13. if (mysql_query($query, (mysql_query($query, $connect) $connect)){ ){ 14. print "Insert into $database was successful!"; 15. } else { 16. print "Insert into $database failed!"; 17. } mysql_close ($connect); 18. ?> Receiving PHP Script 27 26 Script Output 28 7 Content 5. Retrieving data from a table 1. Database and MySQL Overview 2. Basic SQL commands 3. Creating a table 4. Inserting data to a table 5. Retrieving data from a table 6. Updating data for a table ‹ Two major ways to retrieve data: – Retrieving all elements from a table – Searching for specific records in a table ‹ To retrieve all data, use following SQL command 29 5.1. Using mysql_fetch_row() 5. Retrieving Data (2) ‹ ‹ 30 ‹ Use the mysql_fetch_row() function t retrieve to t i data d t on row att a time ti To retrieve all data, use following SQL command For example p 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 8 1. Table Output 2. '; 10. print "$table_name Data
"; 11. $query = "SELECT * FROM $table_name"; 12. print "The query is $query
"; 13. mysql_select_db($database); 14. $results_id = mysql_query($query, $connect); 15. if ($results_id) { 16. print ''; 17. print ''; 20. foreach ($row as $field) { 21. print " "; 22. } 23. print ''; 24. } 25 } else 25. l { di die ("Q ("Query=$query $ f failed!"); il d!") } 26. mysql_close($connect); 27. ?>
NumProductCostWeightCount'; A Script Example (2) A Script Example 18. while ($row = mysql_fetch_row($results_id)){ 19. print '
$field
33 34 5.2. Searching For Specific Records Script Output ‹ Use the SELECT SQL statement with a WHERE clause l – SELECT * FROM TableName WHERE (test_expression); The asterisk (“*”) means look at all table columns. 35 Specify the table name to look at. Specify a test expression to evaluate 36 9 Consider the following example … Selected WHERE CLAUSE Test Operators Operator = ‹ The following example searches a h d hardware inventory i t database d t b for f a specific part name entered by the user. ‹ The form uses the following key HTML form element definition. SQL Query Example Meaning SELECT * FROM Products Retrieve those rows from the Products WHERE table that have a Product_desc column (Product_desc = 'Hammer'); with a value equal to Hammer. > SELECT * FROM Products WHERE (Cost > '5'); < SELECT * FROM Products WHERE (Numb < '3'); <= SELECT * FROM Products WHERE (Cost <= '3'); >= SELECT * FROM Products WHERE (Weight >= '10'); Retrieve those rows from the Products table that have a Cost column with a value greater than 5. Retrieve those rows from the Products table that have a Numb column with a value l less than 3. Retrieve those rows from the Products table that have a Cost column with a value less than or equal to 3. Retrieve those rows from the Products table that have a Weight column with a value greater than or equal to 10. – 38 Slide 88-37 PHP Source PHP Source (2) 1. Search Results 2. '; 10. p print "$table $ _name Data
"; / ; 11. $query = "SELECT * FROM $table_name WHERE (Product_desc = '$Search')"; 12. print "The query is $query
"; 13. mysql_select_db($database); 14. $results_id = mysql_query($query, $connect); 39 15. 16 16. 17. 18. 19. 20. 21. 22. 23. 24. 25. 26. 27. if ($results_id) { print i ' b '
'; d 1 ' print ''; foreach ($row as $field) { print " "; } print ''; } } else { die ("query=$Query Failed");} mysql_close($connect); ?> 40 10
This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
NumProductCostWeight Count'; while ($row = mysql_fetch_row($results_id)) { print '
$field