1 / 15

SQL Statements

SQL Statements. Basic Operations Web Technology. INSERT INTO table (col1, col2, col3) VALUES(val1, val2, val3);. INSERT INTO tct_phone ( std_fname , std_lname , std_phone ) VALUES( “Khaosai” , “Galaxy” , “088-123-4567” );. INSERT INTO. Insert a record into a table

river
Download Presentation

SQL Statements

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. SQL Statements Basic Operations Web Technology

  2. INSERT INTO table (col1, col2, col3) VALUES(val1, val2, val3); INSERT INTO tct_phone (std_fname, std_lname, std_phone) VALUES(“Khaosai”, “Galaxy”, “088-123-4567”); INSERT INTO • Insert a record into a table • Insert record(s) from a table right into another table INSERT INTO tct_phone (std_fname, std_lname, std_phone) select fname, lname, phone from tct_students where academic_year = ‘2552’;

  3. Edit a Record • Modify a record • Modify Khaosai’s phone number UPDATE tableSET field1=’val1’,field2=’val2’, field3=’val3’WHERE condition; UPDATE tct_phone SET std_phone=’089-123-1234’ WHERE std_fname = ‘Khaosai’ and std_lname = ‘Galaxy’;

  4. Delete Record(s) • Delete selected record(s) • Delete Khaosai’s record from the table • This will delete all records with firstname ‘Khaosai’ • This will delete all records with lastname ‘Galaxy’ DELETE FROM tableWHERE condition; DELETE FROM tct_phone WHERE std_fname = ‘Khaosai’; DELETE FROM tct_phone WHERE std_lname = ‘Galaxy’;

  5. Delete Record(s) • Do a better job with AND • Anyway, this would be a better choice by using primary key as the target • Note: avoid this; • it will delete all records in the tatble DELETE FROM tct_phone WHERE std_fname = ‘Khaosai’ AND std_lname = ‘Galaxy’; DELETE FROM tct_phone WHERE std_id = 20; DELETE FROM tct_phone;

  6. Creating Links for Edit and Delete <? print("<TD> [ <a href='edit_phone.php?std_id=$name_row[0]'>Edit</a> | <a href='#‘ onClick=\"del_confirm('std_id=$name_row[0]');\">Delete</a>] </TD>"); ?>

  7. Creating Javascript for Del Confirmation <script language="JavaScript"> function del_confirm(to_del) { var bDel = confirm("Do you really want to delete this record?"); if(bDel) { var str = "del_phone_action.php?" + to_del; window.location = str; } } </script>

  8. Add a Record Form

  9. Sample Code of Add Phone Action <? $fname = $_POST['txtFName']; $lname = $_POST['txtLName']; $phone = $_POST['txtPhone']; $section = $_POST['rdSection']; $query = “INSERT INTO tct_phone (std_fname, std_lname, std_phone, section) VALUES('$fname', '$lname', '$phone', '$section');"; $result = mysql_query($query); if(mysql_affected_rows() == 1) { echo "Add new phone successfully!"; } else echo "<font color='#FF0000'>An error occurs.</font>"; ?>

  10. Edit an Existing Record Form Keep in mind, we have to fill in the form with selected record first. So, we have to make a query (select) on the selected target, and then generate the code (HTML+PHP) as following slide.

  11. Sample Code of Edit Form <TABLE width="312"> <TR><TD bgcolor="#AAAAAA"><strong>Firstname</strong></TD> <TD><input type="text" name="txtFName" value="<? echo $std_fname;?>"></TD> </TR> <TR><TD bgcolor="#AAAAAA"><strong>Lastame</strong></TD> <TD><input type="text" name="txtLName" value="<? echo $std_lname;?>"></TD> </TR> <TR><TD bgcolor="#AAAAAA"><strong>Phone</strong></TD> <TD><input type="text" name="txtPhone" value="<? echo $std_phone;?>"></TD> </TR> <TR><TD bgcolor="#AAAAAA"><strong>Section</strong></TD> <TD> <input type="radio" name="rdSection" value="1R" <? echo ($std_section=="1R")?" checked":"";?>>RA <input type="radio" name="rdSection" value="1T" <? echo ($std_section=="1T")?" checked":"";?>>TA </TD> </TR> <TR><TD colspan="2" align="center"> <input type="hidden" name="std_id" value="<? echo $std_id; ?>"> <input type="reset" name="reset" value="Cancel"> <input type="submit" name="submit" value="Submit"></TD> </TR> </TABLE>

  12. Sample Code of Edit Phone Action <? $std_id = $_POST['std_id']; $fname = $_POST['txtFName']; $lname = $_POST['txtLName']; $phone = $_POST['txtPhone']; $section = $_POST['rdSection']; $query = "UPDATE tct_phone SET std_fname='$fname', std_lname='$lname', std_phone='$phone', section='$section‘ WHERE std_id=$std_id;"; $result = mysql_query($query); if(mysql_affected_rows() == 1) { echo "Edit record successfully!"; } else echo "<font color='#FF0000'>An error occurs.</font>"; ?>

  13. Sample Code of Delete Phone Action <? $std_id = $_REQUEST['std_id']; $query = "DELETE FROM tct_phone WHERE std_id=$std_id;"; $result = mysql_query($query); if(mysql_affected_rows() == 1) { echo "Delete record successfully!"; } else echo "<font color='#FF0000'>An error occurs.</font>"; ?> Note: We should have a way to check for confirmation before really deleting the record. In this case, we use Javascript to prevent unintentional deletion. Anyway, a better way is to have used a submission of POST type. This will be given by in-class discussion

  14. Setting Database Permissions • Web visitor • SELECT only • Contributor • SELECT, INSERT, and maybe UPDATE • Editor • SELECT, INSERT, UPDATE, and maybe DELETE (and maybe GRANT) • Root • SELECT, INSERT, UPDATE, DELETE, GRANT, and DROP

More Related