Oracle Save Point Statement

The Savepoint statement is useful when you want to identify a transaction point to roll back to. This could be a situation where you are updating values and want to confirm aggregate totals of some sort. Here is an example:

UPDATE employees
SET salary = 10000
WHERE last_name = 'Ciarlo';
SAVEPOINT ciarlo_sal;

UPDATE employees
SET salary = 15000
WHERE last_name = 'Doherty';
SAVEPOINT doherty_sal;

SELECT SUM(salary) FROM employees;

ROLLBACK TO SAVEPOINT ciarlo_sal;

UPDATE employees
SET salary = 14000
WHERE last_name = 'Doherty';

COMMIT;

The semantics of the syntax is to specify the name of the savepoint to be created. Savepoint names must be distinct within a given transaction. That is, if you create a second savepoint with the same identifier as an earlier savepoint, then the earlier savepoint is lost. After a savepoint has been created, you can either continue processing, commit your work, roll back the entire transaction, or roll back to the savepoint.

Hierarchical SQL

This post reviews several ways to write Oracle SQL to reflect a hierarchical structure in an organization. The caveat here is that the table you are querying must be mapped with hierarchical data.

In the example below, the SQL statement is querying a table with Employee ID's and their respective Managers. The CONNECT BY clause denotes the parent rows and the child rows of the hierarchy.

SELECT emp_id, l_name, mgr_id
FROM employees
CONNECT BY PRIOR emp_id = mgr_id;

This query produces the result set that defines the relationship between employees and managers.

EMP_ID	L_NAME	MGR_ID
101	Brown	100
108	Greenberg	101
109	Jones	108

In this next example, the LEVEL psuedocolumn shows the parent and child rows.

SELECT emp_id, l_name, mgr_id, LEVEL
FROM employees
CONNECT BY PRIOR emp_id = mgr_id;

The result set looks like this:

EMP_ID	L_NAME	MGR_ID	LEVEL
101	Koch	100	1
108	Greenberg	101	2
109	Franks	108	3

In this last example, the query includes the START WITH clause to specify a root row to start the hierarchy. In addition, the Oracle keyword, SIBLINGS, used in the ORDER BY clause preserves the order within the hierarchy.

SELECT l_name, emp_id, mgr_id, LEVEL
FROM employees
START WITH emp_id = 100
CONNECT BY PRIOR emp_id = mgr_id
ORDER SIBLINGS BY l_name;

SQL Subqueries

There are many different ways to solve a SQL problem. As requests for database information become more complex, the need for added programming tools has become more important. One of the biggest enhancements to SQL has been the SQL subquery. As the name implies, this is a query inside a main query.

There are different names for a subquery: inner query, nested SELECT, sub-SELECT. The main thing to note is that a subquery can be embedded in different places inside the main SQL statement. For example, a subquery can be used as a:
--As a column expression
--As a filter inside a WHERE or HAVING clause
--As a datasource inside the FROM clause

The syntax is straightforward: enclose the subquery in parentheses and do not use a semicolon at the end of the subquery statement. The innermost query is processed first, then the result is used in the main query.

The SQL standard defines three types of subqueries:
Row subquery:
A subquery that returns more than one column and one row.
Table subquery:
A subquery that returns one or more columns and zero to one or more rows.
Scalar subquery:
A subquery that returns only one column and one row, or in other words a single distinct value.

Row subqueries are used in the WHERE clause of the main query. The SQL standard defines a Row Value Constructor for use in the WHERE, HAVING, or ON clause. However, not many vendors have implemented it.  An example of a Row Value Constructor used as a subquery would be something like this:

SELECT student_id, last_name
FROM students
WHERE (instructor_id, department_id) = (100, 150);

Here, the SELECT is on columns in students table where other columns have certain values.  Although the Row Value Constructor is not implemented, Oracle created a compound WHERE clause that works like this:

SELECT student_id, instructor_id, department_id
FROM students
WHERE (instructor_id, department_id) IN (SELECT instructor_id, department_id FROM students WHERE student_id  IN (20, 30));

Here, the inner subquery is structured with two clauses in the WHERE statement. 

A table subquery is used primarily in the FROM clause of the main query, or as an insert into an existing table. As this subquery returns many columns and rows, it is used in the FROM clause of an existing query like so:

SELECT S.student_id, S.last_name, S.score, D.score_avg
FROM students,
(SELECT instructor_id, avg(score) as "score_avg" FROM students GROUP BY department_id) D
WHERE S.department_id=D.department_id AND S.score >  D.score_avg;

A scalar subquery returns only one value, and it is used widely. You can use a scalar subquery as a substitute for a value expression. In addition, it can be used as a column subquery or in the WHERE clause of the main query. Here is an example:

