PHP and My SQL What is the Relationship

PHP and My. SQL

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

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

My. SQL • Client/Server architecture • Limited User Interface (PHPMy. Admin) • My. SQL 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

Data Types Data Type Definition Text 0 -255 characters Memo 0 -64000 characters Number Integer, long integer, single, double Date/Time Dates, times, or both at once Auto. Number Automatically incremented as records are added OLE object Image, sound files Hyperlink Link to an internet resource

Books Practice 1: Determine Data Types. Authors TITLE Id PUBLISHER_id ISBN Author QTY. Publishers PUBLISHER_id PUBLISHER

Practice 1: Data Types Solution Books Authors TITLE Text (255) PUBLISHER_id Number (integer) ISBN Text(10) QTY. Number(integer) Publishers PUBLISHER_id integer PUBLISHER text(255) Id Number(integer) ISBN Text(10) Author text(255)

Practice 2: Determine Data Types Personnel Institutions Pers_id Last Institution_id First Institution M. I. Sector Institution_id Positions id pers_id position

Practice 2: Solution Data Types Personnel Institutions Pers_id Number (integer) Last Text(255) Institution_id Number(integer) First Text(255) Institution text(255) M. I. Text(1) Sector Text(25) Institution_id Number (integer) Positions id Number(integer) pers_id Number(integer) position Text(200)

Basic My. SQL 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 …)

SELECT • • SELECT is 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;

SELECT Persons Table P_Id Last. Name First. Name Address City 1 Hansen Ola Timoteivn 10 Sandnes 2 Svendson Tove Borgvn 23 Sandnes 3 Pettersen Kari Storgt 20 Stavanger SELECT Last. Name, First. Name FROM Persons; SELECT *FROM Persons;

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

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;

WHERE Clause Operator Description = Equal <> Not equal > Greater than < Less than >= Greater than or equal <= Less than or equal BETWEEN Between an inclusive range LIKE Search for a pattern IN If you know the exact value you want to return for at least one of the columns

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

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

INSERT INTO • Use to insert new records in a table INSERT INTO table_name VALUES (value 1, value 2, value 3, …); INSERT INTO table_name (column 1, column 2, column 3, … VALUES (value 1, value 2, value 3, …); INSERT INTO persons VALUES (4, ‘Nilsen’, ‘Tom’, ‘Vingvn 23', 'Stavanger'); INSERT INTO persons (P_Id, lastname, firstname) VALUES (5, ‘Tjessem’, ‘Jakob’);

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;

Creating a My. SQL Database • Use php. My. Admin 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