1 / 21

PHP and MySQL

PHP and MySQL. Lab 7 EECS 448 Dr Fengjun Li and Meenakshi Mishra. HTML form. <form> input elements </form> Input elements <form> First name: <input type=“text” name=“ firstname ”> </form> <form> Password: <input type="password" name=" pwd "> </form>

odessa
Download Presentation

PHP and MySQL

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. PHP and MySQL Lab 7 EECS 448 DrFengjun Li and Meenakshi Mishra

  2. HTML form • <form> input elements </form> • Input elements • <form> First name: <input type=“text” name=“firstname”> </form> • <form> Password: <input type="password" name="pwd"> </form> • <form> <input type="radio" name="sex" value="male">Male<br><input type="radio" name="sex" value="female">Female</form> [Can also use input type=“checkbox”] • <form><input type="submit" value="Submit"> </form> • Create a button • <button type="button" onclick=“method">Text on button</button>

  3. Form Validation Using Javascript <!DOCTYPE html> <html> <head> <script> function validateForm() { var x=document.forms[“enteremail"]["email"].value; var a=x.indexOf("@"); var b=x.lastIndexOf("."); if (a<1 || b<a) { alert("Not a valid e-mail address"); return false; } } </script> </head>

  4. Form Validation <body> <form name=“enteremail" onsubmit="return validateForm();"> Email: <input type="text" name="email"> <input type="submit" value="Submit"> </form> </body> </html>

  5. Database • Each database consists of one or more tables • The link between the tables can be of various types • Tables contain records of data • Each record often represented as a row • SQL provides quick commands to parse through the data

  6. SQL Commands • CREATE DATABASE my_database; • USE database_name • CREATE TABLE my_table (nameOfColumndatatype(size), nameOfColumndatatype(size), nameOfColumndatatype(size) ); //Create a new table • CREATE TABLE Class ( StudentIDint, FirstNamevarchar(100), LastNamevarchar(100), ); • INSERT INTO my_table (column1, column2) VALUES (value1, value2); • UPDATE my_table SET Column1=value1 WHERE column_n=value_n; • DELETE FROM my_table WHERE column1=value1;

  7. Pulling out desired data • SQL SELECT • SELECT column1, column2 FROM table_name; • SELECT FirstName FROM Class; • SELECT * FROM table_name; • SELECT DISTINCT column1, column2 FROM table_name; • SELECT column FROM table_name WHERE column operator value; SELECT FirstName FROM Class WHERE FirstName>='M'; • SELECT column FROM table_name WHERE column operator value AND column operator value; • SELECT column FROM table_name WHERE column operator value OR column operator value;

  8. Pulling out desired data • SQL SELECT • SELECT column FROM table_name ORDER BY column1, column2 ASC|DESC; • SELECT column FROM table_name LIMIT number; • SELECT column FROM table_name WHERE column1 LIKE pattern

  9. SQL Wildcards • % • Replaces any number of characters • _ 'underscore' • Replaces single character • [list of characters] • Set of characters that are allowed to be present • [abcde]% • [!list of characters] • Set of characters that should not be present • [!abcde]%

  10. SQL DATE • NOW() //Current Date and Time • YYYY-MM-DD HH:MM:SS • CURDATE() //Current Date • CURTIME() //Current Time • DATE() //Extracts the date part of a date expression • DATE_ADD() //Add interval to a date • DATE_SUB() //Subtract interval from date • DATE_DIFF() //Difference between two dates • SELECT * FROM Class WHERE DateOfLab='2013-10-20';

  11. SQL Functions • AVG() • SUM() • MAX() • MIN() • COUNT() • FIRST() • LAST() • LEN()

  12. PHP • Server side programming language • Name your scripts with extension ‘.php’ • Place the files in ‘public_html’ folder • Change the permission of the files to enable execution • Can use it to access database • Inserting a php script <html> <?php echo “Hello!!!”; ?> </html>

  13. Variables • $x=5; • $x=“string”; • $a=array(“item1”, “item2”, “item3”); • $a[0] • $a=array(“index1”=> “item1”, “index2” => “item2”) • $a[‘index1’];

  14. Variables • Loosely typed language • Variable declared outside function can only be accessed outside function but is global • To use the variable inside a function, use keyword ‘global’ inside the function • Variable declared inside function is only local to inside • strlen(string variable); • strpos(string1, string2); //position of string 2 in string 1

  15. Operators • Same as C++ or Java for arithmetic • Increment • $x++; • ++$x; • Concatenate • $y=$x.$z; • $y.=$z; • Comparison operators same as javascript

  16. Condition • if (condition) { code} • if …else • if…elseif…else if (condition) { //code if condition is true } elseif (condition) { //code } else { //code }

  17. Loops • for (init;condition;increment) { code } • foreach($arrayvariable as $loopvariable) • foreach($arrayvariable as $loopkey=>$loopvalue) • while(condition) { code } • do … while (condition)

  18. Super Globals • Variables always available in php • $GLOBALS • Lists all super global variable in an array • $_SERVER • Has information about header, paths, scripts etc • $_SERVER[ ‘PHP_SELF’] //filename of current php script • $_SERVER['SERVER_ADDR'] //IP address of Host Server • $_REQUEST // Collect data after submitting html form • $_POST //Use to collect data after submitting form using post method • $_GET //Use to collect data after submitting form using get method

  19. Form Validation Using PHP <form method="post" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>"> Name: <input type="text” name= “firstname” > <spanclass="error">* <?php echo $nameErr;?> </span> <br> E-mail: <input type="text" name="email"> <input type="submit" name="submit" value="Submit"> </form> <?php if (empty($_POST["name"]))      {$nameErr = "Name is required";} ?>

  20. PHP • mysqli_connect(host,username,password,dbname); <?php // Create connection $c=mysqli_connect(“hostname”, “mmishra”, “abc123”, “mmishra”); // Check connection if (mysqli_connect_errno($c)) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); } $x= mysqli_query($c,"SELECT * FROM Class"); while($r= mysqli_fetch_array($x)) •  {  echo $row[ ‘Column1'] . " " . $row[ ‘Column2’]; } mysqli_close($c); ?>

  21. Assignment • Create a Phone Directory in your database • The column values are First Name, Last Name, Address, Phone Number, Email, Message for you, Date of Birth etc • Create a Webpage where a user can enter their contact information as listed above and save it in your phone directory and also be able to leave a message for you • You have to make the First Name, Last Name, Phone Number and Email as required fields • The email address should have a proper format • You can use either php or javascript for form validation • The information should only be entered in your database if the above requirements are met

More Related