1 / 22

PHP & SQL Xingquan (Hill) Zhu xqzhu@cse.fau

PHP & SQL Xingquan (Hill) Zhu xqzhu@cse.fau.edu. PHP&SQL. Relational Database systems Structured Query Language: SQL Access MySQL on Pluto server Create table, add records, query, and delete records PHP MySQL database access Connect to MySQL server Select database Query Show the results.

keenan
Download Presentation

PHP & SQL Xingquan (Hill) Zhu xqzhu@cse.fau

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. PHP & SQLXingquan (Hill) Zhuxqzhu@cse.fau.edu PHP

  2. PHP&SQL • Relational Database systems • Structured Query Language: SQL • Access MySQL on Pluto server • Create table, add records, query, and delete records • PHP MySQL database access • Connect to MySQL server • Select database • Query • Show the results PHP

  3. Relational database systems • A collection of tables of data • Each table can have any number of rows and columns of data • The columns of a table are named • Attributes • Each row usually contains a value for each column • Rows of a table are often referred to as entries • Primary keys • One column which uniquely identify the rows of the table • Both data values and primary key values in a table are called field PHP

  4. Structured Query Language: SQL • Language for specifying access and modification to relational database • Different from most programming language • More like a structure form of English • Reserved words are not case sensitive • SELECT and select are equivalent • The whitespace separating reserved words and clauses is ignored • Commands can be spread across several lines • Simple SQL process • Create database • Use database • Create table • Add records • Search records PHP

  5. Access SQL on Pluto • Download putty (http://www.chiark.greenend.org.uk/~sgtatham/putty/) • Download -> putty.exe -> open PHP

  6. Access SQL on Pluto Username: Your fau ID Password: Your fau ID Your fau ID PHP

  7. Access SQL on Pluto Your FAU ID again MySql version PHP

  8. You are not able to create a database on Pluto, but select your own database • You are only able to use your own db, TSG created for you • Use YourFAUID; PHP

  9. Create a table Table name, you name it • create table orderTbl(ID int not null primary key auto_increment, first_name varchar(30), last_name varchar(30), lobster int, crab int, apple int, orange int, comments varchar(30)); PHP

  10. Insert Records • Insert into orderTbl(ID, first_name, last_name, lobster, crab, apple, orange, comments) values (1, “Hill”, “Zhu”, 2, 1, 2, 0, “Good”); PHP

  11. Query the database • List all the records • Select * from orderTbl; PHP

  12. Query the database • Select first_name, last_name from orderTbl; PHP

  13. Query the database • Select * from orderTbl where first_name=“Hill”; PHP

  14. Delete records • Delete from table where xx=y • Delete from orderTbl where last_name=“Ford”; PHP

  15. PHP&SQL • Relational Database systems • Structured Query Language: SQL • Access MySQL on Pluto server • Create table, add records, query, and delete records • PHP MySQL database access • Connect to MySQL server • Select database • Query • Show the results PHP

  16. An Important Step • Login into pluto mySQL server • Execute the following command • SET PASSWORD FOR ‘yourfauid’@’localhost’ = OLD_PASSWORD(‘yourfauid’); • Otherwise, you will not be able to connect to mySQL server • Error message “Client does not support authentication protocol ” • Some sort of protocol problem PHP

  17. Php connect to MySQL server • Connect to a MySQL server • $db = mysql_connect($hostname, $username, $userpasswd); • Select database • $er = mysql_select_db("customer", $db); • $hostname="localhost"; • $username="hill"; • $userpasswd="hill"; • $db = mysql_connect($hostname, $username, $userpasswd); • if (!$db) • { • print ("Error - Could not connect to MySQL"); • exit; • } Database.php It’s YourFauId if use pluto PHP

  18. PHP SQL Query • $qresult = mysql_query($query); • The query string should not end with a semicolon. • Return “false” on error • Return a complex “resource” structure on success • $num_rows = mysql_num_rows($qresult); • $num_fields = mysql_num_fields($qresult); • $row = mysql_fetch_array($qresult); PHP

  19. PHP SQL Query • $row = mysql_fetch_array($qresult); • Calling Mysql_fetch_array() each time will return one row of the retrieved records (from the top to the bottom) • $row is a special array • It has two elements for each field • The first element consists of the system assigned key (0, 1, 2…) along with the field value • The second element uses attribute name as the key (“first_name”…), along with the field value • So you can use either of the following forms • $row[0], $row[1]…. • $row[“first_name”], $row[“last_name”]…. PHP

  20. PHP SQL Query • A simple PHP query example Customer.htmlaccesscustomer.php PHP

  21. PHP Insert A record • $sqlquery = INSERT INTO $table VALUES($id, $first_name, $last_name, $lobval, $crbval, $appval, $orgval,$comments); • Insert order information into the database Formselection.phpformcheckout.php formprocesswithDB.php mysqlDBProcess.inc PHP

  22. PHP&SQL • Relational Database systems • Structured Query Language: SQL • Access MySQL on Pluto server • Create table, add records, query, and delete records • PHP MySQL database access • Connect to MySQL server • Select database • Query • Show the results PHP

More Related