Transcript Lecture Slides
PHP+MySQL Integration
Connecting to databases
• • • • • One of the most common tasks when working with dynamic webpages is connecting to a database which holds the content of the page PHP has several libraries that allows for communication with many different databases Some of these libraries have variations in the commands We will be using the mysql library Most functions start with mysql_ and then the name of the function 2
Working with databases –key steps 1. Design the DB – Create the DB on PHPMyAdmin or MySQL command 2. Design and create the HTML 3. Create a connection and select the database 4. Run the query – Verify Insert/Fetch the rows/Do other SQL associated tasks 5. Display Results as needed 6. Close the connection 3
1. Database Design and Creation Create database ‘hospital’, add a table ‘discharge’ with appropriate fields – patient id, name, appointment type, admission test, and date (to capture todays date) ward, xray examination, mri scan, cat scan, eye test, hearing
hospital
discharge (patid,name, appointment, ward, xray, mri, cscan, eye, hearing, date)
Data details
Field
patid name appointment ward xray mri cscan eye hearing date
Type
int(11) NN AI PK varchar(45) varchar(11) char(2) char(3) char(3) char(3) char(3) char(3) date
Comment
Primary Key Index Patient Full Name type of appointment: GP, Specialist or Surgery Admitted to ward? No, or ward A,B or C x-rays taken? YES,NO mri carried out? YES,NO CAT scan carried out? YES,NO eye test carried out? YES,NO hearing test carried out? YES,NO date of discharge: format YYYY-MM-DD as text
2. Design and Create the HTML
In order to interact with the database we need a data capture form, and a script that will process the form.
Create an HTML document in the folder you created in C:\wamp\www\hospital, and save it as hospital.html
hospital.html
Form
link to php script file use get to check form is working correctly post could also be used, user choice
process.php
Open a new file, save as process.php, keep on same directory as html file
$name = $_GET['name']; $app = $_GET['appointment']; $ward = $_GET['ward']; single option data passing $_POST option also viable, keep consistent with previous use $variable Arguments are passed as below: http://localhost/process.php?name=Joe+Bloggs&appointment=General+Practitioner&ward=A
if (isset($_GET['xray'])) { $xray = $_GET['xray']; } else { $xray = "No"; }
Checkbox data selection
checkboxes allow multiple options if not checked, it will not pass the parameter (not set) if passed the default is set to ‘yes’ so if not passed, we need to assign a value to the respective variable that will go into the database
Note: This code needs to be repeated for every checkbox variable passed as a parameter the variables used are: $mri, $cscan, $eye, $hearing
Check data capture
print "Name:".$name."
"; print "Appointment:".$app."
"; print "Ward:".$ward."
"; print "X-Ray:".$xray."
"; print "MRI:".$mri."
"; print "CSCAN:".$cscan."
"; print "EYE:".$eye."
"; print "HEARING:".$hearing."
"; print used to check fields are being passed and captured in the page
3. Database connection
• • You will need: – The address to the database server – A username with privileges to access the table you require – The username associated password Optionally: – The name of the database you are connecting to 16
Connection Sequence
• mysql_connect(host, username, password) – returns a link to the host using username and password authentication.
Usage: – $conn=mysql_connect(‘localhost’,myusername’,’MyP455w0rd’); • mysql_select_db(database,link) – selects a database from the connection in link Usage: – mysql_select_db( ‘ db_name ’ ,$conn); 17
Opening a connection
$link = mysql_connect('localhost','student','student') or die(mysql_error()); mysql_select_db('hospital',$link);
Current date selection
This script demonstrates how the current date can be obtained from the MySQL server using the appropriate DATE query $querydate = "SELECT CURDATE() as Today"; $result1 = mysql_query($querydate,$link) or die(mysql_error()); $row = mysql_fetch_assoc($result1); $today = $row['Today']; The parameter being queried (date) does not have an index in the array, so we need to use the alias “AS” to assign an index that we can use to retrieve from the result array
SQL Query Structures
• • SQL is a non procedural language providing syntax for extracting data, including a syntax to update, insert, and delete records.
The Query and Update commands together form the Data Manipulation (DML) part of SQL – SELECT – UPDATE – DELETE – INSERT INTO
SQL-DML SELECT
• • • • SELECT: This command is mandatory when performing a query; it is used to retrieve data from a table based on some criteria It specifies a coma separated list of fields to be retrieved, and the FROM clause specifies the table(s) to be accessed.
The results are stored in a result table known as the result-set.
The * symbol can be used to represent all of the fields
Format: SELECT column_name(s) FROM table_name Example: SELECT LastName, FirstName, Address FROM Students;
SQL-DML SELECT
To select specified columns, the SELECT command is followed by a comma separated list of fields to be selected from the table
SQL-DML SELECT
• • • WHERE Clause: It is used to select a field when a certain criteria set of conditions are desired The WHERE Clause is optional To create the conditions (called selection criteria) SQL provides a set of operators to further qualify what criteria should be specified
Operator
= <>, != > < >=, <= IS [NOT] NULL BETWEEN LIKE NOT LIKE ! , NOT ||, OR &&, AND XOR SQL-DML Where Operators
Description
Equal to Not equal to Greater than Less than Greater/Less Than or Equal Is NULL (vo value) or Not NULL Between an inclusive range Search for a value like a pattern Search for a value not like a pattern Logical not for negation Logical OR Logical AND Exclusive OR
Example
WHERE country = ‘ireland’ WHERE country != ‘USA’ WHERE salary > 28000 WHERE age < 35 WHERE cost >=1200 WHERE birth = NULL WHERE last_name BETWEEN ‘Doherty’ AND ‘McDAID’ WHERE name LIKE ‘D%’ WHERE country NOT LIKE ‘Sw%’ WHERE age ! 10; WHERE order_number > 10 || part_number = 80 WHERE age>12 && age < 21 WHERE status XOR
SQL-DML
• • • Using Quotes: Quotes are always an issue in programming languages. (single quotes?, double quotes?, when?) SQL uses single quotes around text values (MySQL also accepts double quotes) Numeric Values should not be enclosed in quotes.
SQL-DML
• • Comparing Strings: When comparing strings using =, the string must be exactly as typed for the condition to be true – this include length and type of characters.
NULL: Null means that there is not a value in the field, or it is unknown, but does not mean a value of zero.
DML-SQL
• • • • LIKE – NOT LIKE: The pattern matching operator can be used as a condition in the WHERE clause, allowing the selection of rows that are ‘like’ or match a pattern A percent sign (%) can be used as a wildcard to match any possible character that might appear before and/or after the character(s) specified.
A _ is used to match a single character.
The LIKE/NOT LIKE condition can be used in any valid SQL statement, including SELECT, INSERT, UPDATE or DELETE.
DML-SQL
• • • • Examples of the wildcard % uses: SELECT CompanyName, Country FROM Customers WHERE country LIKE ‘SW%’; – Returns all the customers and countries in which the country starts with “Sw” i.e. Sweden, Switzerland SELECT City, Country FROM suppliers WHERE City LIKE ‘%o’; – Returns all cities and countries where the % matches any city that ends with a letter o .
SELECT CompanyName FROM customers WHERE CompanyName LIKE ‘%Super%’ – Returns all company names where the % matches any company name that contains the pattern “Super”
DML-SQL
• • Examples of the wildcard _ uses: SELECT Extension, Firstname FROM Employees WHERE extension LIKE ‘4_ _’; – Returns all extensions and first names where the exetension has three characters and the first character is a 4.
DML-SQL
• ORDER BY: Used to sort the output of a query in either ascending (ASC, the default) or descending (DESC) order where the values being sorted are either strings or numbers Format: SELECT column_name(s) FROM table_name [WHERE condition] ORDER BY column [ASC, DESC] Example: SELECT Company,Ordernumber FROM Orders ORDER BY Company;
• •
SQL-DML INSERT
The INSERT statement is used ot insert new rows into a table. After the VALUES keyword, a comma separated list of column names follows Format: INSERT INTO table_name VALUES (value1, value2, … ) INSERT INTO table_name (column1, column2,…) VALUES (value1, value2, … ) Example: INSERT INTO Shippers (CompanyName, Phone) VALUES ( ‘FEDEX’,’416-555-1221’);
SQL-DML INSERT
• • Usually, the tables have a primary key column that is usually set to auto-increment; when this is the case, the id of the table is created by the database engine automatically Letting the database increment the PRIMARY KEY ensures that the value is always unique.
SQL-DML UPDATE
• • • The UPDATE statement is used to modify data in a table. The UPDATE command is followed by the name of the table where the data will be changed, followed by the SET statement to inidcate what field will be changed, and then the new value that will be assigned to the field The WHERE clause further qualifies what data is to be modified, thereby limiting the scope of the UPDATE
SQL-DML UPDATE
Format: UPDATE table_name SET column_name = new value WHERE column_name = some_value; Example: UPDATE orders SET ShipCountry= ‘Spain’ WHERE CustomerId = ‘whitc’;
SQL-DML DELETE
• • The DELETE statement is used to delete rows in a table and returns the number of rows that were deleted.
DELETE uses the FROM clause to specify the name of the table that contains the data you want to delete • The WHERE clause specifies the criteria to identify what data should be removed.
BE CAREFUL : Without the WHERE clause ALL ROWS are DELETED
SQL-DML DELETE
• • If the ORDER BY clause is specified, the rows are deleted in the order that is specified.
The LIMIT clause places a limit on the number of rows that can be deleted.
Format: DELETE FROM table_name WHERE column_name = some_value; Example: DELETE FROM orders WHERE ShipCountry = ‘Greenland’;
4. Run the query
mysql_query(link, query) is used to run a query on the database server. It requires the link and the query string and returns a result object.
Usage: $result= mysql_query( “ SELECT * from directory ” , $conn); Queries can be constructed as strings. The string variable can then be used on the mysql_query command.
Usage: $query="select * from directory"; $result = mysql_query($query,$link) or die("could not execute:".mysql_error());
Insert query
Query to insert all data from html form + date obtained from previous query
Specify discharge table $query = "INSERT INTO discharge VALUES (NULL,'$name','$app','$ward','$xray','$mri','$cscan','$eye','$hearing','$today')"; $result = mysql_query($query,$link) or die(mysql_error()); $affected = mysql_affected_rows($link); if ($affected >0) {echo "success";} else {echo "fail";} detects affected rows in the query in this case 1 row was INSERTED so $affected=1 if the insert was successful generates mysql error causes
Select query
This query will retrieve all the data from the table discharge $query3 = "SELECT * FROM discharge"; $result3 = mysql_query($query3,$link) or die(mysql_error()); $row3 = mysql_fetch_assoc($result3); ?>
5. Display results
• Result sets are objects. They point to places in memory where the values returned from the query exist • Result sets are NOT the individual rows from the query • You can think of a result set as the TABLE that holds the results • You need to read the rows from that table individually • $row=mysql_fetch_array($result); • Using this function, the array returned can be either numerically indexed or associative!
• If there are no more rows, the function returns FALSE
do …. while loop
Keeping it tidy
• • • • Once you have completed your work with the database, there are two things you should do: – Free the results – Close the connection to the server.
Freeing the results can be optional: On closing the connection, the results are automatically freed If you are planning to run further queries on the same connection, it is good practice to free the previous result set.
– mysql_free_result($result); To close the connection you use – mysql_close($conn); 44
mysql_close($link); ?>
6. Close connection
Places where things can go wrong
• • • • Creating a connection – Server unreachable/offline – Wrong username/password combination Selecting the table – Table does not exist – User without privileges for that table Running the query – Syntax errors – Empty result set Fetching results – Misusing the array 46