SELECT student_id, last_name, score
FROM students
WHERE score > (SELECT avg(score) FROM students);

Here, the subquery in the WHERE clause selects the average score for all students. The result is the WHERE clause sets up a dynamic query as the average score for all students changes as additional tests are given.

The SQL Union Join

The Union Join statement is a powerful join in SQL. The Union Join is not to be confused with the Union set operator. The Union Join enables you to write a SQL statement to retrieve records that do not match on both sides of the join. This is the exact opposite of a left and right outer join, or for that matter, a full outer join. This is part of the SQL standard. However, not many database vendors have implemented it.

To run a Union Join, you can use the Full Outer join syntax and construct a condition that is always false. For example table 1 Full Outer Join table 2 on 3=6.  Here is an example using a department table(department_id PK) and employee table(department_id FK), with the cardinality of one department may contain many employees

SELECT E.last_name,E.first_name,E.department_id,D.department_id
FROM employees E FULL OUTER JOIN departments D on 3=6;

With this SQL statement, you will see all the records from departments with no matching employees and all employees with no matching department.

The SQL ROLLUP Function

As a SQL programmer, if you need to calculate subtotals and a grand total in a query, the SLQ ROLLUP function fits the bill. The ROLLUP function is OLAP(Online Analytical Processing) SQL often used in data warehousing transactions.

Lets say you have an employee table with dept_id and job_id foreign keys to department and job tables. To determine the number of employees in each department and in each job position with subtotals and a grand total, you could write the following query:

SELECT department_id,job_id,Count(*) FROM employees
GROUP BY ROLLUP(department_id,job_id);

This query will produce the following outcome:

DEPT_ID    JOB_ID               COUNT(*)
---------------------- ---------- ---------

50	ST_MAN	5
50	SH_CLERK	20
50	ST_CLERK	20
50		45	Note: department subtotal
110	AC_MGR	1
110	AC_ACCOUNT	1
110		2
		107	Note: grand total

As you can see, the ROLLUP function calculates subtotals by job and department and a grand total all in one statement.

The INTERSECT And MINUS Operators In SQL

This post shows INTERSECT and MINUS operators that make nice additions to your SQL toolkit. First let's establish the tables we'll be querying. Say we have 2 tables: Employee and Job History defined as such:

Employees: employee_id,first_name,last_name,email,phone_number,hire_date,job_id,department_id
employee_id is primary key(PK).
And
Job_History:
employee_id,start_date,end_date,job_id,department_id
The combination of employee_id and start_date is the primary key and employee_id, alone, is the foreign key(FK) back to the Employees table.

Now, the INTERSECT operator returns all rows that are common to multiple queries as shown in the example below:

SELECT employee_id,job_id FROM employees
INTERSECT
SELECT employee_id,job_id FROM job_history;

The rules for using INTERSECT with multiple table queries are:
--The number of columns and datatypes being used in the SELECT statement must be the same. The names of the columns do not have to be identical.
--Reversing the order of the queries does not change the outcome.
--The intersect operation does not ignore NULL values.

On a single table you can achieve intersection results by putting the AND operator in the WHERE clause as follows:

SELECT last_name, first_name
FROM employees
WHERE job_id ='CLERK' AND Salary = 25000;

Here, the intersection is obtained by using specific column values.

The MINUS operator looks at the difference between multiple queries. With MINUS, the order of the two sets is important as it will yield different results when the order of the two query sets is reversed. Like the INTERSECT operator, the number of columns and their respective data types being used in the SELECT must be the same. And the names of the columns do not have to be identical. Here is an example of how the MINUS operator works:

SET 1: 3,5,7,9,12      SET 2: 2,3,5,7,9,11
The difference of set 1 and set 2: (3,5,7,9,12) - (2,3,5,6,9,11) = 12
The difference of set 2 and set 1: (2,3,5,7,9,11) - (3,5,7,9,12) = 2,11

Here the order of the sets was reversed and yielded different results. Now look at this query using the MINUS operator:

SELECT employee_id FROM employees
MINUS
SELECT employee_id FROM job_history;

Now based on the tables as previously defined, this query will yield employees who have never changed jobs. That's pretty handy....

Connect to MySQL using PHP PDO

The PHP Data Objects(PDO) extension provides a consistent object-oriented interface for using PHP to access databases and execute SQL queries. PDO provides a data access layer abstraction layer, which means that, regardless of what database you are using, you can use the same functions to issue queries and fetch data. You cannot use the PDO extension to perform any database operations, but use the database specific PDO driver to access the database server.

The PDO class constructor takes the following parameters of the data source name(DSN), database name, username, and password. In the example below, a MySQL database connection object($dbc) is instantiated using a localhost server. The $dbc object establishes a connection to the test database using the applicable logon credentials of $dbuser and $pwd. Here is a simple example.

