PHP Database Integration—SQL
See all Sams Teach Yourself on InformIT Web Development Tutorials.
This chapter is from the book
This chapter is from the book
This chapter is from the book
One of the defining features of PHP is the ease with which you can connect to and manipulate databases. In this hour, we will concentrate on MySQL, but you will find similar functions for many of the databases that PHP supports. Why MySQL? It fits well with the spirit of PHP in that it is free to the individual user, yet remains a powerful tool that can be used as the basis of demanding real-world projects. Furthermore, versions of MySQL are available for multiple platforms. You can download MySQL from http://www.mysql.com.
In this hour, you will learn:
A few SQL samples
How to connect to the MySQL database server
How to select a database
About error handling
How to add data to a table
How to retrieve data from a table
How to alter data in a table
About the structure of databases
An approach to automating database queries
A (Very) Brief Introduction to SQL
SQL stands for Structured Query Language. It provides a standardized syntax by which different types of database can be queried. Most SQL database products provide their own extensions to the language, just as many browsers provide their own extensions to HTML. Nonetheless, an understanding of SQL enables you to work with a wide range of database products across multiple platforms.
This book cannot even begin to describe all the intricacies of SQL. Nonetheless, we can fill in some background about MySQL and SQL in general.
MySQL is an open source database server that can be queried using SQL. MySQL runs as a server daemon to which users on the same or even remote machines can connect. Once connected to the server, you can select a database if you have the privileges to do so.
Within a database, there will be a varying number of tables of data. Each table is arranged in rows and columns. The intersection between a row and a column is the point at which each item of data you want to store and access sits. Each column only accepts a predefined type of data, INT for integer, for example, or VARCHAR for a variable number of characters up to a defined limit.
To create a new table within a database we have selected, we might use a SQL query like the following:
CREATE TABLE mytable ( first_name VARCHAR(30), second_name VARCHAR(30), age INT);
Our new table has three columns. first_name and second_name can contain strings of up to 30 characters. age can contain any integer.
To add data to this table, we could use an INSERT statement:
INSERT INTO mytable ( first_name, second_name, age ) VALUES ( 'John', 'Smith', 36 );
The field names to which we want to add data are defined in the first set of parentheses. The values we want to insert are defined in the second.
To acquire all the data in a table, we would use a SELECT statement:
SELECT * FROM mytable;
The "*" symbol represents a wildcard which means "all fields." To acquire the information from a single field, you can use the column name in place of the wildcard:
SELECT age FROM mytable;
To change the values already stored in a table, you can use an UPDATE statement:
UPDATE mytable SET first_name = 'Bert';
This changes the first_name field in every row to "Bert". We can narrow the focus of SELECT and UPDATE statements with a WHERE clause. For example,
SELECT * FROM mytable WHERE first_name = 'Bert';
returns only those rows whose first_name fields contain the string "Bert". This next example
UPDATE mytable SET first_name = "Bert" WHERE second_name = "Baker";
changes the first_name fields of all rows whose second_name fields contain "Baker".
For more information on SQL, see Sams Teach Yourself SQL in 21 Days by Ryan K. Stephens et. al.