Transcript CIS115-Lecture 7 – MySQL Databases and PHP Integration

MySQL Databases
& PHP Integration
Using PHP to write data to, and retrieve data
from, a MySQL database
Interacting with Databases
We used XML pages statically to act as databases
Relational Database:
Think of an Excel document where each worksheet
is a table within the document (database)
A table is rows / columns while a database is
records / fields
Data is stored in database, not RAM as with XML
MySQL Command Line
Login: mysql –u username –p
(prompt for password)
SHOW DATABASES, CREATE, ALTER, DROP,
SELECT, INSERT, UPDATE, DELETE
Must use commands (in all caps) and semicolons (;)
at the end to execute strings
phpMyAdmin
Web-based database management system written in
PHP
Used to manipulate any database item, view items,
perform queries, etc…
Table Creation:
Field Name, Type (usually VARCHAR), Length
Values, Collation, Attributes, Null, Default, Extra
Login using your username (flastname) and password
(password)
Databases and PHP
PHP function: “mysqli_connect” / “mysqli_close”
Test our connection to our database using PHP
<?php
$dbconnect = mysqli_connect(“192.168.254.124”, “flastname”, “password”);
echo “<p>MySQL Client Version: “, mysqli_get_client_info(), “</p>”;
echo “<p>MySQL Connection: “, mysqli_get_host_info($dbconnect), “</p>”;
echo “<p>MySQL Protocol Version: “, mysqli_get_proto_info($dbconnect), “</p>”;
echo “<p>MySQL Server Version: “, mysqli_get_server_info($dbconnect), “</p>”;
mysqli_close($dbconnect);
?>
sample1.html & sample1.php
sample1.html – grabs user input in an HTML form and
holds it in memory
sample1.php – connects to a MySQL database for the
insertion of the data to the table
http://192.168.254.124/~instructor/CIS115Spring2011/Lecture5/
View sample1.html and sample1.php’s code
View phpMyAdmin to see the customer database –
sample1 table records
dbexample1.html &
dbexample1.php
dbexample1.html – provides a username/password
field for logging into the database
dbexample1.php– connects to a MySQL database for
the selection and viewing of table data in an
organized table
http://192.168.254.124/~instructor/CIS115Spring2011/Lecture5/
View dbexample1.html and dbexample1.php’s code
View phpMyAdmin to see the customer database –
sample1 table records
order.html & order.php
order.html gathers data and sends it to order.php for
verification
Nothing is written to the database…data is just
reprinted on the screen
http://192.168.254.124/~instructor/CIS115Spring2011/Lecture5/orders/
orderdb.html & orderdb.php
orderdb.html gathers data and sends it to orderdb.php
for verification
orderdb.php inputs that data into a database for
storage using fields corresponding to the form
variables
http://192.168.254.124/~instructor/CIS115Spring2011/Lecture5/orders/
orderdboutput.html &
orderdboutput.php
orderdboutput.html provides a login form for a user to
log in and view the contents of the database table
orderdboutput.php grabs the entered
username/password, checks it against the databse,
then displays the table records in an organized HTML
table
http://192.168.254.124/~instructor/CIS115Spring2011/Lecture5/orders/