<?php
$dbc = new PDO(' mysl: = localhost; dbname= test ', $dbuser, $pwd');
?>

If there are any connection problems, a PDO exception is thrown. If the exception is not caught and handled, the default action is to terminate the script and a back trace is displayed showing the database connection details including the username and password. To mitigate this security risk, it is best to handle the exception as it occurs. Depicted below is a script to handle connection errors.

<?php
query('SELECT * FROM testCase') as $row )
{
print_r($row);
}
$dbc = 'null ';
} catch (PDOException $e)
{
echo 'Error!:' . $e->getMessage() . ;
die();
}
?>

Upon successful connection to the database, an instance of the PDO class is returned to your script. The connection remains active for the lifetime of the PDO object. To close the connection, you need to destroy the object by ensuring that all remaining references to it are deleted--you do this by assigning NULL to the variable that holds the object. If you don't do this explicitly, PHP will automatically close the connection when your script ends.

<?php
$dbc = new PDO(' mysql:=localhost; dbname = test '; $dbuser, $dbpwd);
//use the connection here

//and now close the connection
$dbc = 'null ';
?>

Restarting MySQL from XAMPP

The Apache Foundation provides an open source tool called XAMPP with modules that include the Apache webserver,PHP, MySQL, a file server called FileZilla and an email client named Mercury Mail. XAMPP comes with an all-in-one installer which can be downloaded either as an executible or zip file. Upon download and installation, all of these components can be started as services. For web and database development purposes, it is really nice to have a local webserver as opposed to numerous ftp's to a web host. It save alot of time.

However, I sometimes find that MySQL does not start through the XAMPP control panel GUI. One solution is to find the Windows batch files for uninstalling and installing MySQL as a service. These batch files are found in the directory C:\xampp\mysq. First right click on the uninstall batch file. A shell window appears and follow the prompts to uninstall MySQL as a service. Then right click on the install batch file and click through the prompts.

Go back to the XAMPP control service, click to start MySQL as a service and then click the Start button. That's all there is to it.

SQL Rand Function

The RAND() function is used for returning random numbers. For example, the statement SELECT rand() will return a random number between 0 and 1 as such:

mysql> SELECT rand();
+-------------------+
| rand() |
+-------------------+
| 0.138089752884665 |
+-------------------+
1 row in set (0.00 sec)

This function comes in handy when you are selecting customers to receive a special offer or perhaps raffling off a prize in a sweepskate contest. To show how this function would work, I have designed a sample table called Pie from a MySQL database called Dessert. The rand function will be used to generate a random someValue integer.

SELECT * FROM pie;

pie_id	name	someValue
1	apple	1
2	cherry	4
3	peach	2
4	berry	3
5	cream	5
6	pumpkin	6
7	mince	7
8	rubarb	10
9	pecan	8
10	mud	9

If you write the SQL query: SELECT someValue FROM pie ORDER BY rand() limit 1; you will generate a random someValue integer. When I ran this query 5 times, I got the following values: 2,4,9,4,8.

The following works for MS SQL and Oracle:
MS SQL:
SELECT TOP someValue FROM pie
ORDER BY someValue;

Oracle:
SELECT someValue FROM (
SELECT someValue from pie ORDER BY dbms_random.someValue) WHERE rownum=1;

The SQL*Plus Buffer

Using SQL*Plus is the classic way of creating and managing Oracle databases. It is available with all installations on all operating systems. So, if you know how to use it, you'll be able to process an Oracle database on any operating system.

To use SQL*Plus, find the icon under the Start/Programs/Oracle.. and click it. Sign onto your database using the username and password. You should see a window similiar to the one shown below:

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 26 13:51:58 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL>

Among the many features of SQL*Plus is a text editor. As you type into SQL*Plus, your keystrokes are placed into a buffer. When you press Enter, SQL*Plus will save what you typed into a line in the buffer and go to a new line, but it will neither finish the statement not execute it.

To see the contents of the buffer, type LIST. The line shown with an asterisk is the current line. You can change the current line by entering LIST followed by the line number such as LIST 1. To change the contents of the current line, enter change/astring/bstring, where astring is the string you want to change, and bstring is the replacement string.

The Change command is fine for making small changes, but it could be unworkable for editing longer expressions such as stored procedures. For such purposes, you can set up SQL*Plus to connect to your text editor. Before doing this, however, you should create a directory for your Oracle code, say C:\MyDirectory\OracleCode. Find the SQL*Plus icon your C drive or desktop and right click to reveal properties and enter the name of the new directory in the Start In text box. Click OK and restart SQL*Plus.

