Web Technologies and Programming Lecture 26

1Web Technologies and ProgrammingLecture 262Database Connectivity, File Upload in PHP 3Summary of the previous lectureWriting regular expression in PHPBrackets []Quantifiers +, *, ?, {int. range}, and $ Sub-patternsPredefined character ranges\d:\D:\w:Validating User’s InputValidating name:Validating Password:Validating date:Validating CNIC:Validating Email:Validating user’s input4Summary of the previous lectureDefined functions. preg_match():preg_match_all():preg_grep():String Functions in PHPstrlen():strcmp():strcasecmp():strtolower():strtoupper():ucfirst():ucwords():strpos():strrpos():substr_count():5OutlineIntro to MySQLCreating database in MySQL using WAMPConnecting PHP with MySQLInserting data in databaseCONNECTIONS: user registration FILES super global variableFile uploading in PHPStoring reference of uploaded file in databaseUser registration in CONNECTIONS web application with file upload61. MySQL DATABASEWith PHP, you can connect to and manipulate databases.MySQL is the most popular database system used with PHP.71. Creating database in MySQL using WAMPWhat is MySQL?MySQL is named after co-founder Monty Widenius's daughter: MyMySQL is a database system used on the webMySQL is a database system that runs on a serverMySQL is ideal for both small and large applicationsMySQL is very fast, reliable, and easy to useMySQL uses standard SQLMySQL compiles on a number of platformsMySQL is free to download and useThe data in a MySQL database are stored in tables. A table is a collection of related data, and it consists of columns and rows.81. Creating database in MySQL using WAMPGo to home page of WAMP serverSelect PHP myadminLogin to MySqlEnter database name and click create database button91. Creating database in MySQL using WAMPCreating a table in a database:Select database from the list of databasesEnter table name and number of columnsEnter name, data-type and other required properties for columnsClick create table button101. Creating database in MySQL using WAMP11localhost1. Creating database in MySQL using WAMP12Select phpmyadmin1. Creating database in MySQL using WAMP13User name is rootEmpty password1. Creating database in MySQL using WAMP14Select databases1. Creating database in MySQL using WAMP15Enter database nameSelect create1. Creating database in MySQL using WAMP16Select database 1. Creating database in MySQL using WAMP17Table nameEnter no. of columnsClick go1. Creating database in MySQL using WAMP18Column nameData-typeMax length1. Creating database in MySQL using WAMP19Primary keyAuto increment1. Creating database in MySQL using WAMP20Click to create table1. Creating database in MySQL using WAMP21name2. Connecting PHP with MySQLBefore we can access data in the MySQL database, we need to be able to connect to the server:connect_error) {    die("Connection failed: " . $conn->connect_error);} echo "Connected successfully";?>222. Connecting PHP with MySQLOur MYSQL connection: mysql_connect(“hostname” ,”username”, ”password”)mysql_connect(“localhost”,”root”,””)mysql_select_db(“database name”)mysql_select_db(“testdatabase”)232. Connecting PHP with MySQL24Connect with serverHost nameusernameEmpty passwordError messageSelect databaseDatabase name3. Inserting data in database25After a database and a table have been created, we can start adding data in them.Here are some syntax rules to follow:The SQL query must be quoted in PHPString values inside the SQL query must be quotedNumeric values must not be quotedThe word NULL must not be quotedThe INSERT INTO statement is used to add new records to a MySQL table:INSERT INTO table_name (column1, column2, column3,...)VALUES (value1, value2, value3,...)3. Inserting data in database for user input26Create form to receive input from userOn action pageRetrieve user’s inputValidate user’s input (optional)Establish connection with databaseWrite insert commandExecute command3. Inserting data in database27postreg_action.phpfor later usenameemailpassword3. Inserting data in database28Insert SQL command:INSERT INTO `table_name` (list of columns) VALUES (list of values)INSERT INTO users(‘user_Name’,’user_Email’,’user_Password’)VALUES (‘$name’,’$email’,’$password’)mysql_query(query to execute)3. Inserting data in database29User’s input is retrievedDatabase connectionInsert commandCommand is executed3. Inserting data in database30Header functionlocationmessageClose warningsMessage is displayed3. Inserting data in database31message4. CONNECTIONS: user registration32Create databaseDatabase name: connectionsTable name: usersTable columns: user_Id int 4User_Name text 25User_Email varchar 35User_Password varchar 20User_Picture varchar 50 4. CONNECTIONS: user registration33nameemailpasswordpost4. CONNECTIONS: user registration34Connection to the serverDatabase selectionDatabase name4. CONNECTIONS: user registration35User’s input is retrievedDatabase connectionName validation4. CONNECTIONS: user registration36Email validationInsert commandCommand is executed5. $_FILES: super-global variable$_FILES: contains any item uploaded to the server when the post method is usedan array type variableCreated automatically Can be accessed on other pages375. $_FILES: super-global variableKeeps information aboutNameSizeType Tmp_name385. $_FILES: super-global variableFORM attributes required:Method should be postEnctype should be multipart/form-data395. $_FILES: super-global variable40File typenameAsadChoose FilesubmitNo File ChosenMypci.jpgnameAsad$_POST$_FILESpic[name]=>file name[type]=>file type[size]=>file size=>tmp name[tmp_name]5. $_FILES: super-global variable41Accessing file information$_FILES[‘input-field name’][‘name’];$_FILES[‘picture’][‘name’];$_FILES[‘input-field name’][‘type’];$_FILES[‘picture’][‘type’];$_FILES[‘input-field name’][‘size’];$_FILES[‘picture’][‘size’];5. $_FILES: super-global variable42enctypemethodInput fieldSubmit button5. $_FILES: super-global variable43Display files arrayFile nameFile typeFile sizetmp name5. $_FILES: super-global variable44Selected fileFiles arrayFile nameFile typeFile sizeTemp name6. Uploading filemove_uploaded_file():bool move_uploaded_file ( string $filename , string $destination );This function checks to ensure that the file designated by filename is a valid upload file (meaning that it was uploaded via PHP's HTTP POST upload mechanism) If the file is valid, it will be moved to the filename given by destination456. Uploading file46basename():returns the filename from a path Example:retutns index.php6. Uploading file47File upload steps:Identify the file to be uploadedtmp_name is usedDefine destinationLocation + file nameUpload the file6. Uploading file48File tmp nameFile nameFolder + filenameFile uploaded6. Uploading file49Restricting Users:Size restrictionType restrictionFile rename7. Uploading file50Retrieving file attributesFile renameSize and typeFile uploaded7. Storing reference to database51nameemailpasswordpictureMethod is postEnctype=“multipart/form-data”7. Storing reference to database527. Storing reference to database53DB connectionExecuting queryInsert queryredirection7. Storing reference to database547. Storing reference to database55Record is added7. Storing reference to database568. CONNECTIONS: registration action57nameemailpasswordpostpic8. CONNECTIONS: registration action58Input is retrievedFile is uploadedDB connection8. CONNECTIONS: registration action59Input is validated8. CONNECTIONS: registration action60Data is insertedSummaryIntro to MySQLCreating database in MySQL using WAMPConnecting PHP with MySQLInserting data in databaseCONNECTIONS: user registrationFILES super global variableFile uploading in PHPStoring reference of uploaded file in databaseUser registration in CONNECTIONS web application with file upload61THANK YOU62