Web Technologies and Programming Lecture 27

1Web Technologies and ProgrammingLecture 272Retrieving Data, Delete, Update from Database3Summary of the previous lectureCreating 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 upload4Outline Retrieving data from MySQL using PHPCONNECTIONS: login functionalityDeleting records in MySQL using PHPUpdating records in MySQL using PHP51. Retrieving data from MySQL using PHPConnection with databaseExecute Select SQL commandMake display structureWrite data 61.1 Connection with database71.2 Selecting data8The SELECT statement is used to select data from one or more tables:SELECT command in SQL:SELECT column-nameFROM table-nameSELECT user_NameFROM usersSELECT *FROM users1.2 Selecting data9Condition selection:SELECT column-nameFROM table-nameWHERE conditionSELECT *FROM usersWHERE user_Id>41.2 Selecting data101.2 Selecting data11Counting rows:mysql_num_rows(variable);1.3 Display structure12 User Name User Email User Password User Picture        1.4 Writing data13mysql_fetch_array(result-resource);mysql_fetch_array($result);1.4 Writing data14$result=$row = mysql_fetch_array($result);1Aliali@yahoo.com123upload/123ali.jpg2Umarumar@yahoo.com123upload/123umar.jpg$row=1Aliali@yahoo.com123upload/123ali.jpguser_Iduser_Nameuser_Emailuser_Passworduser_Picture04123echo $row [1]; echo $row[‘user_Name’];1.4 Writing data15 User Name User Email User Password User Picture ”> User NameUser EmailUser PasswordUser PictureAliali@yahoo.com1231.4 Writing data16Heading Row ”> User NameUser EmailUser PasswordUser PictureAliali@yahoo.com123Umarumar@yahoo.com1231.5 Example17Starts a HTML pageConnection to databaseSelect commandQuery executedCounting number of rows1.5 Example18Heading rowLoop startsKeeps row 1.5 Example19Displays nameDisplays emailDisplays passwordDisplays imageSets sourceEnds loopEnds table1.5 Example20Records in user’s tableOutput from the table2. CONNECTIONS: User login Form for user’s inputLogin action page:Connection with databaseRetrieve user’s inputSelect a record from user’s table with same email and passwordCount the number of row in resultIf one row is selected then fetch its values and store in session variable, otherwise send an error message on main page212.1 CONNECTIONS: User login form22emailPasswordPost method2.2 CONNECTIONS: database connection232.3 CONNECTIONS: Retrieve user’s input242.4 CONNECTIONS: Select record252.5 CONNECTIONS: Redirect26No. of rows selectedFetch user informationRegister session variablesredirectIf user’s input is invalid2.6 CONNECTIONS: user page27User profileUser’s picUser’s informationactions2.6 CONNECTIONS: user page28Profile divImage divUser’s info3. Deleting record in MySQL using PHPConnection with databaseDelete the record293.1 Connection with database303.2 Delete the recordDelete SQL instruction:DELETE FROM table-nameWHERE conditionDELETE FROM usersWHERE user_Id =5DELETE FROM usersWHERE user_Id >5313.2 Delete the record323.3 Example Display data from database in a tableAdd actions columnIn each record, add a delete button When delete button in clicked, delete that record331.3 Example34Delete3.3 Example35 User Name User Email User Password User Picture Actions 3.3 Example36 ”> ”>Delete3.3 Example37”> DeleteLink startsPage nameId nameAssigning the id of current recordLink textEnd of link3.3 Example38Starts HTML pageConnection to databaseSelecting data3.3 Example39Heading rowLoop starts3.3 Example40Writing user’s recordLink to the delete.php Ends loop3.3 Example41Getting record idDB connectionDelete instructionExecuting instruction3.3 Example42Confirmation before delete:Link to pageOn-click eventConfirm box4. Updating records in MySQL using PHPConnection with databaseUpdate the record43 4. Updating records in MySQL using PHPUpdate SQL instruction:UPDATE table-nameSET column-names = valuesWHERE conditionUPDATE usersSET user_Name = ‘Ali’, user_Email = ‘ali@yahoo.com’, user_Password=‘123’WHERE user_Id=144 4. Updating records in MySQL using PHP 45 4.1 Example46Selects updateForm with previous values 4.1 Example47Values are updatedUpdated record 4.1 Example48Link to update.php, record id is passed with linkView page:4.1 ExampleGet id of the recordConnect to databaseRetrieve record from database against the idStart formSet retrieved values as value of the input fields494.1 Example50HTML page startGets record’s idDB connectionData selectionQuery executionRecord is retrieved4.1 Example51Form startsTable startslabelValue is set to user’s current nameId is sent as hidden value4.1 Example52Email password4.1 Example53Submit button4.1 Example54Up_action.php page:Retrieve users inputConnect with databaseExecute update instructionRedirect to view page4.1 Example55User’s inputConnection to databaseUpdate instructionredirection5. Limit Data Selections From a MySQL Database56MySQL provides a LIMIT clause that is used to specify the number of records to return.The LIMIT clause makes it easy to code multi page results or pagination with SQL, and is very useful on large tables. Returning a large number of records can impact on performance.5. Limit Data Selections From a MySQL Database57Assume we wish to select all records from 1 - 30 (inclusive) from a table called “users". The SQL query would then look like this:$sql = "SELECT * FROM Users LIMIT 30";When the SQL query above is run, it will return the first 30 records.Summary of MYSQL DATABASEIntro 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 upload58SummaryRetrieving data from MySQL using PHPConnection with databaseExecute Select SQL commandMake display structureSelecting CommandCounting rowsWrite data CONNECTIONS: login pageDeleting record in MySQL using PHPConnection with databaseDelete the recordDelete Command59SummaryUpdating records in MySQL using PHPConnection with databaseUpdate the recordUpdate CommandLimit Data Selections60THANK YOU61