Today’s web applications are becoming more and more advanced, and most of them are relying on data stored in databases. In most cases several queries have to be executed to get the initial data in the application, so a quick way to get this job done would be usefull. Thats why I’ve made this advanced PHP query function to use in my own projects, but I’m sure many of you will find it usefull too.

Some of its features are custom SQL parameters like LAST_INSERTED_ID or INSERT_ID_FROM(n), executing different queries in one line of code, quickly returning SELECT queries as arrays, returning the new ID as the query result for INSERT-statements and so on.

Executing several SQL-statements easily

Suppose we need several SELECT-queries to retrieve the data needed for our application to run. Many of you already have made query-functions to retrieve the database resultset in just one line of code, but this probably still creates a new connection for each query you execute. Instead of doing that, we can loop through an array of SQL-statements and execute them all in the same connection. You’ll get the same result, but only one connection to the database is created, which results in a better function performance.

Offcourse we still need to be able to fetch all result-sets returned by our queries. This is where the “$query_results”-array comes in handy. I’ve placed a small example below to clear things up.

1
2
3
4
5
6
7
8
9
10
11
12
13
14

// when executing multiple queries, the function will always
// return the result from the last executed SQL-statement
// you can execute one query by just passing the 
// SQL-statement as a string, or execute multiple by passing
// an array of SQL-statements.
$sessions = query( array( "SELECT * FROM db_accounts",
	"SELECT * FROM db_hits_np",
	"SELECT * FROM db_sessions_np") );
 
// we can get the other results through $query_results
// pass the index of the SQL-statement to get its result
// be carefull, the indexes start from 0 !		
$accounts = $query_results[1];
$hits_np = $query_results[2];

Getting the result as an associative array

This is basically the same functionality as the mysql_fetch_array() method, but it saves you a line of code. You can specify a second (optional ) parameter when calling the function to specify if you want the result of the query to be returned as a normal resultset or as an associative array.

Set this optional parameter to “true” if you’d like an associative array to be returned, false otherwise. The default value is false.

SQL INSERT-statement functions

There are many situations where we need to insert a new record to the database, and right after use the newly inserted ID in our code. We can retrieve the new ID through the SQL “@@IDENTITY” command or PHP’s mysql_insert_id() function, but wouldn’t it be a nice feature to get the ID as a result of every INSERT-statement ?  This function does it all for you.

Custom SQL syntax for easier coding with INSERT-statements

It happens quite a lot when we insert several new records in a database, and we need the newly added ID in other SQL statements. Instead of using our query result in our next SQL-statement, we can add a bunch of statements and define in our SQL which ID should come instead of the given parameter. There are 2 custom SQL variables available:

LAST_INSERTED_ID

This parameter returns the last inserted ID, as you could probably guess. Here’s an example:

1
2
3
4
5
6
7

// we're using the LAST_INSERTED_ID here to 
// use the newly returned ID of the last
// INSERT-statement in this connection. 
// Don't use this if the last query was not 
// an INSERT-statement
query( array( "INSERT INTO db_first_table(name, value, something) VALUES('name', 'value', 'something')",
	"INSERT INTO db_second_table(first_id, value) VALUES(LAST_INSERTED_ID, 'value')" ) );

INSERT_ID_FROM(number)

Less obvious to use is the INSERT_ID_FROM variable. Before executing your query, this variable is replaced by the ID returned from one of the previous SQL INSERT-statements executed in the same database connection. We can define the SQL-statements index in the parameter. Another example:

1
2
3
4
5
6
7
8

// we're using the INSERT_ID_FROM here to
// the newly added ID of one of the previous
// INSERT-statements of this connection.
// pass the index of the INSERT-statement
// to the parameter
query( array( "INSERT INTO db_first_table(name, value, something) VALUES('name', 'value', 'something')",
	"INSERT INTO db_second_table(first_id, value) VALUES(LAST_INSERTED_ID, 'value')",
	"INSERT INTO db_third_table(first_id, value) VALUES(INSERT_ID_FROM(0), 'value')" ) );

The Query-function

So here it is, the function I’ve spent 902 words on in this post:
( You can easily download the function in the zip-file attached below )

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

$c_host = 'localhost';
$c_database = 'database_name';
$c_username = 'username';
$c_password = 'password';
 
function query($sql, $arr=false){
	// Advanced PHP Query function by Nick Van der Vreken
	// more information about this function on
	// http://www.bydust.com/advanced-php-query-functionadvanced-php-query-function/
	// please leave this comment intact if you're using my function :-)
 
	global $c_host, $c_database, $c_username, $c_password, $query_inserted_id, $query_results;
 
	$query_inserted_id = array();
	$query_results = array();
	$queries = is_array($sql)?$sql:array($sql);
	$conn = mysql_connect( $c_host, $c_username, ($c_password != '')?$c_password:NULL );
	mysql_select_db( $c_database, $conn );
	foreach($queries as $i => $query){
		if( eregi('LAST_INSERTED_ID', $query) ){ // LAST_INSERTED_ID parameter
			if( count($query_inserted_id) ) 
				$query = str_replace( 'LAST_INSERTED_ID', $query_inserted_id[count($query_inserted_id) -1], $query );
			else die('Error on LAST_INSERTED_ID, there were no insert-statements executed in the current connection');
		}
		if( eregi('INSERT_ID_FROM', $query) ){ // INSERT_ID_FROM parameter
			$s = strpos( $query, 'INSERT_ID_FROM(');
			$n = substr( $query, $s + 15, 1);
			$id = ( $n < count($query_inserted_id) ) ? $query_inserted_id[$n] : die('Error on INSERT_ID_FROM, wrong index');
			$query = substr( $query, 0, $s ) . $id . substr( $query, strpos( $query, ')', $s) + 1 );
		}
		//echo "<br/><b>Query $i: </b> $query";
		$result = mysql_query(  $query , $conn );
		array_push( $query_results, $result); 
		array_push( $query_inserted_id, mysql_insert_id($conn) );
		if( ( $i == count($queries) -1 ) && ( strpos( $query, 'INSERT') === 0 || strpos( $query, 'insert') === 0 ) ) $result = $query_inserted_id[count($query_inserted_id) -1];
	}
	mysql_close($conn);
	if($arr) $result = ( $result && mysql_num_rows($result) ) ? mysql_fetch_array($result) : NULL;
	return $result;
}

Don’t forget to set the database information in the variables mentioned, otherwise this won’t work !

If you have any fixes or additions, feel free to share with us ;-)