Database Implementation With Web Scripting

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 tagsphp?> • <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”> • 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 • Name <input type="text" name=“name"><br> • 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 – addy@dm.ucf.edu • Available for consulting

Database Implementation With Web Scripting