Topics covered

1. Topics covered • Structured Query Language • What can it do? • Advantages of SQL • Why bother with SQL? • The restaurant model • Example SQL usage in PHP

2. What can it do? SQL provides commands for a variety of tasks including: querying data, inserting, updating, and deleting rows in a table, creating, replacing, altering, and dropping objects, controlling access to the database and its objects, guaranteeing database consistency and integrity.

3. Advantages of SQL All major relational database management systems support SQL You can transfer all skills you have gained with SQL from one database to another SQL lets you work with data at the logical level, only being concerned with the implementation details when you want to manipulate them. i.e. you do not need to know how or where data is stored

4. Why bother with SQL? SQL gives us the power to access the contents of a database in a simple, powerful way SQL is (mostly) independent of the underlying database engine So you can use SQL with many different kinds of database This is simpler than learning how to access each type of database in a proprietary way

5. The restaurant model • The query engine provides a layer between programming languages and database. • It allows a developer to write programs which access data, without knowing how the database is implemented.

6. Example SQL usage in PHP What does the SQL do? How does PHP use SQL? How does the SQL get sent to the database? Where do the results of the query appear? // grabs matching record(s) from the “tblstudent" table $sSQL="SELECT * FROM tblstudent WHERE Email='$sEmail' "; $rsMain = mysql_query($sSQL);

7. SQL to English conversion Select each record from the table called “tblstudent” where the value of the “Email” field in the record being checked is equal to the value held in the PHP variable “$sEmail”. i.e. grab all the stuff where there’s a match! $sSQL = "SELECT * FROM student WHERE Email='$sEmail' "; $sSQL = "SELECT * FROM student WHERE Email='ab123456' ";

8. Useful SQL SELECT examples SELECT lastname, age FROM tblstudent WHERE firstname=‘Naomi’; SELECT * FROM tblstudent WHERE firstname LIKE ‘Naom*’; SELECT * FROM tblstudent WHERE age BETWEEN 16 AND 25; SELECT firstname, studentnumber FROM tblstudent WHERE age>=25; SELECT * FROM tblstudent WHERE studentnumber IS NOT NULL;

9. More SQL SELECT examples SELECT * FROM tblstudent ORDER BY age DESC; SELECT * FROM tblstudent WHERE postcode IN(‘ST1’, ‘ST4’, ‘ST6’); SELECT * FROM tblstudent WHERE age BETWEEN 16 AND 25 ORDER BY age, studentnumber DESC; SELECT firstname, studentnumber FROM tblstudent WHERE age>=55 AND studentnumber IS NOT NULL; SELECT COUNT(*) AS “Total” FROM tblstudent WHERE age>=55;

10. The Four SQL Statements There are four main SQL statements you will use. The most useful is SELECT, which we have just seen. It is usually used in this format; SELECT column_name(s) FROM table_name SELECT column_name(s) FROM table WHERE column operator value e.g. SELECT lastname, age FROM emp WHERE firstname=‘Naomi’;

11. The Insert Statement INSERT INTO table_name VALUES (value1, value2,....) Inserts a new row into an existing table INSERT INTO Persons VALUES (‘Lightman', ‘David‘, ‘dlightman@gtnw.org’); INSERT INTO Authorisation VALUES (‘Joshua’, ‘BURGR’); Insert Data in Specified Columns INSERT INTO Persons (LastName, Address) VALUES (‘Smith', '7 Lee Avenue');

12. The Update Statement UPDATE table_name SET column_name=new_value WHERE column_name=some_value; Update one Column in a Row: UPDATE Person SET FirstName='Bob' WHERE Email='ab123456'; Update several Columns in a Row: UPDATE Person SET Address='21 Jump Street', City='Los Angeles' WHERE LastName='Hanson';

13. The Delete Statement DELETE FROM table_name WHERE column_name = some_value Delete one Row: DELETE FROM Person WHERE Email='ab123456'; Delete several Rows: DELETE FROM Person WHERE LastName='Smith'; Delete all Rows in a Table: [Beware!] DELETE FROM Person;→ Same as: DELETE FROM Person WHERE LastName='*';

14. The Database Connection • Creates a new connection, configured for student ID codes // Note: username is “xy123456”, password is “xy123456” // This is unusual – normally the user, pwd and dbname differ $con = mysql_connect(“web.fcet.staffs.ac.uk", "xy123456", "xy123456") or die("Failed to connect to DB, " . mysql_error() ); // For simplified use, our database name is also your ID... mysql_select_db('xy123456', $con) or die('Could not find database.');

15. Using the Statements - Select // Receives values from Form, assigns values entered to vars $npage_code = $_REQUEST['code']; $sSQL = "SELECT * FROM PageElements WHERE PageCode='$npage_code' "; $rsSearch = mysql_query($sSQL); // fetches a row of fields and steps to the next one $row = mysql_fetch_assoc($rsSearch); // gets each fields by name $linkcode=$row['LinkAd']; $buycode=$row['BuyAd'];

16. Using the Statements - Insert // Receives values from Form, assigns values entered to vars $formname = $_REQUEST["name"]; $formemail = $_REQUEST["email"]; $formcomments = $_REQUEST["comments"]; // Declares SQL statement that will add data to the database $sSQL = "INSERT INTO users (usrName, usrEmail, usrComments) VALUES ('$formname', '$formemail', '$formcomments')"; // Runs the SQL query mysql_query($sSQL) or die("Could not insert into table: " . mysql_error()); mysql_close($con);

17. Using the Statements - Update // Declares SQL statement that will update a database $sSQL = "UPDATE Members SET FirstName='Mike' WHERE LastName='North' "; // Executes the SQL mysql_query($sSQL) or die("Could not insert into table:" . mysql_error());

18. Using the Statements - Delete // Declares SQL statement to delete from a DB $sSQL="DELETE FROM Members WHERE CITY='BELFAST' "; // Executes the SQL statement mysql_query($sSQL) or die("Could not insert into table:" . mysql_error());

19. Conclusion We have seen What SQL can be used for, The value of SQL as a cross-platform database interface language, Many examples of SQL clauses, Examples of SQL in PHP.