Next, click the Edit menu in the SQL*Plus window menu and then select Editor/Define Editor. You can enter the name of your editor here. Notepad is the default editor and works just fine for editing your SQL code. Click OK. Now you have defined your text editor for SQL*Plus and set it to point to your directory. Now whenever you type Edit, SQL*Plus will invoke Notepad or whichever editor you selected.

You can now create,save,and edit files of code in that directory. For example, if you enter the following:

SELECT Table_Name
FROM USER_TABLES;

After the contents appear, type Edit, SQL*Plus will bring up your editor with the contents of the buffer. Use SAVE AS to give the file a new name, such as EX1.txt. Close your editor and you will return to SQL*Plus. To edit the file you just created, type EditEX1.txt. and you will enter your editor with that file. When you exit your editor and return to SQL*Plus, EX1.txt will be stored in the SQL*Plus buffer. To cause the buffer contents to execute, enter the right-leaning slash(/).

Limiting Query Results in MySQL

When using MySQl databases, a SQL clause that can be used in queries is LIMIT. In a SELECT query, WHERE dictates which records to return, and ORDER BY decides how those records are sorted. The LIMIT clause states how many records to return. It is used like so:

SELECT * FROM tablename LIMIT x;
In this case, only the initial x records are returned from the query. So to return 5 records, you would write the query SELECT * FROM tablename LIMIT 5;

Using the format below, you can have y records returned, starting at x.

SELECT * FROM tablename LIMIT x,y;
SELECT * FROM tabename LIMIT 10,10;

This statement would return records 11 through 20. This is because like arrays, result sets begin at 0 when it comes to LIMIT, so 10 is the 11th record. To return the last records in a data set, you would write the query as follows:

SELECT * FROM tablename LIMIT 20,1;
Here the 20th record is returned.

The LIMIT x,y clause is used most frequently when paginating query results(showing them in blocks over multiple pages). However, the LIMIT clause does not improve query processing speed as the SQL engine still has to assemble the entire result set and then truncate the list. In addition, the LIMIT clause is not part of the SQL standard and is therefore not available in all databases.

Nonetheless, in MySQL, you can use LIMIT with most types of queries, not just with SELECT statements. You can also use the LIMIT with WHERE and/or ORDER BY clauses, but LIMIT must always come last in the query.

Using Output Buffering in PHP

By default, anything that a PHP script prints or any HTML outside of the PHP tags(even in include files) is immediately sent to the Web browser. Output buffering is a PHP feature that overrides this behavior. Instead of immediately sending HTML to the Web browser, that output is placed in a buffer-temporary memory. Then, when the buffer if flushed, it is sent to the Web browser. There can be performance benefits with output buffering, but the main benefit is that it eliminates those headers already sent error messages.

Some functions such as header(),setcookie(), and session_start() can only be called if nothing has been sent to the Web browser. Without output buffering, nothing is sent to the Web browser until the end of the page. As such, you can call these functions at any point in a script.

To begin output buffering, use the ob_start() function. This should be at the beginning of your script right after the opening PHP tag. Once this function is called, every echo() and similiar function will send data to a memory buffer rather than to the Web browser. And, HTTP calls such as header() and setcookie() will not be buffered and operate as usual.

At the end of your script, call the ob_end_flush() function to send the accumulated buffer to the Web browser. This has the effect of turning off the output buffering.

Using The Tenary Operator in PHP

The tenary operator provides a more concise way to structure conditional statements. Instead of if-else or case statements, you could write it as follows:

(condition) ? value1 : value2

The condition in parentheses is evaluated; if it is true, the first value will be returned (value1). If the condition is false, value2 is returned. Because the tenary operator returns a value, the entire structure is often used to assign a value to a variable or used as an argument for a function. For example, in PHP the line below will print out SET or NOT SET, depending upon the status of the variable $var.

echo (isset($var)) ? 'SET' : 'NOT SET';

Another example of using the tenary operator would be to alternate the background colors on table rows. Here is an example:

$bg = '#eeeeee'; //set the initial background color
$bg = ($bg == '#eeeeee' ? '#ffffff' : '#eeeeee'); //switch the background color
echo '

Here the initial row background color is assigned to the variable $bg and then the tenary operation, alternating colors, is assigned back into $bg. The result is a table with rows of alternating background colors.

Creating Files Using PHP

When working with files, PHP has several useful functions such as fopen(),fread(), fwrite() and fclose(). Fopen() opens a file or url and the sytax is fopen(string $filename, string $mode). If filename is of the form "scheme://...", it is assumed to be a URL and PHP will search for a protocol handler (also known as a wrapper) for that scheme.

If PHP has decided that filename specifies a local file, then it will try to open a stream on that file. The file must be accessible to PHP, so you need to ensure that the file access permissions allow this access.

