• Choose a category
• All
• Classic-Fiction

By Root 285 0

write abstractions with the same function names used in LIB_mysql. In this way, you can make the code in this book work with Oracle, SQL Server, or any other database without modifying any scripts.

The source code for LIB_mysql is available from this book's website. There are other fine database abstractions available from projects like PEAR and PECL; however, the examples in this book use LIB_mysql.

Listing 6-1 shows the configuration area of LIB_mysql. You should configure this area for your specific MySQL installation before you use it.

MySQL Constants (scope = global)

define("MYSQL_ADDRESS", "localhost"); // Define IP address of your MySQL Server

define("MYSQL_USERNAME", ""); // Define your MySQL username

define("MYSQL_PASSWORD", ""); // Define your MySQL password

define("DATABASE", ""); // Define your default database

Listing 6-1: LIB_mysql server configurations

As shown in Listing 6-1, the configuration section provides an opportunity to define where your MySQL server resides and the credentials needed to access it. The configuration section also defines a constant, "DATABASE", which you may use to define the default database for your project.

There are three functions in LIB_mysql that facilitate the following:

Inserting data into the database

Updating data already in the database

Executing a raw SQL query

Each function uses a similar interface, and each provides error reporting if you request an erroneous query.

The insert() Function

The insert() function in LIB_mysql simplifies the process of inserting a new entry into a database by passing the new data in a keyed array. For example, if you have a table like the one in Figure 6-3, you can insert another row of data with the script in Listing 6-2, making it look like the table in Figure 6-4.

Figure 6-3. Example table people before the insert()

$data_array['NAME'] = "Jill Monroe";

$data_array['CITY'] = "Irvine";

$data_array['STATE'] = "CA";

$data_array['ZIP'] = "55410";

insert(DATABASE, $table="people", $data_array);

Listing 6-2: Example of using insert()

Figure 6-4. Example table people after executing the insert() in Listing 6-2

The update() Function

Alternately, you can use update() to update the record you just inserted with the script in Listing 6-3, which changes the ZIP code for the record.

$data_array['NAME'] = "Jill Monroe";

$data_array['CITY'] = "Irvine";

$data_array['STATE'] = "CA";

$data_array['ZIP'] = "92604";

update(DATABASE, $table="people", $data_array, $key_column="ID", $id="3");

Listing 6-3: Example script for updating data in a table

Running the script in Listing 6-3 changes values in the table, as shown in Figure 6-5.

Figure 6-5. Example table people after updating ZIP codes with the script in Listing 6-3

The exe_sql() Function

For database functions other than inserting or updating records, LIB_mysql provides the exe_sql() function, which executes a SQL query against the database. This function is particularly useful for extracting data with complex queries or for deleting records, altering tables, or anything else you can do with SQL. Table 6-1 shows various uses for this function.

Table 6-1. Example Usage Scenarios for the LIB_mysql_exe_sql() Function

Instruction

Result

$array = exe_sql(DATABASE, "select *

$array[1]['ID']="1";

from people");

$array[1]['NAME']="Kelly Garrett";

$array[1]['CITY']="Culver City";

$array[1]['STATE']="CA";

$array[1]['ZIP']="90232";

$array[2]['ID']="2";

$array[2]['NAME']="Sabrina Duncan";

$array[2]['CITY']="Anaheim";

$array[2]['STATE']="CA";

$array[2]['ZIP']="92812";

$array[3]['ID']="3";

$array[3]['NAME']="Jill Monroe";

$array[3]['CITY']="Irvine";

$array[3]['STATE']="CA";

$array[3]['ZIP']="92604";

$array = exe_sql(DATABASE, "select * from people where ID='2'");

$array['ID']="2";

$array['NAME']="Sabrina Duncan";

$array['CITY']="Anaheim";

$array['STATE']="CA";

$array['ZIP']="92604";

List($name)= exe_sql(DATABASE, "select NAME from people where ID='2'");

$name = "Sabrina Duncan";

exe_sql(DATABASE,