1 / 20
Download Presentation

PHP and MySQL

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. 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

More Related