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 • 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’;

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’;

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’;

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;

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>"); ?>

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>

Add a Record Form

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>"; ?>

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.

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>

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>"; ?>

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

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

SQL Statements

Télécharger la présentation

Plus de contenu associé