If PHP has decided that filename specifies a registered protocol, and that protocol is registered as a network URL, PHP will check to make sure that allow_url_fopen is enabled. If it is switched off, PHP will emit a warning and the fopen call will fail.

Here is a simple PHP script that opens an existing text file, creates and writes to a new file and then closes the file.

?php
//create and open a new file
$newfile = fopen("c:\\xampp\\htdocs\\philduhe.txt", "a+");
fwrite($newfile, "This is Phil's new file.");
fclose($newfile);
echo "All done!";
?>

The mode parameter specifies the type of access or stream to the file. It may be any of the following listed below. In my script, I used the mode of +a to read and write to to the file.

'r' Open for reading only; place the file pointer at the beginning of the file.
'r+' Open for reading and writing; place the file pointer at the beginning of the file.
'w' Open for writing only; place the file pointer at the beginning of the file and truncate the file to zero length. If the file does not exist, attempt to create it.
'w+' Open for reading and writing; place the file pointer at the beginning of the file and truncate the file to zero length. If the file does not exist, attempt to create it.
'a' Open for writing only; place the file pointer at the end of the file. If the file does not exist, attempt to create it.
'a+' Open for reading and writing; place the file pointer at the end of the file. If the file does not exist, attempt to create it.
'x' Create and open for writing only; place the file pointer at the beginning of the file. If the file already exists, the fopen() call will fail by returning FALSE. If the file does not exist, attempt to create it.
'x+' Create and open for reading and writing; place the file pointer at the beginning of the file. If the file already exists, the fopen() call will fail. If the file does not exist, attempt to create it.

On the Windows platform, be careful to escape any backslashes used in the path to the file, or use forward slashes.

Using control flow operators in MySQL

There are several control flow operators available in MySQL. This post will show how to use the CASE operator. One version of this operator employs the following syntax:

CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result...] [ELSE result] END.

In this version, the CASE statement returns the result value when value=compare_value. Here is an example:

mysql> SELECT CASE 1 WHEN 1 THEN 'apple'
-> WHEN 2 THEN 'orange'
-> ELSE 'more' END;
+--------------------------------+
CASE 1 WHEN 1 THEN 'apple' WHEN 2
THEN 'orange' ELSE 'more' END
+--------------------------------+
| apple |
+--------------------------------+
1 row in set (0.00 sec)

Another version of the CASE statement is:

CASE WHEN [condition] THEN result [WHEN condition THEN result...] [ELSE result] END.

Here is how this version would work as follows:

mysql> SELECT CASE WHEN 2>1 THEN 'true'
-> ELSE 'false' END;
+-------------------------------------------+
| CASE WHEN 2>1 then 'true' ELSE 'false' END
+-------------------------------------------+
| true
+-------------------------------------------+
1 row in set (0.00 sec)

In this example, the result value is returned for the first condition that is true. If there was no matching result value, the result after ELSE is returned, or NULL if there is no ELSE part.

The default return type of a CASE expression is the compatible type of all return values. If used in a string context, the result is returned as a string. If used in a numeric context, then the result is returned as a decimal or integer value.

The syntax of the CASE expression shown here differs slightly from that of the SQL CASE statement which does not allow an ELSE NULL clause, and it is terminated with END CASE instead of END.

MYSQL User Defined Variables

In MySQL, you can define your own variables to use in queries. You can store user defined variables in one statement and refer to it in another statement. User defined variables are specfic to a connection session. That is, a user variable defined in one session cannot be seen or used in another session. All variables for a given client connection are automatically freed when that client exits.

User variables are written as @var, where the variable name "var" consists of alphanumeric characters from the current character set, “.”, “_”, and “$”. One way to establish user variables is through a Set statement as follows:

SET @var = expr [, @var = expr] ...
For SET, either = or := can be used as the assignment operator.

You can also assign a value to a user variable in statements other than SET. In this case, the assignment operator must be := and not = because = is treated as a comparison operator in non-SET statements. Here is a simple example of how user-defined variables could be applied:

mysql> set @var1=2,@var1=4,@var3=6;
Query OK, 0 rows affected (0.00 sec)
mysql> select @var1,@var2,@var3,@var4:=@var1+@var2+@var3;

The above select statement will return values of 2,4,6,12.

Using MySql Prepared Statements

Prepared Statements were introduced in MySQl 4.1 and provide the option to set up a SQL statement query once and execute it many times with different parameters.  Prepared Statememts replace ad hoc query strings, are executed on the server, and in doing offer increased security and performance.

A typical prepared statement would look something like:
 SELECT * FROM employee WHERE code = ?

The ? is a placeholder. When you execute the above query, you would need to supply the value for it, which would replace the ? in the query above.  I have prepared(no pun intended..) a sample from a database I created to keep track of job applications.  The syntax is depicted below:

