NMED 3850 A Advanced Online Design

NMED 3850 AAdvanced Online Design January 14, 2010 V. Mahadevan

SQL Data Types • Various kinds of data can be stored in a relational database. • Each data item has a particular type. • Examples: • INT: integers • CHAR: alphanumeric characters (up to 30) • VARCHAR: alphanumeric characters (up to 255) • FLOAT: real numbers • DATE: YYYY-MM-DD • DATETIME: YYYY-MM-DD HH:mm:SS

SQL Data Types (cont.) • CREATE TABLE Person (person_id INT, last_name VARCHAR(20), first_name VARCHAR(20), age INT, bday DATE); • INSERT INTO Person VALUES (1, 'Smith', 'Bob', 30, '1980-01-01');

SQL Data Types (cont.) • Some useful MySQL date functions: • CURDATE(): returns the current date • NOW(): returns the current date and time • CREATE TABLE Datetest (the_date DATE, the_date_time DATETIME); • INSERT INTO Datetest VALUES (CURDATE(), NOW()); • CURDATE(): returns the current date. • NOW(): returns the current date and time.

SQL Data Types (cont.) • Date types can be compared using SQL: • SELECT * FROM Person WHERE bday > ‘1980-01-01’; • This is a very powerful feature.

First Web Form <html> <body> <form action="echo_data.php" method="post"> Last Name: <input type="text" name="last_name" /> <p> First Name: <input type="text" name="first_name" /> <p> Age: <input type="text" name="age" /> <p> <input type="submit" /> <input type="reset" /> </form> </body> </html>

First PHP Script <html> <body> You entered Last Name: <b> <?php echo $_POST["last_name"]; ?> </b> <br> You entered First Name: <b> <?php echo $_POST["first_name"]; ?> </b> <br> You entered Age: <b> <?php echo $_POST["age"]; ?> ………

Inserting Data into MySQL <?php $hostname = 'localhost'; $username = 'newmedia'; $password = 'newmedia'; $connection = mysql_connect($hostname, $username, $password) or die ('Connection error!!!'); $database = 'peopledb'; mysql_select_db($database); $lastname = $_POST["last_name"]; $firstname = $_POST["first_name"]; $age = (int)$_POST["age"];

Inserting Data into MySQL (cont.) print "<h1> Inserted the following data into the MySQL Database: </h1>"; print "<h2> Last Name: $lastname </h2>"; print "<h2> First Name: $firstname </h2>"; print "<h2> Age: $age </h2>"; $execute_statement = "INSERT INTO person(last_name, first_name, age) VALUES ('$lastname', '$firstname', '$age')"; mysql_query($execute_statement) or die ('Error executing SQL statement!!!'); ?>

Retrieving Data from MySQL $lastname = $_POST["last_name"]; print "<h1> Retrieved the following data from the MySQL Database based on last name = $lastname: </h1>"; $execute_statement = "SELECT * FROM person WHERE last_name='$lastname'"; $results = mysql_query($execute_statement) or die ('Error executing SQL statement!!!'); while($item = mysql_fetch_array($results)) { print $item['last_name']; print "<br>"; print $item['first_name']; print "<br>"; print $item['age']; print "<br><br>"; }

Updating Data in MySQL $oldlastname = $_POST["old_last_name"]; $newlastname = $_POST["new_last_name"]; print "<h1> Updated oldlastname = $oldlastname to newlastname = $newlastname : </h1>"; $execute_statement = "UPDATE person SET last_name = '$newlastname' WHERE last_name='$oldlastname'"; mysql_query($execute_statement) or die ('Error executing SQL statement!!!');

Deleting Data from MySQL $lastname = $_POST["last_name"]; print "<h1> Deleted lastname = $lastname from database </h1>"; $execute_statement = "DELETE FROM person WHERE last_name = '$lastname'"; mysql_query($execute_statement) or die ('Error executing SQL statement!!!');

References • MySQL 5.0 Reference Manual: http://dev.mysql.com/doc/refman/5.0/en/index.html • PHP Manual: http://www.php.net/manual/en/

NMED 3850 A Advanced Online Design