| Although PHP is considered a scripting language, it has quite a number of advanced features. There isn't room enough in this chapter to cover every one of those features, so I'll only discuss creating custom functions and working with MySQL. Functions PHP has hundreds (if not thousands) of built-in functions, some of which you've already been introduced to. A function is merely encapsulated code, a repeatable process that you can call on to perform a task. For example, the built-in function called count() returns a count of the number of elements in an array. There's no need for you to figure this out manually or to reinvent the wheel. If you need to know how many items are in the array $foo, use count($foo) to get back the number of elements. As useful as all the built-in functions are, PHP wouldn't be complete unless it allowed you to create your own custom functions. To create a function, you (naturally) use the function statement. An example of a very simple function is one that says hello: function sayhello() { echo "Hello!"; } To say hello, you would call that function from your code: <? sayhello() ?> Let's create a more complicated function that does some easy math and then call that function from the code. The function you're creating will add two numbers that you pass to it, and that means that the function has to know that you are passing arguments to it. You can tell a function to expect arguments by listing those arguments right after the name, in parentheses: function addtwo ($first, $second) { } Now that you have a function and it knows to expect some arguments as input, you can fill in the body of the function. In this case, you create an expression that adds the two arguments, and then you return that value: function addtwo ($first, $second) { $sum = $first + $second; return $sum; } To use this function, you would call it from your PHP code, passing the arguments in, like so: <p>2 + 4 equals: <b><?= addtwo(2,4) ?> The answer should be 6. (Did you catch the use of <?= to generate output from the function?) Obviously, functions can be much more complex than this, but even understanding them at this level is a monumental step forward as a PHP programmer. For example, once you understand functions, you can start working with object-oriented PHP, because all an object really is, is a bunch of functions called methods. Figure 24.1. Understanding PHP functions allows you to start understanding PHP objects.
Working with MySQL PHP has a good number of built-in functions that make working with MySQL fast and easy. The following section describes some of the ones you'll use on a constant basis and some more advanced ones, such as creating databases and dropping tables. Connecting to MySQL Before you can send SQL statements to a MySQL database, you'll need to actually connect to the database server. You can use the mysql_connect function to do this: $link = mysql_connect($server, $user, $password) or die ("Couldn't connect!"); In this case, mysql_connect is expecting three arguments: the name of the database server (localhost if it's on your own machine), the username, and the password. The die function provides feedback that the connection couldn't be made (this could be because the username or password are wrong or because the server is unavailable). Selecting a Database After you've successfully connected to the database, you can select a database to work from with the mysql_select_db function: mysql_select_db("news"); NOTE Remember that selecting a database isn't the same thing as selecting a table. Tables reside in a database, but before you can query or update those tables, you need to use the proper database.
Creating a Database Creating a database enables you to set aside a space in which you can create a group of related tables. This is useful if you need to create a development sandbox for your own experimentation or keep tables for different projects separate from each other. Although you can create a database from the MySQL interface, it's just as easy to create a database from PHP using the mysql_create_db function: mysql_create_db("mytestdb") or die ("Could not create database!"); NOTE You must connect to MySQL as an administrator for mysql_create_db to work. Make sure that your mysql_connect statement is passing the proper administrative username and password.
Querying a Database Table You can send queries to MySQL using the mysql_query function. In most cases, you'll want to store your SQL query in a variable and then execute the mysql_query function on that variable: $sql = "select * from info"; $result = mysql_query($sql); Looking at a Recordset After you send a query to a database, you'll need to look at those results. The easiest way is to use a while loop to iterate over the results. Inside the while loop, you'll use the mysql_fetch_array function to output each row of the recordset: $sql = "select * from info"; $result = mysql_query($sql); //the list function gives a variable name to each //field extracted from the database table while (list($id, $title, $body) = mysql_fetch_array($result)) { echo "$id:<br>"; echo "$title<br>"; echo "$body<hr>"; } Inserting Records Insert commands take the following form: insert into tablename (column1, column2, column3) values (value1, value2, value3) To insert a record in a table, send an insert command using the mysql_query function: $sql = " insert into info (title, body) values ("A title", "A body") "; $result = mysql_query($sql); Updating Records Update commands take the following form: update tablename set column=value where column=value Although update commands seem more complex than insert commands, they really aren't. What you are saying is, "For all records in a table, update the value of a column only if that record has a certain value in a given column." For example, if you had a table that consisted of names, departments, and phone numbers, you may want to update the table by changing all the department names (it seems that someone at HQ has decided to change the name of Marketing to Corporate Communications). Doing this by hand would be very tedious and time-consuming. It's much faster and easier to use an update command: update employeelisting set department = 'Corporate Communications' where department = 'Marketing' To send this command to MySQL from PHP, embed it into a variable and pass the variable to mysql_query: $sql = " update employeelisting set department = 'Corporate Communications' where department = 'Marketing' "; $result = mysql_query($sql); Deleting Records The delete command has the following form: delete from table where column = value In essence, you're asking MySQL to delete from a table those rows that match the where clause. For example, if you wanted to delete all employee records for everyone in the Facilities group, you'd use: delete from employeelisting where department = 'Facilities' WARNING If you don't use a where clause on a delete command, MySQL will quietly and lethally delete all records in the database table. You've been warned.
To send this command to MySQL from PHP, simply embed it into a variable and pass the variable to mysql_query: $sql = " delete from employeelisting where department = 'Facilities' "; $result = mysql_query($sql); Checking Inserts, Updates, and Deletions If the SQL statement you are sending to MySQL is inserting a new row, updating an existing row, or deleting one or more rows, you can check that the SQL code actually executed with the mysql_affected_rows function. If the function returns 0, the action failed (that is, no rows in the table were affected). Otherwise, the number returned by this function indicates how many rows were affected. $sql = "delete from info where id = 3"; $result = mysql_query($sql); $affected = mysql_affected_rows($result); if ($affected == 0) { echo "<b>warning: no deletion occurred!</b><br>"; } else { echo "deletion successful<br>"; } Creating a Table You can create tables in MySQL, but the easiest way to create them is from PHP. As with querying a database table, you compose your table creation script as a variable value and pass that variable to the mysql_query function. Table creation syntax is very straightforward in MySQL: create table nameoftable ( column1 columntype attributes, column2 columntype attributes, primary key (column_name) ) For example, the following table can contain information about an employee: create table employees ( employeeID integer not null auto_increment, firstname varchar(32) not null, lastname varchar(32) not null, email varchar(64) not null, birthdate date null, hiredate date not null primary key (employeeID) ) In this case, the employeeID serves as the primary key for the table. It is an integer and set to auto_increment. That means that each time you add a record to the table, the employeeID is automatically incremented and generated for you. The firstname, lastname, and email are all varchars, which means that they contain text characters (numbers, letters, and special characters) that are not padded with space at the end in other words, varchar fields are variable length. NOTE For a complete discussion of the different data types, see the sidebar "Data Types in MySQL."
Also take notice that all three of these fields (firstname, lastname, and email) are all set to not null, which means they have to be filled in. If they aren't filled in, MySQL will reject the insert or update operation. To insert this create table command into MySQL from PHP, embed the entire command into a variable and pass the variable to mysql_query: $sql = " create table employees ( employeeID integer not null auto_increment, firstname varchar(32) not null, lastname varchar(32) not null, email varchar(64) not null, birthdate date null, hiredate date not null primary key (employeeID) )"; $result = mysql_query($sql); Altering a Table Sometimes you need to alter an existing table's structure. MySQL enables you to change various parts of a table. To change a table's name, use the following form: alter table tablename rename new_tablename To change the employees table to the staff table, use the following: alter table employees rename staff To add or drop a field, use the add column and drop column clauses of the alter command: alter table staff add column phone varchar(16) not null alter table staff drop column birthdate The best way to change a column definition is to use the change clause of the alter command: alter table staff change phone phone varchar(20) null Notice that you have to state the name of the column twice: once to state that it's the one you want to change and again as part of the change definition. To send an alter table command to MySQL from PHP, embed the entire command into a variable and pass the variable to mysql_query: $sql = "alter table staff drop column birthdate"; $result = mysql_query($sql); Dropping a Table Sometimes you have to drop a table in other words, delete the entire structure along with all its data. To drop a table, use the drop table command: drop table staff It's as simple as that. To send a drop table command to MySQL from PHP, embed the entire command into a variable and pass the variable to mysql_query: $sql = "drop table staff"; $result = mysql_query($sql); WARNING The drop table command deletes the entire table structure and data. You've been warned.
Data Types in MySQL MySQL comes with a range of column types. Each is different from the others. char The char column type accepts text strings and has a maximum length of 255 characters. This is a fixed-length type, which means that MySQL will insert as many spaces as it needs to fill the field to its maximum value. For example, a char(16) column that receives a text string 10 characters long will be padded with 6 blank spaces. The spaces are removed from the string when data is extracted from this type of field. varchar The varchar column type accepts text strings and has a maximum length of 255 characters. This is a variable-length type, which means no padding. MySQL keeps track of how long each individual field is. Most of your text string fields will be varchar. tinytext The tinytext column type is the first of the binary text character types (or BLOBs). It is variable-length and can contain 255 characters. All 255 characters can be indexed. text The text column type is similar to tinytext, except that it can hold 65,535 characters. The first 255 characters can be indexed. mediumtext The mediumtext column type is similar to text and tinytext, but it can hold 16,777,215 characters. The first 255 characters can be indexed. longtext The longtext column type is similar to text, tinytext, and mediumtext, but it can hold 4,294,967,295 characters. The first 255 characters can be indexed. enum You can use the enum column type to restrict data entry to a certain set. It's normally used when you want yes/no or true/false responses: answer enum ('yes', 'no') default 'no' integer The integer column type can hold whole numbers. Integers can be unsigned or signed. Unsigned integers can be in the range of 0 and 4,294,967,295. Signed integers can be in the range of 2,147,483,648 to 2,147,483,648. In most cases, you'll be using unsigned integers. tinyint The tinyint column type can store small whole numbers. As with the integer data type, tinyints can be unsigned or signed. Unsigned tinyints can be in the range of 0 to 255. Signed tinyints can be in the range of 128 to 127. mediumint The mediumint column type can store small integers between 8,338,608 and 8,338,607 (signed) or 0 and 16,777,215 (unsigned). bigint The bigint column type can store integers between 9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 (signed) or 0 to 18,446,744,073,709 (unsigned). float The float column type can store a floating-point number that must be signed. Acceptable values are 3.402823466E+38 to 1.175494351E-38, 0, and 1.175494351E+38 to 3.402823466E+38. double This is a double-precision floating point column type. It must be signed. datetime This data type is for a combination of date and time values. MySQL displays the DATETIME value as YYYY-MM-DD HH:MM: format. However, you can set the values using either strings or numeric values. timestamp The TIMESTAMP data type range is between 1970-01-01 00:00:00 to 2037-12-31:23:59:59. year This can be formatted in either the default 4-digit format or a 2-digit format. The acceptable values are 1901-2155 and 0000. If you want to use the two-digit format, the acceptable values for the years 1970-2069 are 70-69. |
NOTE For a complete list of MySQL functions, be sure to visit http://www.php.net, the home page for the PHP community. The site includes complete online documentation.
| 