mysql>PREPARE fa FROM 'SELECT * FROM job WHERE title=?';
Query OK, 0 rows affected (0.00 sec)
Statement Prepared
mysql>SET @fa_param='Financial Analyst';
Query OK, 0 rows affected (0.00 sec)
mysql> EXECUTE fa USING @fa_param;

+--------+-------------------+--------+-------------------------------------+
| job_id | title             | salary | requirements | requisition | company_id |
+--------+-------------------+--------+-------------------------------------+----+
|     11 | Financial Analyst |      0 | excel;pivot tables | NULL |       11 |
|     17 | Financial Analyst |      0 | Crystal Reports    | NULL |       24 |
..........
|    117 | Financial Analyst |     24 | nonprofit experience | NULL |     78 |
|    141 | Financial Analyst |      0 | monitoring and reporting |NULL | 108 |
+--------+-------------------+--------+-------------------------------------+----+

13 rows in set (0.00 sec)
mysql>DEALLOCATE PREPARE fa;
Query OK, 0 rows affected (0.00 sec)
mysql>

Prepared Statements work with DML(INSERT,DELETE,UPDATE, and REPLACE), CREATE TABLE and SELECT queries. The increased security comes from separating data  being supplied from SQL logic.  In addition,  the separation of logic and data can help prevent a very common type of vulnerability called an SQL injection attack.

Normally when you are dealing with an ad hoc query, you need to be careful when handling the data that you received from the user. This entails using functions that escape all of the necessary trouble characters, such as the single quote, double quote, and backslash characters. This is unnecessary when dealing with prepared statements. The separation of the data allows MySQL to automatically take into account these characters and they do not need to be escaped using any special function.


Second, the increase in performance comes from the MySQL engine parsing the query only once. When you initially prepare the statement, MySQL will parse the statement to check the syntax and set up the query to be run. Then if you execute the query many times, it will no longer have that overhead. This pre-parsing can lead to decreased processing time if you need to run the same query many times, such as when doing many INSERT statements.

As a side note, PHP has an API to Prepared Statements through its "mysqli" extension. You can read more about it in the mysqli section of the PHP Manual.

Uploading Files With PHP

The process of uploading a file has two dimensions. First, the HTML form must be displayed with the proper code to allow for uploads. Then, upon submission of the form, your PHP script must copy the uploaded file to its final destination.

However, for this process to work, a few things must be in place:
-PHP must run with the right settings
-A temporary storage directory must exist with the correct permissions
-The final storage directory must exist with the correct permissions.

There are several settings in the PHP configuration file(php.ini) that dictate file uploads. In the file uploads section of this file, you will see the following three lines:

file_uploads=On
;upload_tmp_dir =
upload_max_filesize = 2M

The first line dictates whether or not uploads are allowed. The second line states where the uploaded files should be temporarily stored. On most systems, this setting can be left commented out. On Windows, you would create a temporary directory, set to a value such as C:\temp, making sure the line is not preceded by a semicolon.

Finally, a maximum upload file size is set. The "M" is shorthand for megabytes in configuration settings. Save the php.ini file and restart your Web server. You can confirm the settings by running the phpinfo()script.

Next prepare your HTML upload form:

<html>
<head>
<title>File Upload Form</title>
</head>
<body>
This form allows you to upload a file to the server.<br>
<form enctype="multipart/form-data" action="upload.php" method="post">
Please choose a file: <input type="file" name="uploaded" /><br />
<input type="submit" value="Upload" />
</form>
</body>
</html>

And a PHP script which receives the HTML form is depicted below:

<?php
$target = "upload/";
$target = $target . basename( $_FILES['uploaded']['name']) ;
if(move_uploaded_file($_FILES['uploaded']['tmp_name'],$target))
{
echo "The file ". basename( $_FILES['uploaded']['name']). " has been uploaded";
}
else {
echo "Sorry, there was a problem uploading your file.";
}
?>

Hope this helps someone out there!

MySQLI_Real_Escape_String()---A Useful Function

If you are collecting information through an html form or providing user access to a MySQL database, the mysqli_real_escape_string() function can be a very useful tool to employ. This function can be used to prevent database attacks from malicious users by "escaping" special characters such as "\r,"\n",!\,etc.

Situations where such special characters cause problems are usernames and passwords. The code below shows an example of what could happen.

// We didn't check username and password.
// Could be anything the user wanted! Example:
$_POST['user'] = 'jerry';
$_POST['pwd'] = "' OR ''='";

The SQL sent would be:
SELECT * FROM users
WHERE user='jerry' AND password='' OR ''=''
This means that anyone could log in without a valid password!

