Deleting and Updating Records in MySQL using PHP

Deleting and Updating Records in MySQL using PHP Basharat Mahmood, Department of Computer Science,CIIT,Islamabad, Pakistan.1Summary of the previous lecture Retrieving data from MySQL using PHPCONNECTIONS: login functionalityBasharat Mahmood, Department of Computer Science,CIIT,Islamabad, Pakistan.2OutlineDeleting records in MySQL using PHPUpdating records in MySQL using PHPBasharat Mahmood, Department of Computer Science,CIIT,Islamabad, Pakistan.31. Deleting record in MySQL using PHPConnection with databaseDelete the recordBasharat Mahmood, Department of Computer Science,CIIT,Islamabad, Pakistan.41.1 Connection with databaseBasharat Mahmood, Department of Computer Science,CIIT,Islamabad, Pakistan.51.2 Delete the recordDelete SQL instruction:DELETE FROM table-nameWHERE conditionDELETE FROM usersWHERE user_Id =5DELETE FROM usersWHERE user_Id >5Basharat Mahmood, Department of Computer Science,CIIT,Islamabad, Pakistan.61.2 Delete the recordBasharat Mahmood, Department of Computer Science,CIIT,Islamabad, Pakistan.71.3 Example Display data from database in a tableAdd actions columnIn each record, add a delete button When delete button in clicked, delete that recordBasharat Mahmood, Department of Computer Science,CIIT,Islamabad, Pakistan.81.3 ExampleBasharat Mahmood, Department of Computer Science,CIIT,Islamabad, Pakistan.9Delete1.3 ExampleBasharat Mahmood, Department of Computer Science,CIIT,Islamabad, Pakistan.10 User Name User Email User Password User Picture Actions 1.3 ExampleBasharat Mahmood, Department of Computer Science,CIIT,Islamabad, Pakistan.11 ”> ”>Delete1.3 ExampleBasharat Mahmood, Department of Computer Science,CIIT,Islamabad, Pakistan.12”> DeleteLink startsPage nameId nameAssigning the id of current recordLink textEnd of link1.3 ExampleBasharat Mahmood, Department of Computer Science,CIIT,Islamabad, Pakistan.13Starts HTML pageConnection to databaseSelecting data1.3 ExampleBasharat Mahmood, Department of Computer Science,CIIT,Islamabad, Pakistan.14Heading rowLoop starts1.3 ExampleBasharat Mahmood, Department of Computer Science,CIIT,Islamabad, Pakistan.15Writing user’s recordLink to the delete.php Ends loop1.3 ExampleBasharat Mahmood, Department of Computer Science,CIIT,Islamabad, Pakistan.16Getting record idDB connectionDelete instructionExecuting instruction1.3 ExampleBasharat Mahmood, Department of Computer Science,CIIT,Islamabad, Pakistan.17Confirmation before delete:Link to pageOn-click eventConfirm box2. Updating records in MySQL using PHPConnection with databaseUpdate the recordBasharat Mahmood, Department of Computer Science,CIIT,Islamabad, Pakistan.18 2. 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=1Basharat Mahmood, Department of Computer Science,CIIT,Islamabad, Pakistan.19 2. Updating records in MySQL using PHP Basharat Mahmood, Department of Computer Science,CIIT,Islamabad, Pakistan.20 2.1 ExampleBasharat Mahmood, Department of Computer Science,CIIT,Islamabad, Pakistan.21Selects updateForm with previous values 2.1 ExampleBasharat Mahmood, Department of Computer Science,CIIT,Islamabad, Pakistan.22Values are updatedUpdated record 2.1 ExampleBasharat Mahmood, Department of Computer Science,CIIT,Islamabad, Pakistan.23Link to update.php, record id is passed with linkView page:2.1 ExampleGet id of the recordConnect to databaseRetrieve record from database against the idStart formSet retrieved values as value of the input fieldsBasharat Mahmood, Department of Computer Science,CIIT,Islamabad, Pakistan.242.1 ExampleBasharat Mahmood, Department of Computer Science,CIIT,Islamabad, Pakistan.25HTML page startGets record’s idDB connectionData selectionQuery executionRecord is retrieved2.1 ExampleBasharat Mahmood, Department of Computer Science,CIIT,Islamabad, Pakistan.26Form startsTable startslabelValue is set to user’s current nameId is sent as hidden value2.1 ExampleBasharat Mahmood, Department of Computer Science,CIIT,Islamabad, Pakistan.27Email password2.1 ExampleBasharat Mahmood, Department of Computer Science,CIIT,Islamabad, Pakistan.28Submit button2.1 ExampleBasharat Mahmood, Department of Computer Science,CIIT,Islamabad, Pakistan.29Up_action.php page:Retrieve users inputConnect with databaseExecute update instructionRedirect to view page2.1 ExampleBasharat Mahmood, Department of Computer Science,CIIT,Islamabad, Pakistan.30User’s inputConnection to databaseUpdate instructionredirectionSummaryDeleting record from tables in MySQL using PHPUpdating record in MySQL using PHPBasharat Mahmood, Department of Computer Science,CIIT,Islamabad, Pakistan.31References Chapter 30, “Beginning PHP and MySQL” by W. Jason Gilmore, Apress publisher, 4th edition; 2010, ISBN-13 (electronic): 978-1-4302-3115-8.Basharat Mahmood, Department of Computer Science,CIIT,Islamabad, Pakistan.32