Databases


26
Jan 11

PHP SQL Query Builder

As an excersise I have started building another php based sql query builder (separate from my stagnating php framework). So far I can do SELECT [columns] FROM table WHERE [conditions]

This is how it should be used (so far):
(for the examples, assume that $q is a query object)

$q->table('test')
	->column('test_2', 'test')
	->table('test_3', 'test_2');
//=> SELECT test_2 as test FROM test, test_3 AS test_2

You can also group conditions:

$q->table('table')
	->begin_and()
	->and_where('col_1', 1)
	->or_where('col_2', 2)
	->end_and()
	->or_where('col_3', 3, '!=');
 
//=>SELECT * FROM table WHERE ( col_1 = '1'  OR col_2 = '2' ) or col_3 != '3'

which is fun.

I have just tried this example and am pleased to say that you can put multiple groups at the start of the query:

$q->table('table')
	->begin_and()
	->begin_and()
	->and_where('col_1', 1)
	->or_where('col_2', 2)
	->end_and()
	->end_and()
	->or_where('col_3', 3, '!=');
 
//=>SELECT * FROM table WHERE ( ( col_1 = '1' OR col_2 = '2' ) ) OR col_3 != '3'

You can follow along here if you want, suggest improvements, criticize it to no end because I am doing it toally wrong, or “steal” my ideas and make your own (but give me some of the credit at least).


10
Mar 10

Using the jQuery-UI Autocomplete Widget

The latest version of jQuery-ui has an autocomplete widget. No more looking for one that will work (you get the point) with the version of jQuery that you have.

All you have to do is pull the latest one down from the jQuery-ui site and you are good to go.

First the jQuery to use the autocomplete plugin:

 
//we will be using this to cache the responses from the server
var ajaxCache = {};
 
//activate autocomplete on boxes that have the autocomplete class
$("input.auto_complete").autocomplete({
	source: function(request, response) {
		//what are we searching for
		var query_type = $(this).attr('element').attr('id');
		//the cacheterm that we use to save it in the cache
		var cachedTerm = (request.term + '' + query_type) . toLowerCase();
		//if the data is in the cache and the data is not too long, use it
		if (ajaxCache[cachedTerm] != undefined && ajaxCache[cachedTerm].length < 13) {
			//map the data into a response that will be understood by the autocomplete widget
			response($.map(ajaxCache[cachedTerm], function(item) {
				return {
					label: item.value,
					value: item.value
				}
			}));
		}
		//get the data from the server
		else {
			$.ajax({
				url: "/ajax/auto_complete.php",
				dataType: "json",
				data: {
					query_type: query_type,
					q: request.term
				},
				success: function(data) {
					//cache the data for later
					ajaxCache[cachedTerm] = data;
					//map the data into a response that will be understood by the autocomplete widget
					response($.map(data, function(item) {
						return {
							label: item.value,
							value: item.value
						}
					}));
				}
			});
		}
	},
	//start looking at 3 characters because mysql's limit is 4
	minLength: 3,
	//when you have selected something
	select: function(event, ui) {
		//close the drop down
		this.close
	},
	//show the drop down
	open: function() {
		$(this).removeClass("ui-corner-all").addClass("ui-corner-top");
	},
	//close the drop down
	close: function() {
		$(this).removeClass("ui-corner-top").addClass("ui-corner-all");
	}
});

The ajax/auto_complete.php would look something like this:

 
<?php
 
//make sure that we only allow valid query types
/**
 * @var array
 */
$validQueryTypes = array(
	'country',
	'city',
	'first_name',
	'last_name',
);
/**
 * @var string
 */
$column = (isset($_POST['query_type']) && in_array($_POST['query_type'], $validQueryTypes))? $_POST['query_type'] : null;
/**
 * @var string
 */
