Core Web Application Development with PHP and MySQL - page 221

Summary

We have quickly scooted through the installation and configuration of web servers, the MySQL database server, and PHP5 language engine in this appendix. Although this will help us get up and running quickly with our book and let us play with scripts quickly, it is rarely a good idea to take web applications live without spending much more time learning about the various configuration options and spending the time fiddling with them.

There are a number of security documents written for each of the servers, and those, combined with the advice in this book and an evening or two of playing, should be enough to provide all the information you need to fully prepare the software for your exact needs.

Appendix B. Database Function Equivalents

Although most of the examples in this book use a SQL syntax that is specific to MySQL versions 4.1 and greater, you are not absolutely required to use it when writing your web applications. In fact, many companies have an investment in other relational database management systems and want to keep using them.

This appendix shows the equivalent SQL syntax for various table types, operations, and other important aspects of web applications covered in this book. These suggested replacements might not always be the best way to perform the equivalent tasks , but they will most certainly get you started.

Working with Databases

Let's look at some basic operations you might want to do when interacting directly with your database server.

Listing Available Databases

To list those databases available to you when you first connect to a database server, use the following:

Server	Syntax
MySQL	`SHOW DATABASES;`
PostgreSQL	`\l`
Oracle	Individual Oracle server instances are centered on a particular database. This does not really make sense for this server.
Microsoft SQL Server	Set `Database=master` and execute `sp_databases` . The first column returned from `sp_databases` contains the name . The second column is the size of the database.

Listing Tables in a Database

To list tables available in the currently selected database ( USE DatabaseName ), use this:

Server	Syntax
MySQL	`SHOW TABLES;`
PostgreSQL	`\dt`
Oracle	`SELECT * FROM all_tables user_tables dba_tables`
Microsoft SQL Server	select * from sysobjects where type = 'u'

Describing a Table

To see the structure of a table, use this:

Server	Syntax
MySQL	`DESCRIBE TableName;`
PostgreSQL	`\d TableName`
Oracle	`DESC TableName;`
Microsoft SQL Server	SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE FROM pubs.INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'titles'

Data Description and Creation

Now that we know which databases and tables we are using, we can look at differences in how we specify various features during the creation process.

Creating Databases to Work with UTF-8

To create databases that understand UTF-8 input and store string values as Unicode text in tables, we use the following queries. We also use these to specify the default sorting order (collation) for these new databases:

Server	Syntax
MySQL	CREATE DATABASE DBName DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
PostgreSQL	CREATE DATABASE MooCow WITH ENCODING 'UNICODE' or `initdb -E UNICODE` from command line.
Oracle	When Creating Database, specify `UTF8` or `AL32UTF8` as `CHARACTER SET`
Microsoft SQL Server	Done at installation time.

Setting the Connection to UTF-8

Even if the database server is set up to use UTF-8 correctly, we need to ensure that the connection is set up correctly:

Server	Syntax
MySQL	`SET NAMES 'utf8'`
PostgreSQL	`SET NAMES 'utf8'` or `SET CLIENT_ENCODING TO 'utf8'`
Oracle	Set the `NLS_LANG` environment variable to `AL32UTF8` or simply `UTF8` . Do this before starting Apache or IIS.
Microsoft SQL Server	Make sure `freeTDS` is configured properly.

Auto-Incrementing Fields

Fields that have an auto-incrementing index after every row insertion are quite different from server to server:

Server	Syntax
MySQL	`AUTO_INCREMENT`
PostgreSQL	`SERIAL, BIGSERIAL datatypes`
Oracle	CREATE SEQUENCE supplier_seq MINVALUE 1 MAXVALUE 999999999999999999999999999 START WITH 1 INCREMENT BY 1 CACHE 20; sequence.nextval is critical use of feature. INSERT INTO suppliers (supplier_id, supplier_name) VALUES (supplier_seq.nextval, 'Some Company');
Microsoft SQL Server	`IDENTITY`

Date/Time Column Types

To create a column of a date or time type, you must change your SQL from server to server:

Server	Syntax
MySQL	`DATE, TIME, DATETIME`
PostgreSQL	`DATE, TIME, TIMESTAMP`
Oracle	`DATE` (does `DATE` and times), `TIMESTAMP` (date with more precision) and `INTERVAL HOURS TO SECONDS (HH:MM:SS)` , `INTERVAL YEAR TO DAY (YYYY-MM-DD)` , `TIME` datatype exists too.
Microsoft SQL Server	`DATETIME` , `SMALLDATETIME` only

Binary Column Types

To store binary data in your database, you need to use a special data type:

Server	Syntax
MySQL	`BLOB`
PostgreSQL	`bytea`
Oracle	`BLOB`
Microsoft SQL Server	`image`

Large Text Column Types

Because most CHAR and VARCHAR columns are limited to 255 characters , you sometimes need larger text column types. These are as follows :

Server	Syntax
MySQL	`TEXT`
PostgreSQL	`TEXT`
Oracle	`CLOB`
Microsoft SQL Server	`TEXT`

Bulk Data Insertion

Inserting data into your database outside of PHP code can sometimes be done by using what is known as bulk data insertion . It is very specific to servers:

Server	Syntax
MySQL	`LOAD DATA` and `LOAD DATA LOCAL`
PostgreSQL	`COPY TableName (col1,...,coln) FROM FILENAME WITH ...`
Oracle	Control File: `LOAD DATA` INFILE <dataFile> APPEND INTO TABLE <tableName> FIELDS TERMINATED BY '<separator>' (< list of all attribute names to load >) and then data file
Microsoft SQL Server	You will have to do this by hand or in code.