To prevent this, you "escape" special characters with the mysqli_real_escape_string() function, which takes two arguments, the database connection variable and the string variable to be escaped. The function returns a string on success or FALSE for failure. Depicted below is the syntax:

$string=mysqli_real_escape_string($dbc,$escapeString)--where $dbc is the database link connection and $escapeString is the string to be escaped.

Using the Post method, the username and password would be "escaped" like this:

$user=mysqli_real_escape_string($dbc,$_POST['user']);
$pwd=mysqli_real_escap_string($dbc,$_POST['pwd']);

SQL Grouping Sets

This post in a continuation of a series discussing OLAP SQL. OLAP stands for Online Analytical Processing, often used in data warehousing transactions. In Oracle, Grouping Sets are an extension of the Group By clause that enables specifying multiple groups of data.

With Grouping Sets, you just specify the desired groups and the Oracle database computes all groupings specified in the Grouping Sets clause and combines the results in a Union All operation. The Union All operation allows duplicate rows in the result set. The syntax is depicted below:

SELECT col1,col2,col3,count(*);
FROM table1
GROUP BY GROUPING SETS(col1,col2,col3);

Without the Grouping Sets syntax, you would have to run separate queries and then do a Union operation. This can be inefficient if several groupings are required and can make your SQL code more complex. With Grouping Sets, this is can be done in one step and is more efficient as the underlying table is accessed only once.

Oracle SQL Decode Function

The SQL Decode function offers a way to to write expressions. In addition, sometimes you want to write complex programming logic. The problem is that SQL does not provide this logic, as SQL is a command based language. This is where vendor specific functions such as the Oracle Decode come into play.

Functions in SQL create links between the SQL programming language and the database programming environment, allowing for multi-line constructs. The Decode function is one such example. This function acts like an If..Else...End..If construct with some variation. The syntax for the Decode function is:

DECODE(col/expr, search1, result1[, search2, result2 ]...[, default ])

Decode compares each column/expression for the search value one by one. If the column/expression matches the search value, then the result1 is returned. If no match is found, then Oracle returns default. If default is omitted, then Oracle returns null.

The example below decodes the value region_id. If region_id is 1, then the function returns 'Northeast'; if region_id is 2, then it returns 'South'; and so forth. If region_id is not 1, 2, 3, or 4, then the function returns 'Other'.

SELECT employees_id,last_name,first_name,
DECODE (region_id, 1, 'Northeast',
                                  2, 'South',
                                  3, 'Midwest',
                                  4, 'West',
                                   'Other')
as "Region" FROM employees;

You can test for many more search values if needed. The maximum number of components in the DECODE function, including expr, searches, results, and default, is 255.

SQL GROUPING SETS

This post is a continuation of a series on OLAP SQL.  OLAP refers to Online Anayltical Processing used in data warehousing transactions.  The Grouping Sets clause in SQL allows you to see the results of a query in different groups.

For example, suppose you wanted to see a report of sales by sales reps but also wanted to see sales grouped by week and territory.  You could write two queries to obtain two reports.  Or you could write one query that used the Union All operator on the two groupings.

This is where the Grouping sets clause in SQL comes in handy.  Grouping Sets allows you to define multiple groups in one data set.  The advantage is that the underlying table is accessed only once which speeds up query performance on large data sets.  The syntex for Grouping Sets is:

SELECT col1,col2,count(*)
FROM table1
WHERE.....
GROUP BY GROUPING SETS((col1,col2),(col3,col4));

Here is an example using the hr schema in Oracle:

SELECT department_id,job_id,count(*)
FROM employees
GROUP BY GROUPING SETS(department_id,job_id);

The result set would like this:

DEPARTMENT_ID	JOB_ID	COUNT(*)
	IT_PROG	5
	AC_MGR	1
	AC_ACCOUNT	1
	ST_CLERK	20
	HR_REP	1
100		6
30		6
90		3
20		2
70		1
110		2

As you can see, the Grouping By Sets function offers efficient processing of large data sets than querying two  tables separately.

jQuery oEmbed Content

At times, it may be appropriate to embed video content directly in your website.  The oEmbed API enables you to dispaly embeded content such as Vimeo videos, Flickr photos, and YouTube movies.  OEmbed is a format for allowing embedded comtent from third-party sites.  Facebook is a poplar user of the oEmbed format where you can copy and past a link and the link is converted into an embedded movie.  To use oEmbed, you have to first download the jQuery oEmbed plugin . Then you enter the following files in script tags in the head section of your HTML.

src="jquery.min.js" type="text/javascript"
src="jquery.oembed.js" type="text/javascript"

Next, you add a div and and input tag to hold the embedded content in the link:

input id="olink" type="text"
div id="oembed"

Now add some functionality with Javascript. This should be placed in javascript tags right after the body tag in your HTML:

