PHPSQL Updating and Deleting Data Mechanism for Deleting

Mechanism for Deleting Data Make a List Take note of the Index Key SUBMIT

Deleting Data This HTML form has a textbox where the user enters a search

Remember the SELECT command in SQL>? SELECT * FROM table_name WHERE [Conditions] Eg. SELECT

View, Add, Edit and Delete DELETE from table_name WHERE some_column=some_value Eg. DELETE from Students

What to do? Filter the Data (pre- selection) Select an Item to be deleted

Filtering Data 1. <html> 2. <body> 3. <form name="input" 4. 5. 6. 7. 8.

Result_form. php improvement 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16.

Result_form. php improvement 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11.

Delete_key. php 1. <? php 2. $delete_key = $_POST[‘delete_key’]; 3. $connection = 4. 5.

Updating Data This HTML form has a textbox where the user enters a search

View, Add, Edit and Delete UPDATE table_name SET column 1=value, column 2=value 2, .

Delete_key. php 1. $edit_id = $_GET['edit_key']; 2. $query = "SELECT * FROM names where

Editing Data 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12.

Update_record. php 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12.

Main Menu Add Record Edit Record View Record Delete Record Create Forms Search Record

A more Efficient Method Main Menu Add Record View Record Fill-in. Forms Search Record

Slides: 25

Download presentation

PHP+SQL: Updating and Deleting Data

Mechanism for Deleting Data Make a List Take note of the Index Key SUBMIT BUTTON PHP gets the index key, and executes SQL Delete command

Deleting data

Deleting Data This HTML form has a textbox where the user enters a search string, and a button to send data Select the Index Key and execute the Delete Function SUBMIT BUTTON Filter the data for easier deletion using the SELECT function The user selects an item and sending only the Index Key

Remember the SELECT command in SQL>? SELECT * FROM table_name WHERE [Conditions] Eg. SELECT * INTO friends WHERE Lastname = ‘Gener’;

View, Add, Edit and Delete DELETE from table_name WHERE some_column=some_value Eg. DELETE from Students WHERE Id = ‘ 2993’;

What to do? Filter the Data (pre- selection) Select an Item to be deleted from the filtered list. Execute the Delete

Filtering Data 1. <html> 2. <body> 3. <form name="input" 4. 5. 6. 7. 8. action=“result_form. php" method="post"> message: <input type="text" name= “lastname" > <input type ="submit" value ="Submit"> </form> </body> </html>

Result_form. php improvement 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. <? php $search_value = $_POST[‘lastname’]; $connection = mysql_connect(‘localhost’, ’root’, ’’); mysql_select_db('friends') ; $query = “Select * FROM names where lastname LIKE ‘$search_value%’”; $result = mysql_query($query); echo "<ol>"; if(mysql_num_rows($result) > 0) { while($row = mysql_fetch_row($result)) { echo "<li> <b>$row[1]</b>, $row[2] </li>"; } } else { echo “Record Not found!”; 18. 19. 20. 21. 22. } echo "</ol>"; mysql_free_result($result); mysql_close($connection); ? > 1. 2. 3. 4. 5.

Result_form. php improvement 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. <? php $search_value = $_POST[‘lastname’]; $connection = mysql_connect(‘localhost’, ’root’, ’’); mysql_select_db('friends') ; $query = “Select * FROM names where lastname LIKE ‘$search_value%’”; $result = mysql_query($query); echo "<ol>"; if(mysql_num_rows($result) > 0) { while($row = mysql_fetch_row($result)) { echo "<li> <b>$row[1]</b>, $row[2] </li> <a href='delete_record. php? delete_key=$row[0]'> [delete] </a> "; } } else { echo “Record Not found!”; 19. 20. 21. 22. 23. } echo "</ol>"; mysql_free_result($result); mysql_close($connection); ? > My Index key is located in $row[0]

Delete_key. php 1. <? php 2. $delete_key = $_POST[‘delete_key’]; 3. $connection = 4. 5. 6. 7. 8. mysql_connect(‘localhost’, ’root’, ’’); mysql_select_db('friends') ; $query = “DELETE FROM names where idnumber = $delete_key”; $result = mysql_query($query); mysql_close($connection); ? >

Updating data

Updating Data This HTML form has a textbox where the user enters a search string, and a button to send data Match the index key with the specific data using the index key Load the data and place it in its corresponding textbox SUBMIT BUTTON Filter the data for easier deletion using the SELECT function The user selects an item and sending only the Index Key Update Function

Remember the SELECT command in SQL>? SELECT * FROM table_name WHERE [Conditions] Eg. SELECT * INTO friends WHERE Lastname = ‘Gener’;

View, Add, Edit and Delete UPDATE table_name SET column 1=value, column 2=value 2, . . . WHERE some_column=some_value Eg. UPDATE Persons SET Address='Nissestien 67', City='Sandnes' WHERE Last. Name='Tjessem' AND First. Name='Jakob'

Result_form. php improvement 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. <? php $search_value = $_POST[‘lastname’]; $connection = mysql_connect(‘localhost’, ’root’, ’’); mysql_select_db('friends') ; $query = “Select * FROM names where lastname LIKE ‘$search_value%’”; $result = mysql_query($query); echo "<ol>"; if(mysql_num_rows($result) > 0) { while($row = mysql_fetch_row($result)) { echo "<li> <b>$row[1]</b>, $row[2] </li> <a href=‘edit_record. php? edit_key=$row[0]'> [delete] </a> "; } } else { echo “Record Not found!”; 19. 20. 21. 22. 23. } echo "</ol>"; mysql_free_result($result); mysql_close($connection); ? > My Index key is located in $row[0]

Delete_key. php 1. $edit_id = $_GET['edit_key']; 2. $query = "SELECT * FROM names where idnumber = $edit_id";

Editing Data 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. $result = mysql_query($query) or die ('Error in query: $query. '. msql_error()); echo "<form name='input' action='update_record. php' method='get'>"; if(mysql_num_rows($result) > 0) { while($row = mysql_fetch_row($result)) { echo "Lastname <input type='text' name='lastname' value = $row[1]> "; echo "Firstname <input type='text' name='firstname' value = $row[2]> "; echo "Age <input type='text' name='age' value = $row[3]> "; echo "Gender <input type='radio' name='gender' value='male' /> MALE <input type='radio' name='gender' value='female' /> Female "; echo "<input type='hidden' name='id' value = $row[0]> "; } } else { echo "Record Not found! "; } echo "<input type='submit' value='Submit' >"; echo "</form>";

Update_record. php 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. <? php // insert db connection here // // get data// $id = $_GET['id']; $firstname = $_GET['firstname']; $lastname = $_GET['lastname']; $age = $_GET['age']; $gender = $_GET['gender']; // insert data using UPDATE // $query = “ UPDATE names SET firstname='$firstname', lastname='$lastname', age='$age', gender='$gender’ where idnumber = $id"; $result = mysql_query($query) ; ? > <h 1> UPDATE Process Done </h 1> <a href="index. html"> [GO BACK] </a>

Making A Simple System

Main Menu Add Record Edit Record View Record Delete Record Create Forms Search Record Save Form Entries Show Filtered List Select a Record Show Editable Record Data Delete Record Update Entries

A more Efficient Method Main Menu Add Record View Record Fill-in. Forms Search Record Save Form Entries Show Filtered List Edit Select a Record Show Editable Record Data Update Entries Delete Select a Record Delete Record