Procedural File: ez_select.php
Source Location: Program_Root/classes/general/ez_select.php
Page Details:
EZ Select is a simple PHP class for generating SELECT queries.
It is intended to make it easier to create complex queries especially when creating them based on user input. http://www.phpclasses.org/browse.html/package/452.html
----------------------------------------
Using EZ Select
----------------------------------------
To use EZ Select, you can either copy and paste the code out of ez_select.php into your own program/library, or simply include ez_select.php like this:require("ez_select.php"); The interface is very straightforward and demonstrated in example1.php, example2.php, and example3.php
----------------------------------------
Creating a new query
----------------------------------------
Simply create a new object, like so:$my_query = new ez_select();
----------------------------------------
Adding a DISTINCT clause to the query
----------------------------------------
To add a DISTINCT clause to your query, simply call the distinct method without parameters. You can call this method any time before calling make() or show() $my_query->add_distinct();
----------------------------------------
Adding fields to the query
----------------------------------------
Adding fields to select (SELECT field_1, field_2 ...) is done via the add_field() method:
$my_query->add_field("field_1");
$my_query->add_field("my_table2.field_2");
----------------------------------------
Adding tables to select from
----------------------------------------
To add tables to select from (the FROM ... part of the query), use the add_table() method:
$my_query->add_table("my_table1");
$my_query->add_table("my_table2");
You can do LEFT JOIN and such like this:$my_query->add_table("my_table1 LEFT JOIN my_table2 ON my_table1.id=my_table2.id");
----------------------------------------
Adding where clauses
----------------------------------------
Adding a where clause is done by using the add_where_clause() method
$my_query->add_where_clause("my_table1.id = my_table2.id");
$my_query->add_where_clause("my_table1.name != 'foobar'");
----------------------------------------
Adding group by clauses
----------------------------------------
Use the add_group_by() method. They will appear in the query in the order that you add them.
$my_query->add_group_by("my_table1.id");
----------------------------------------
Adding order by clauses
----------------------------------------
Use the add_order_by() method. They will appear in the query in the order that you add them.
$my_query->add_order_by("my_table1.id");
$my_query->add_order_by("RAND()");
The ORDER BY clause in the generated query would look like: "ORDER BY my_table1.id,RAND()"
----------------------------------------
Setting the order type
----------------------------------------
Setting the order type (Ascending or Descending, i.e. ASC or DESC) is done by the set_order_type() method:$my_query->set_order_type("ASC");
----------------------------------------
Setting the limit/offset
----------------------------------------
Use the set_limit() method to add a LIMIT clause to the query:$my_query->set_limit(0, 25); The first argument is the offset (what row to start at), and the second is the number of rows to return. The offset defaults to 0 if you don't specify anything.
----------------------------------------
Outputting the query (debugging)
----------------------------------------
To output the color coded sample query, use the show() method:$my_query->show();
----------------------------------------
Generating the query
----------------------------------------
To return the final query, use the make() method:
$query = $my_query->make();
$result = mysql_query($query);
OR:
$result = mysql_query( $my_query->make() );
EXAMPLE 1
require("ez_select.php");
$my_query = new ez_select();
// we want to select from "foo" and "bar" tables
$my_query->add_table("foo");
$my_query->add_table("bar");
// add the fields we want to select
$my_query->add_field("foo.id");
$my_query->add_field("foo.name");
$my_query->add_field("bar.id");
// where clauses to restrict our query
$my_query->add_where_clause("foo.id = bar.id");
$my_query->add_where_clause("foo.name != 'foobar'");
// order by foo.id, then RAND()
$my_query->add_order_by("foo.id");
$my_query->add_order_by("RAND()");
$my_query->set_order_type("ASC");
// get first 25 results
$my_query->set_limit(0, 25);
// show our query
$my_query->show();
// at this point you would run the query through mysql_query() or whatever
// $result = mysql_query( $my_query->make() )
EXAMPLE 2
// Assumes tings like $dont_show_foobars, $order_by, $num_result, etc.
// came from a form or some sort of user input
require("ez_select.php");
$my_query = new ez_select();
// we want to select from "foo" and "bar" tables
$my_query->add_table("foo");
$my_query->add_table("bar");
// add the fields we want to select
$my_query->add_field("foo.id");
$my_query->add_field("foo.name");
$my_query->add_field("bar.id");
// where clauses to restrict our query
$my_query->add_where_clause("foo.id = bar.id");
if ( isset($dont_show_foobars) ) {
$my_query->add_where_clause("foo.name != 'foobar'");
}
// how did they want to order the results?
switch( $order_by ) {
case 'foo.id':
$my_query->add_order_by("foo.id");
break;
case 'foo.name':
$my_query->add_order_by("foo.name");
break;
default:
$my_query->add_order_by("RAND()");
break;
}
// how many results to show?
if ( $num_results ) {
$my_query->set_limit(0, $num_results);
} else { // or do 25 by default
$my_query->set_limit(0, 25);
}
// Let's see our query
$my_query->show();
// at this point you would run the query through mysql_query() or whatever
// $result = mysql_query( $my_query->make() );
Tags:
| 