PHP and MySQL

1. PHP and MySQL

2. What is the Relationship between PHP and MySQL? • PHP has the ability to connect to and manipulate databases. • The most popular database system that is used with PHP is called MySQL. • MySQL is a free database system and is supported by most servers.

3. MySQL - Fundamental Functions • Organize data – reduce or eliminate redundancy • Retrieve data – query/search/select • Sort data • Update data • Output – link to other software

4. MySQL • Client/Server architecture • Limited User Interface (PHPMyAdmin) • MySQL is cross platform, multi user access • Accessible to more users thru the web, client program or other admin tools to access database (via authentication) • Can be integrated with Web Server (web programming languages) • Data available remotely • Free, open-source

5. Data Types

6. Practice 1: Determine Data Types Books Authors Publishers

7. Practice 1: Data Types - Solution Books Authors Publishers

8. Practice 2: Determine Data Types Personnel Institutions Positions

9. Practice 2: Solution Data Types Personnel Institutions Positions

10. Basic MySQL Operations • Create table • Insert records • Load data • Retrieve records • Update records • Delete records • Modify table • Join table • Drop table • Optimize table • Count, Like, Order by, Group by • More advanced ones (sub-queries, stored procedures, triggers, views …)

11. SELECT • SELECTis used to select data from a database • The result is stored in a result table, called the result-set • SQL is not case sensitive • SELECT syntax SELECT column_name(s) FROM table_name; SELECT * FROM table_name;

12. SELECT Persons Table SELECT LastName, FirstName FROM Persons; SELECT *FROM Persons;

13. WHERE clause SELECT column_name(s) FROM table_name WHERE column_name operator value; SELECT * FROM persons WHERE city=‘Sandnes’;

14. WHERE Clause • Text values should be quoted by single quotes or double quotes • Numeric values do not need to be enclosed in quotes SELECT * FROM persons WHERE city=‘Sandnes’; Or SELECT * FROM persons WHERE city=“Sandnes”; Or SELECT * FROM persons WHERE P_Id=1;

15. WHERE Clause

16. AND or OR • AND, OR operators are used to filter records based on more than one condition • AND=both the first and the second conditions is true • OR=either the first or the second condition is true

17. AND or OR SELECT * FROM persons WHERE firstname=‘Tove’ AND lastname=‘Svendson’; SELECT * FROM persons WHERE firstname=‘Tove’ OR firstname=‘Ola’; SELECT * FROM persons WHERE lastname=‘Svendson’ AND (firstname=‘Tove’ OR firstname=‘Ola’);

18. INSERT INTO • Use to insert new records in a table INSERT INTO table_name VALUES (value1, value2, value3,…); INSERT INTO table_name (column1, column2, column3, … VALUES (value1, value2, value3,…); INSERT INTO persons VALUES (4, ‘Nilsen’, ‘Tom’, ‘Vingvn23', 'Stavanger'); INSERT INTO persons (P_Id, lastname, firstname) VALUES (5, ‘Tjessem’, ‘Jakob’);

19. DELETE statement • Used to delete records in a table DELETE FROM table_name WHERE some_column=some_value; DELETE FROM persons WHERE lastname=‘Tjessem’ AND firstname=‘Jakob’; DELETE FROM table_name; Or DELETE * FROM table_name;

20. Creating a MySQL Database • Use phpMyAdmin to build a single Table Database. • The database will be called addressbook and the table will be called friend. • The table will include the following attributes: First name Last name Phone Birthday Email – A record will identified by this primary unique key