$q = isset($_POST['q'])? $_POST['q'].'%':null;
 
 
if($column && $q){
	switch($column){
		case 'country':
		case 'city':
			$q = new sQuery();
			$results = $q->from('addresses')
				//to make things simpler for the javascript, always select as value
				->column($column . ' as value')
				->where($column, $q, 'LIKE')
				->getAll();
 
			//SELECT $column as value FROM addresses WHERE $column LIKE '$q%';
			break;
		case 'first_name':
		case 'last_name':
			$q = new sQuery();
			$results = $q->from('users')
				->column($column . ' as value')
				->where($column, $q, 'LIKE')
				->getAll();
			//SELECT $column as value FROM users WHERE $column LIKE '$q%';
			break;
	}
 
	//$result is something like this:
	array(
		array('value'=>'Canada'),
		array('value'=>'America'),
		array('value'=>'Mexico'),
		array('value'=>'Netherlands'),
	);
 
	//then return it to the javascript
	echo json_encode($results);
}
exit;

Then the simplest part of the exercise:

 
City:
<input type="text" name="city" id="city" title="enter a city" class="auto_complete" />
 
Country:
<input type="text" name="country" id="country" title="enter a country" class="auto_complete" />
 
First Name:
<input type="text" name="first_name" id="first_name" title="enter a first_name" class="auto_complete" />
 
Last Name:
<input type="text" name="last_name" id="last_name" title="enter a last_name" class="auto_complete" />

And if they don’t have javascript enabled it doesn’t detract from the form (go progressive enhancement!).

Edit:

This post is pretty old, but just in-case you were wondering sQuery is this: https://github.com/SeanJA/ShoestringPHP/blob/master/library/classes/squery.class.php and there is also a better tested more standalone-y version here as a separate project: https://github.com/SeanJA/query-builder.


28
Sep 08

A look at PostgreSQL

Almost all last week at work I was working on adding Postgres to the database interfacing part of our php framework. The more I worked on it and did research to figure out why things were the way that they were and why certain things didn’t work the same way as I thought, because of my limited knowledge of databases other than MySQL and a bit of Oracle, they should, the more that I liked this database backend (side note: why doesn’t chrome have an add to dictionary option when right click on something you ‘misspelled’?). It is not that one is definitively better than the other, it is just a different way of tackling the same problems (well… in my case they are pretty much the same problems…).

For the most part, simple queries that you would write are the exact same:

SELECT * FROM my_table WHERE my_column = 'something'

This will of course return all of the things in the database where my_column = ‘something’, nice and simple.

Problems sometimes arise however when you want to do more complex things. For example if you want to delete everything from two tables where one column is the same as the other:

In MySQL, easy:

DELETE table1.*, table2.* FROM table1, table2 WHERE table1.column1 = table2.column1

In Postgres, it is easy as well, but different:

(Downloading and installing Postgres so I don’t mess this up… that would be embarrassing…)

CREATE TEMPORARY TABLE mytmptable(column1 text);
INSERT INTO mytemptable(SELECT table1.column1 FROM table1, table2 WHERE table1.column1 = table2.column1);
DELETE FROM table1 WHERE table1.column1 IN (SELECT * FROM mytemptable);
DELETE FROM table2 WHERE table2.column1 IN (SELECT * FROM mytemptable);

You could do the delete in a similar way to that in MySQL, but you probably wouldn’t because the other way is simpler. It is more dangerous though because of the possibility to delete everything in your tables while you are testing by accident if you miswrite the query.

Fortunately not everything is more complicated in Postgres, for example you can do:

DELETE FROM table1 WHERE column1 IN (SELECT column1 FROM table2 WHERE column1 LIKE 'some text' LIMIT 10 OFFSET 2)

and it will have no trouble deleting things from your tables. In MySQL you cannot do this because of the way that MySQL queries the tables when it uses LIMIT and OFFSET (apparently). What you have to do is this:

CREATE TEMPORARY TABLE mytemptable(column1 text);
INSERT INTO mytemptable(SELECT column1 FROM table2 WHERE column1 LIKE 'some text' LIMIT 10 OFFSET 2);
DELETE FROM table1 WHERE column1 IN (SELECT column1 FROM mytemptable)

This makes postgres a more attractive option to me… if only it supported

CREATE TABLE IF NOT EXISTS ...