$(document).ready(function() {
$("#olink").blur(function (){
olink = $("#olink").val();
$("#oembed").oembed(olink);
})
});

Next copy and paste a link into the text box and when you move your cursor away from the box, the link will convert to embedded content into your web page. A nice technique to have in your web design toolkit. Visit this link jQuery documentation for the full documentation of the oEmbed plugin.

OLAP IN SQL

OLAP stands for Online Analytical Processing, a new feature in SQL that is being implemented in database products by various vendors. OLAP SQL is typically used in data warehouses. OLAP differs from OLTP which is Online Transaction Processing used in transaction systems. The key differences are:

OLTP:	OLAP:
Data changes often	Data is static
Current data stored	Stores historic data
Few indexes	Many indexes to speed up complex queries
Fast and optimized	Accurate and complete
Highly normalized	Highly denormalized

There are several OLAP SQL functions. This post will discuss the RANK function, aggregate and analytical. The RANK function calculates the rank of a value in a group of values and returns a number. An example is shown below:

SELECT RANK(45500, .05) WITHIN GROUP
(ORDER BY salary, commission_pct) "Rank"
FROM employees;

Rank
----------
55

As an analytic function, RANK computes the rank of each row returned from a query with respect to the other rows returned by the query, based on the values of the value_expression in the order_by_clause. Here is an example of an analytical RANK:

SELECT department_id, last_name, salary, commission_pct,
RANK() OVER (PARTITION BY department_id
ORDER BY salary DESC, commission_pct) "Rank"
FROM employees WHERE department_id = 80;

DEPARTMENT_ID	LAST_NAME	SALARY	COMMISSION_PCT	Rank
80	Brown	14000	.4	1
80	Lewis	13500	.3	2
80	Smith	12000	.3	3
80	Carlson	11500	.25	4
80	Chambers	11000	.3	5
80	Adams	11000	.3	5

There is another version of the RANK function, called DENSE RANK which computes the rank of a row in an ordered group of rows and returns the rank as a NUMBER. The ranks are consecutive integers beginning with 1. Rank values are not skipped in the event of ties. Rows with equal values for the ranking criteria receive the same rank. This function is useful for top-N and bottom-N reporting.

The following example computes the ranking of a hypothetical employee with the salary $45,500 and a commission of 5%.

SELECT DENSE_RANK(45500, .05) WITHIN GROUP
(ORDER BY salary DESC, commission_pct) "Dense Rank"
FROM employees;

Dense Rank
-------------------
3

There it is the OLAP RANK SQL. I'll discuss other OLAP SQL functions in future posts.

Hierarchical SQL

In a previous post, I showed an example of a self-join query. In this post, I will show examples of using Hierarchical SQL is to display self-join data in a more intuitive way. Hierarchical SQL is an extension of Oracle PL/SQL. This extension to the standard SQL provides a way to loop through all the branches and levels of multi-level self-join data. Consider the following SQL query:

SELECT last_name,||' '||'reports'||' '|| to'||' '||
nvl(PRIOR last_name, 'no one') "Reporting Relationship"
FROM employees
START WITH employee_id=100
CONNECT BY PRIOR employee_id=manager_id;

Here, the hierarchical query clause allows you to select rows in a hierarchical order. The nvl function returns the value of 'no one' if the last_name is null. The Prior keyword is used to link the current record to the "prior" record, or next higher in the hierarchy. The Connect By key words are used to establish a link between the primary and foreign key. The execution of this query would return the result set as seen below:

Reporting Relationship
--------------------------------------------------------------
King reports to no one
Kochhar reports to King
Greenberg reports to Kochhar
Faviet reports to Greenberg
Chen reports to Greenberg
Sciarra reports to Greenberg
Urman reports to Greenberg
Popp reports to Greenberg

Using hierarchical SQL, you could also create an organizational chart from this query statement:

SELECT lpad(last_name,Length(last_name)+(LEVEL*4)-4,'-') as "Organization Chart"
FROM employees
START WITH employee_id=100
CONNECT BY PRIOR employee_id=manager_id;

The lpad function returns a character string padded on the left side;the Length function returns the length of the string. The Level keyword indicates the level of the hierarchy(number of nested levels). Here we are creating an expression that creates more hyphens the deeper the nesting level. This query would produce the following result:

Organization Chart
King
----Kochhar
--------Greenberg
------------Faviet
------------Chen
------------Sciarra
------------Urman
------------Popp
--------Whalen
--------Mavris
--------Baer
--------Higgins
------------Gietz
----De Haan
--------Hunold
------------Ernst
------------Austin
------------Pataballa
------------Lorentz

As shown in these examples, hierarchical SQL is a nice tool to have in your database query toolkit.