1 / 13

NMED 3850 A Advanced Online Design

NMED 3850 A Advanced 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)

Download Presentation

NMED 3850 A Advanced Online Design

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. NMED 3850 AAdvanced Online Design January 14, 2010 V. Mahadevan

  2. 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

  3. 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');

  4. 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.

  5. 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.

  6. 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>

  7. 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"]; ?> ………

  8. 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"];

  9. 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!!!'); ?>

  10. 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>"; }

  11. 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!!!');

  12. 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!!!');

  13. 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/

More Related