ADVM 420 Class 4 Web Design with PHP
ADVM 420 - Class #4 Web Design with PHP and My. SQL Adding and Listing from a My. SQL Database
Objectives l Create our first My. SQL database table l l l tbl. Survey Create a PHP script to display the data from this table Create an HTML add form & PHP script system to add new data into the table
What is a Database? l A Database is a storage mechanism l A Database is a collection of “tables” l l Each table has a number of “fields” l l Eg. Name, Address, City, Phone, Age Each field has a specific “datatype” l l Eg. A Customer, Products, Orders Eg. Character, Integer, Float, Date Each field may also have “properties” l Auto-increment, index, unique, default
Primary My. SQL Datatypes l l l l l Varchar – variable length character text Text – unlimited length text (memo) Int – 4 -byte integer (-32000 to +32000) Big. Int – 8 -byte(? ) integer (huge!) Single – small floating point numbers Double – large floating point numbers Decimal – fixed decimal (money 10, 2) Date – Date only Date. Time – Combined date and time
Other Database Features l A Database also has a security system. l l l My. SQL’s security system is based on a username, password and host computer My. SQL does not yet fully support referential integrity We also need a My. SQL Database Manager to allow creation & maintenance of: l l Tables, field properties Users, security, processes, configuration
2 My. SQL Database Managers l My. SQL Database Administration Systems allow us to manage to our remote databases by creating tables, fields and users. l php. My. Admin A web-based alternative to My. SQL Control Center l Download and Install the php. My. Admin from: § l http: //www. phpmyadmin. net/ My. SQL Control Center A Window’s based database management system l Download and Install the My. SQL Control Center from: § http: //www. mysql. com/downloads/ § An ODBC driver is also available from My. SQL. com
My. SQL Control Center / php. My. Admin
Installing php. My. Admin l To install php. My. Admin: l Download php. My. Admin from: l l Expand the ZIP file and copy all to: l l http: //www. phpmyadmin. net ~yourname/www/php. My. Admin Configure php. My. Admin l as shown on next page…
Configuring php. My. Admin l Find and open the config. inc. php file l Modify the following lines: l l $cfg['Pma. Absolute. Uri'] = 'http: //localhost/~FLast/php. My. Admin/'; $cfg['Servers'][$i]['host'] = ‘localhost'; $cfg['Servers'][$i]['auth_type'] = 'http'; Open php. My. Admin: l http: //phpclassdata. floatsintheparade. com
Example: Survey Table l Create a table called tbl. Survey that has the following fields and properties: l l l l ID: int 4, auto-increment, primary key Name: varchar 60, required Children: int 4, default 0 Gender: varchar 1, not required Interests: varchar 50, not required Age. Group: varchar 20, not required Comments: text, not required Modified: timestamp, required Do not use spaces or special characters in the field names l Test your table by adding two or more rows of data!
My. SQL in PHP l My. SQL is an extension to PHP l l There a set of ~40 mysql functions: l l l you must ensure it is installed (it is by default) All are prefixed with mysql_ Approximately 8 are used frequently Similar to other database extensions: l Interbase, Postgre. SQL, Oracle & Others l See documentation for details…
What is SQL, anyway? l SQL – Structured Query Language l l A language for Creating, Reading, Updating and Deleting data in a database (CRUD) Using PHP text strings, we need to “build” the required SQL statements and ask My. SQL to execute them…
Four essential SQL statements l SELECT – for viewing Record. Sets l l INSERT – for adding records l l INSERT INTO tbl. Surveys (Name, Children) VALUES (‘Bob’, 4); UPDATE – for changing records l l SELECT * FROM tbl. Surveys ORDER BY Name; UPDATE tbl. Surveys SET Name=‘Bob’, Children=5 WHERE (ID=2) DELETE – for deleting records l DELETE FROM tbl. Surveys WHERE (ID=2);
SELECT Queries: Seven Steps to Viewing Data l There are 7 steps to viewing data: 1. Connect to the my. SQL server l $link = mysql_connect(”host”, ”user”, ”pass”); Select the database to use 2. l mysql_select_db(”db”); Build the query you want to execute 3. l $query = "SELECT * FROM my_table"; Execute the query 4. l $result = mysql_query($query); Process the results 5. l $line = mysql_fetch_array($result, MYSQL_ASSOC); //Etc. . Free the result set n l mysql_free_result($result); Close the connection n 1. mysql_close($link);
SELECT Queries: Quick View of a Table… <? php $link = mysql_connect("host", "user", "password") or die("Could not connect"); mysql_select_db("my_database") or die("Could not select database"); $query = "SELECT * FROM my_table"; $result = mysql_query($query) or die("Query failed"); print "<table>n"; while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) { print "t<tr>n"; foreach ($line as $col_value) { print "tt<td>$col_value</td>n"; } print "t</tr>n"; } print "</table>n"; mysql_free_result($result); mysql_close($link); ? >
INSERT Queries: 6 Steps to adding data l There are 6 steps to adding data: Get the users input l Validate it – garbage in garbage out!! Connect to the my. SQL server l $link = mysql_connect(”host”, ”user”, ”pass”); Select the database to use l mysql_select_db(”db”); Build the query to do the insert l $query = “INSERT INTO my_table (field 1, field 2) VALUES (‘text’, 999)"; Execute the query l $result = mysql_query($query); Close the connection l mysql_close($link);
INSERT Queries: an example $link = mysql_connect("my_server", "User", "Pass") or die("Could not connect"); mysql_select_db("my_database") or die("Could not select database"); $query = "INSERT INTO my_table ". " (Name, Children, Gender, Interests, Age. Group, Comments) ". " VALUES (". "'". $name. "', ". $children. ", ". "'". join($gender, ’, ’). "', ". "'". join($interests, ’, ’). "', ". "'". $agegroup. "', ". "'". $comments. "'". ")"; print “$query ”; $result = mysql_query($query) or die(mysql_error()); mysql_close($link);
Formatting your output l So far, our viewing scripts have been very simple – how to produce “pretty” output? l l Column headings Currency and date formatting Use of Colours Let’s modify Survey. Dump. php to show the data as formatted output l Create the new file as: Survey. View. php
Survey. View. php /* Connecting, selecting database */ $link = mysql_connect("sequel. macewan. ca", "Username", "Password") or die(mysql_error()); mysql_select_db("DBName") or die(mysql_error()); /* Performing SQL query */ $query = "SELECT *, UNIX_TIMESTAMP(Modified) as Mod. Date FROM tbl. Survey ORDER BY Name"; $result = mysql_query($query) or die(mysql_error()); /* Printing results in HTML */ print "<table border=1 width='100%'>n"; print "<tr bgcolor=#E 0 E 0 E 0>n"; print "<th>ID</th>n"; print "<th>Name</th>n"; print "<th>Modified</th>n"; print "</tr>n"; while ($row_data = mysql_fetch_array($result, MYSQL_ASSOC)) { print "<tr>n"; print "<td>". $row_data['ID']. " </td>n"; print "<td>". $row_data['Name']. " </td>n"; print "<td>". date("F j, Y g: i a", $row_data['Mod. Date']). " </td>n"; print "</tr>n"; } print "</table>n"; mysql_free_result($result);
Create a New Table: tbl. Links l Create a table called tbl. Links that has the following fields and properties: l l l int_Link_ID: int 4, auto-increment, primary key txt_Title: varchar 60, required url_Hyperlink: varchar 255, required mem_Description: text, not required dat_Modified_Date: Timestamp, automatic txt_Modified_By: varchar 20, required
Self-test: Link Add System l Create a form called Link. Add. html that will allow a user to add a link to your database, including: l l Create a form processing script called Link. Save. php that adds this link to the database, automatically setting: l l Hyperlink, Title, Description aut_Modified_By to the user’s IP address Hint: Modify an example from last class
Self-test: List the Links l Modify the listing program to show the hyperlinks in the database l l Show the links as clickable using <a> tags Test your system by adding and listing 5 of your favorite links
Discussion l Now we have way too many links: l How will we delete them? l How will we edit them?
- Slides: 23
