DatabaseImplementationWithWebScripting
Download
Report
Transcript DatabaseImplementationWithWebScripting
Database Implementation With
Web Scripting
PHP & MySQL
What is PHP?
PHP = PHP: Hypertext Preprocessor
PHP is a widely-used general-purpose
scripting language that is especially suited
for Web development and can be embedded
into HTML.
How to Use PHP
PHP is a server-side scripting language
This means the script is read and executed
by a web server (as opposed to JavaScript,
which is client-side and executed by the
browser)
The web server needs to have PHP installed
to be able to read and execute the files.
On most servers, the default extension for
PHP files is “.php”
The Power of PHP
PHP can be used on all major operating
systems.
You have the choice of using procedural
programming or object oriented
programming, or a mixture of them.
With PHP you are not limited to output
HTML. PHP's abilities includes outputting
images, PDF files and even Flash movies
(using libswf and Ming) generated on the fly.
The Power of PHP
One of the strongest and most significant
features in PHP is its support for a wide
range of databases.
–
MySQL is the one we will be using for examples.
PHP also has support for talking to other
services using protocols such as LDAP,
IMAP, SNMP, NNTP, POP3, HTTP, COM (on
Windows) and countless others.
–
Basically, PHP can interact with web and mail
servers.
Where to Put PHP
PHP must use opening and closing tags to tell the
server where it starts and ends among the HTML.
–
–
–
<?
// PHP Code In Here -- Most common
?>
<?php
// These are comment tags
php?>
<script language="php">
// Least popular notation
</script>
A Basic PHP Page
<html>
<head>
<title>PHP Test</title>
</head>
<body>
<?
echo “<p>Hello World</p>”;
// prints HTML output
echo $_SERVER[“HTTP_USER_AGENT”];
// prints the browser of the person visiting the page
?>
</body>
</html>
PHP for Database Interaction
If we want to talk to a MySQL database, we
have to use statements called queries.
Format for incorporating MySQL queries into
PHP:
–
mysql_query (“QUERY GOES HERE”) or die
(mysql_error());
MySQL SELECT Queries
Select all data in a table
SELECT * FROM [tablename]
Conditional selections
SELECT * FROM [tablename] WHERE [field name]
= ‘whatever’
SELECT * FROM directory WHERE name = ‘Bob’
AND phone_number = ‘3444444’
SELECT * FROM directory WHERE name != ‘Bob’
AND phone_number = '3444444' ORDER BY
phone_number
MySQL SELECT Queries
SELECT * FROM [table name] WHERE
name like ‘Bob%’ AND phone_number =
'3444444‘
–
name like ‘Bob%’ – will search for items in the
name column that start with ‘Bob’ i.e. ‘Bobby.’
SELECT * FROM [table name] WHERE
name = ‘Bob’ OR name = ‘$name’
MySQL Insert Queries
INSERT INTO [table name] (field_name1,
field_name2, field_name3) VALUES
(‘value1',‘value2',‘$variable')
–
Field names must match up with values that are
inserted and be in the correct order.
INSERT INTO [table name] (name,
phone_number) VALUES (‘Bob’, ‘’)
–
We can also use empty quotes to insert a null
string as a data item.
MySQL DELETE and UPDATE Queries
DELETE from [table name] where [field
name] = 'whatever‘
–
Deletes a record (row) from a table
UPDATE [table name] SET name = ‘Bill',
phone = ‘555-555-5555', age = ‘25' where
[field name] = 'user‘
–
Edits a record (or records) that already exists in
the database.
Using MySQL Queries in PHP
Scenario:
Let’s say we have a database table of users
that can access a particular website. Let’s
call this table “userTable,” and suppose it has
the following fields: name, password, email.
We’ll set up a form for adding new users.
Sending Form Data
addUser.html
<html>
<body>
<form action=“submit.php" method="POST“
name=“newUser”>
Name <input type="text" name=“name"><br>
Password <input type="text" name=“pw"><br>
Email <input type="text" name=“email"><br>
<input name="submit" type="submit" id="submit"
value=“Add User">
</form>
</body>
</html>
Dissecting the Form
<form action=“submit.php" method="POST“
name=“newUser”>
–
–
–
Name <input type="text" name=“name"><br>
–
–
action - defines which page will process the form.
method - HTTP method used to submit the form. Are we
asking for information or sending it?
name – form name
input type= “text” – Creates a single line text box
<br> - line break; moves to next line.
<input name="submit" type="submit" id="submit"
value=“Add User">
–
Creates a “Submit” button that reads “Add User.”
Sending Form Data
submit.php
<html>
<body>
<?
$name = $_POST[“name”];
$password = $_POST[“pw”];
$email = $_POST[“email”];
mysql_query (“INSERT INTO userTable (name, password,
email) VALUES (‘$name’, ‘$password’, ‘$email’)”) or die
(mysql_error());
?>
<P>A new user has been added.</P>
</body>
</html>
Dissecting the PHP Processing Page
$name = $_POST[“name”];
$password = $_POST[“pw”];
$email = $_POST[“email”];
// Retrieve the variables that were posted to
this page.
mysql_query (“INSERT INTO userTable
(name, password, email) VALUES (‘$name’,
‘$password’, ‘$email’)”) or die
(mysql_error());
// Insert the retrieved variables into the
database table.
Using MySQL Queries in PHP
Let’s say we want to query the database for
users with a first name of Adam
–
mysql_query (“SELECT * FROM userTable
WHERE name like ‘Adam%’) or die
(mysql_error());
Using MySQL Queries in PHP
How do we use the data retrieved from a
query once we make it?
–
–
Put it in a variable
$result01 = mysql_query (“SELECT * FROM
userTable WHERE name like ‘Adam%’) or die
(mysql_error());
After we have the query saved to a variable,
we can do a number of things.
Using MySQL Queries in PHP
Get the number of rows generated by the
query
–
$num1 = mysql_num_rows ($result01);
Print the names from the rows retrieved
–
–
for ($i=0; $i<$num1; $i++){
$name = mysql_result ($result01, $i, name);
echo “<b>Name:</b> $name <br>”;
}
HTML code can be mixed in the “echo” output
statements that print to the screen
What else can PHP/MySQL do?
We can generate dynamic pages based on a
user’s stored information.
Let’s say that Bob has logged into a website.
The website uses sessions or cookies to
know that Bob is logged in on all the pages
he visits. It access the table with Bob’s user
settings, and generates the pages’ style
elements based on Bob’s stored preferences.
What else can PHP/MySQL do?
<?
$result01 = mysql_query(“SELECT * FROM userSettings
WHERE userID=‘007’”) or die (mysql_error());
$bodyClass = mysql_result ($result01, 0, body);
$textClass = mysql_result ($result01, 0, text)
?>
<html>
<head><title>PHP Test</title></head>
<body class="<?=$bodyClass?>">
<P class=“<?=$textClass?>”>
Blah blah blah. Page content goes here.
</P>
</body>
</html>
Questions? Comments?
Take questions and give examples.
Slides by Adrienne Schneck
Email – [email protected]
Available for consulting