·
ezSQL is a widget that makes it very fast and easy for you to
use database(s) within your PHP scripts.
·
It is one php file that you
include at the top of your script. Then, instead of using standard php database
functions listed in the php manual, you use a much smaller (and easier) set of
ezSQL functions.
·
It automatically caches
query results and allows you to use easy to understand functions to manipulate
and extract them without causing extra server overhead
·
It has excellent debug
functions making it lightning-fast to see what’s going on in your SQL code
·
Most ezSQL functions can
return results as Objects, Associative Arrays, or Numerical Arrays
·
It can dramatically increase development time
and in most cases will streamline your code and make things run faster as well
as making it very easy to debug and optimise your database queries.
·
It is a small class and will not add very much
overhead to your website.
Note:
It is assumed that you are familiar with PHP, basic Database concepts and basic
SQL constructs. Even if you are a complete beginner ezSQL can help you once you
have read and understood this tutorial.
Note: In all these examples no other code is required other than including ez_sql.php
----------------------------------------------------
----------------------------------------------------
// Select multiple
records from the database and print them out..
$users =
$db->get_results("SELECT name, email FROM users");
foreach ( $ users as
$user )
{
// Access data using
object syntax
echo $user->name;
echo $user->email;
}
----------------------------------------------------
----------------------------------------------------
// Get one row from
the database and print it out..
$user =
$db->get_row("SELECT name,email FROM users WHERE id = 2");
echo $user->name;
echo
$user->email;
----------------------------------------------------
----------------------------------------------------
// Get one variable
from the database and print it out..
$var =
$db->get_var("SELECT count(*) FROM users");
echo $var;
----------------------------------------------------
----------------------------------------------------
// Insert into the
database
$db->query("INSERT
INTO users (id, name, email) VALUES (NULL,'justin','jv@foo.com')");
----------------------------------------------------
----------------------------------------------------
// Update the
database
$db->query("UPDATE
users SET name = 'Justin' WHERE id = 2)");
----------------------------------------------------
----------------------------------------------------
// Display last query
and all associated results
$db->debug();
----------------------------------------------------
----------------------------------------------------
// Display the
structure and contents of any result(s) .. or any variable
$results =
$db->get_results("SELECT name, email FROM users");
$db->vardump($results);
----------------------------------------------------
----------------------------------------------------
// Get 'one column'
(based on column index) and print it out..
$names = $db->get_col("SELECT
name,email FROM users",0)
foreach ( $names as
$name )
{
echo $name;
}
----------------------------------------------------
----------------------------------------------------
// Same as above ‘but
quicker’
foreach ( $db->get_col("SELECT
name,email FROM users",0) as $name )
{
echo $name;
}
----------------------------------------------------
----------------------------------------------------
// Map out the full
schema of any given database and print it out..
$db->select("my_database");
foreach (
$db->get_col("SHOW TABLES",0) as $table_name )
{
$db->debug();
$db->get_results("DESC
$table_name");
}
$db->debug();
When working with databases most of
the time you will want to do one of four types of basic operations.
1. Perform
a query such as Insert or Update (without results)
2. Get
a single variable from the database
3. Get
a single row from the database
4. Get
a list of results from the database
ezSQL wraps up these four basic
actions into four very easy to use functions.
bool $db->query(query)
var $db->get_var(query)
mixed $db->get_row(query)
mixed $db->get_results(query)
With ezSQL these four functions are
all you will need 99.9% of the time. Of course there are also some other useful
functions but we will get into those later.
To install ezSQL download, unzip and
install the contents of ez_sql.zip into
the same directory within your web server.
Before you can use
ezSQL you need to set 4 basic settings within ez_sql.php. They can be found at
the top of the ez_sql.php file. They are:
define("EZSQL_DB_USER",
""); <--
mysql db user
define("EZSQL_DB_PASSWORD",
""); <-- mysql db
password
define("EZSQL_DB_NAME",
"mysql"); <-- mysql
db name
define("EZSQL_DB_HOST",
"localhost"); <-- mysql
server host
Actually you
probably only need to worry about the first three values since on most systems localhost
will be fine for the dbhost value. If you are unsure about any of the above
settings you should contact your provider or look through your providers
documentation.
If you are running
on a local machine and have just installed mySQL for the first time, you can
probably leave the user name and password empty ( i.e. = “”) until you set up a mySQL user account.
Once you have
installed ezSQL as described above you can see it in action by running
ez_demo.php via your web browser. To do this simply go to..
http://yourserver.com/install_path/ez_demo.php
If you are running
your web server on your local machine this will be..
http://127.0.0.1/install_path/ez_demo.php
What the demo does…
is use ezSQL functions to map out the table structure of your database (i.e the
database you specified at the top of ez_sql.php). You will be surprised how
little code is required to do this when using ezSQL. I have included it here so
you can get a quick feel for the compactness and speed of ezSQL.
<?php
include_once "ez_sql.php";
$my_tables =
$db->get_results("SHOW TABLES",ARRAY_N);
$db->debug();
foreach ( $my_tables as $table )
{
$db->get_results("DESC
$table[0]");
$db->debug();
}
?>
<?php
This is the standard
way to start php executing within your web page.
include_once
“ez_sql.php”;
This is how you include
ezSQL in your script. Normally you include it at the top of your script and
from that point forward you have access to any ezSQL function.
$my_tables = $db->get_results(“SHOW TABLES”,ARRAY_N);
get_results() is how you
get ‘a list’ of things from the database using ezSQL. The list is returned as
an array. In this case the std mySQL command
of ‘SHOW TABLES’ is called and the resulting list is stored in a newly created array $my_tables.
When using
$db->get_results(), if there are any results, they are always returned as
multi-dimensional array. The first dimension is a numbered index. Each of the
numbered indexes is either an object, associative array or numerical array
containing all the values for ‘one row’.
For example using
the switch ARRAY_A would produce an array that looked something like this.
$users =
$db->get_results(“SELECT id,name FROM users”,ARRAY_A);
$users[0]
= array (“id” => “1”, “name” => “Amy”);
$users[1]
= array (“id” => “2”, “name” => “Tyson”);
If you wanted a
numerical array use the switch ARRAY_N.
$users =
$db->get_results(“SELECT id,name FROM users”,ARRAY_N);
$users[0]
= array (0 => “1”, 1 => “Amy”);
$users[1]
= array (0 => “2”, 1 => “Tyson”);
If you wanted an object
(which is the default option) you don’t need a switch..
$users
= $db->get_results(“SELECT id,name FROM users”);
$users[0]->id
= “1”;
$users[0]->name
= “Amy”;
$users[1]->id
= “2”;
$users[1]->name
= “Tyson”;
Results returned as
an object make it very easy to work with database results using the numerous
array functions that php offers. For example, to loop through results returned
as an object all one needs to do is..
$users
= $db->get_results(“SELECT id,name FROM users”);
foreach(
$users as $user )
{
echo
$user->id;
echo
$user->name;
}
If
you are 100% sure that there will be results you can skip a step and do this..
foreach(
$db->get_results(“SELECT id,name FROM users”) as $user )
{
echo
$user->id;
echo
$user->name;
}
If
you don’t know whether there will be results or not you can do this..
If
( $users= $db->get_results(“SELECT id,name FROM users”) )
{
foreach(
$users as $user )
{
echo
$user->id;
echo
$user->name;
}
}
else
{
echo “No results”;
}
$db->debug();
This function prints the
most recently called sql query along with a well formatted table containing any
results that the query generated (if any) and the column info.
foreach
( $my_tables as $table)
This is the standard way to easily
loop through an array in php. In this case the array $my_tables was created
with the ezSQL command $db->get_results(“SHOW TABLES”,ARRAY_N). Because of
the ARRAY_N switch the results are returned as a numerical array.
The
resulting array will look something like..
$my_tables[0]
= array (0 => “users”);
$my_tables[1]
= array (0 => “products”);
$my_tables[2]
= array (0 => “guestbook”);
{
The foreach is
looping through each primary element of $my_tables[n] which are in turn
numerical arrays, with the format like so..
array(0 => “value”, 1 =>
“value”, etc.);
Thus, during the
foreach loop of $my_tables we have access to the value of the first column like
so:
foreach ($my_tables as $table)
{
echo $table[0];
}
If
we did the same thing using an associative array it might look like this..
$users = $db->get_results(“SELECT
id,name FROM users”,ARRAY_A);
foreach ( $users as $user )
{
echo $user[‘id’];
echo $user[‘name’];
}
But if there were no
results foreach might generate a warning. So a safer way to do the above is..
if ( $users =
$db->get_results(“SELECT id,name FROM users”,ARRAY_A))
{
foreach ( $users as
$user )
{
echo
$user[‘id’];
echo
$user[‘name’];
}
}
else
{
echo “No Users”:
}
This
works because if no results are returned then get_results() returns false.
$db->get_results(“DESC $table[0]”);
This database query
is nested within the foreach loop. Note that we are using the results of the
previous call to make a new call. Traditionally you would have to be concerned
about using different db_resource identifiers in a case like this but ezSQL
takes care of that for you, making it very easy to nest database queries.
You may be wondering
why I have used a numerical array output and not object or associative array.
The reason is because in this case I do not know what the name of the first
column will be. So I can make sure that I can always get its value by using
numerical array output and targeting the first column by element [0].
FYI: The SQL command
SHOW TABLES always names the first column a different value depending on the
database being used. If the database was named users the column would be
called Tables_in_users if the database was called customers the column
would be called Tables_in_customers and so on.
$db->debug();
This function will
always print the last query and its results (if any) to the browser. In this
case it will be for the above query..
$db->get_results(“DESC
$table[0]”);
You may have noticed
that the above get_results function is not assigning a value. (i.e. $var =
val). This is because even if you do not assign the output value of any ezSQL
function the query results are always stored and made ready for any ezSQL
function to use. In this case $db->debug() is displaying the stored results.
Then, by calling any ezSQL function using a null query you will be
accessing the stored results from the last query. Here is a more detailed
example.
Users Table..
amy, amy@foo.com
tyson, tyson@foo.com
// Any ezSQL function will store
query results..
$users = $db->get_results(“SELECT
name,email FROM users”);
// This gets a variable from the
above results (offset by $x = 1, $y = 1).
echo $db->get_var(null,1,1);
// Note: Because a null query
is passed to get_var it uses results from the previous query.
Output: tyson@foo.com
}
This closes the foreach loop
?>
This stops php
executing code
ezSQL functions
$db->get_results - get multiple
row result set from the database (or previously cached results)
$db->get_row -- get
one row from the database (or previously cached results)
$db->get_col - get one
column from query (or previously cached results) based on column offset
$db->get_var -- get
one variable, from one row, from the database (or previously cached results)
$db->query -- send a
query to the database (and if any results, cache them)
$db->debug - print last sql query and returned results (if any)
$db->vardump - print the
contents and structure of any variable
$db->select -- select
a new database to work with
$db->get_col_info - get
information about one or all columns such as column name or type
$db = new db --
Initiate new db object.
$db = new db
$db = new db -- Initiate new db
object. Connect to a database server. Select a database.
$db = new db(string
username, string password, string database name, string database host)
Does three things. (1) Initiates a
new db object. (2) Connects to a database server. (3) Selects a database. You
can also re-submit this command if you would like to initiate a second db
object. This is interesting because you can run two concurrent database
connections at the same time. You can even connect to two different servers at
the same time if you want to.
Note: For the sake of efficiency
it is recommended that you only run one instance of the db object and
use $db->select to switch between different databases on the same
server connection.
//
Initiate new database object..
$db2 = new db(”user_name”,
”user_password”, ”database_name”, “database_host”);
// Perform
some kind of query..
$other_db_tables = $db2->get_results(“SHOW
TABLES”);
// You can still
query the database you were already connected to..
$existing_connection_tables = $db->get_results(“SHOW
TABLES”);
// Print the results
from both of these queries..
$db->debug();
$db2->debug();
$db->select
$db->select -- select a new
database to work with
Description
bool $db->select(string
database name)
$db->select() selects
a new database to work with using the current database connection as created
with $db = new db.
//
Get a users name from the user’s database (as initiated with $db = new db)..
$user_name =
$db->get_var(“SELECT name FROM users WHERE id = 22”)
;
//
Select the database stats..
$db->select(“stats”);
//
Get a users name from the user’s database..
$total_hours =
$db->get_var(“SELECT sum(time_logged_in) FROM user_stats
WHERE user = ‘$user_name’”) ;
//
Re-select the ‘users’ database to continue working as normal..
$db->select(“users”);
$db->query
$db->query -- send a query to the
database (and if any results, cache them)
Description
bool $db->query(string
query)
$db->query() sends a
query to the currently selected database. It should be noted that you can send any
type of query to the database using this command. If there are any results
generated they will be stored and can be accessed by any ezSQL function as long
as you use a null query. If there are results returned the function will return
true if no results the return will be false
//
Insert a new user into the database..
$db->query(“INSERT
INTO users (id,name) VALUES (1,’Amy’)”) ;
//
Update user into the database..
$db->query(“UPDATE
users SET name = ‘Tyson’ WHERE id = 1”) ;
//
Query to get full user list..
$db->query(“SELECT
name,email FROM users”) ;
//
Get the second row from the cached results by using a null query..
$user_details =
$db->get_row(null,1);
//
Display the contents and structure of the variable $user_details..
$db->vardump($user_details);
$db->get_var
$db->get_var -- get one
variable, from one row, from the database (or previously cached results)
Description
var $db->get_var(string query
/ null [,int column offset[, int row offset])
$db->get_var() gets
one single variable from the database or previously cached results. This
function is very useful for evaluating query results within logic statements
such as if or switch. If the query generates more than one row
the first row will always be used by default. If the query generates more than
one column the leftmost column will always be used by default. Even so, the
full results set will be available within the array $db->last_results should
you wish to use them.
//
Get total number of users from the database..
$num_users =
$db->get_var(“SELECT count(*) FROM users”)
;
//
Get a users email from the second row of results (note: col 1, row 1 [starts at
0])..
$user_email =
$db->get_var(“SELECT name, email FROM users”,1,1)
;
//
Get the full second row from the cached results (row = 1 [starts at 0])..
$user =
$db->get_row(null,1);
// Both are the same value..
echo $user_email;
echo $user->email;
//
Find out how many users there are called Amy..
if ( $n =
$db->get_var(“SELECT count(*) FROM users WHERE name =
‘Amy’”) )
{
// If there are users then the if clause
will evaluate to true. This is useful because
//
we can extract a value from the DB and test it at the same time.
echo
“There are $n users called Amy!”;
}
else
{
//
If there are no users then the if will evaluate to false..
echo
“There are no users called Amy.”;
}
//
Match a password from a submitted from a form with a password stored in the DB
if ( $pwd_from_form ==
$db->get_var(“SELECT pwd FROM users WHERE name =
‘$name_from_form’”) )
{
// Once again we have extracted and
evaluated a result at the same time..
echo
“Congratulations you have logged in.”;
}
else
{
// If has evaluated to false..
echo
“Bad password or Bad user ID”;
}
$db->get_row
$db->get_row -- get one row
from the database (or previously cached results)
Description
object $db->get_ row(string
query / null [, int row offset [, OBJECT / ARRAY_A / ARRAY_N ]])
$db->get_row() gets a
single row from the database or cached results. If the query returns more than
one row and no row offset is supplied the first row within the results set will
be returned by default. Even so, the full results will be cached should you
wish to use them with another ezSQL query.
// Get a users name and email from the database and extract it into an object called user..
$user =
$db->get_row(“SELECT name,email FROM users WHERE id = 22”) ;
//
Output the values..
echo
“$user->name has the email of $user->email”;
Output:
Amy
has the email of amy@foo.com
//
Get users name and date joined as associative array
// (Note: we must specify
the row offset index in order to use the third argument)
$user
= $db->get_row(“SELECT name, UNIX_TIMESTAMP(my_date_joined) as date_joined
FROM users WHERE id = 22”,0,ARRAY_A) ;
//
Note how the unix_timestamp command is used with as this will ensure
that the resulting data will be easily
// accessible via the
created object or associative array. In this case $user[‘date_joined’] (object
would be $user->date_joined)
echo
$user[‘name’] . “ joined us on ” . date(“m/d/y”,$user[‘date_joined’]);
Output:
Amy
joined us on 05/02/01
//
Get second row of cached results.
$user
= $db->get_row(null,1) ;
//
Note: Row offset starts at 0
echo
“$user->name joined us on ” . date(“m/d/y”,$user->date_joined);
Output:
Tyson
joined us on 05/02/02
//
Get one row as a numerical array..
$user
= $db->get_row(“SELECT name,email,address FROM users WHERE id =
1”,0,ARRAY_N);
//
Output the results as a table..
echo
“<table>”;
for
( $i=1; $i <= count($user); $i++ )
{
echo
“<tr><td>$i</td><td>$user[$I]</td></tr>”;
}
echo
“</table>”;
Output:
1
amy
2
amy@foo.com
3
123 Foo Road
$db->get_results
$db->get_results – get multiple
row result set from the database (or previously cached results)
Description
array $db->get_results(string
query / null [, OBJECT / ARRAY_A / ARRAY_N ] )
$db->get_row() gets
multiple rows of results from the database based on query and returns
them as a multi dimensional array. Each element of the array contains one row
of results and can be specified to be either an object, associative array or
numerical array. If no results are found then the function returns false enabling
you to use the function within logic statements such as if.
Returning results as
an object is the quickest way to get and display results. It is also useful
that you are able to put $object->var syntax directly inside print
statements without having to worry about causing php parsing errors.
//
Extract results into the array $users (and evaluate if there are any results at
the same time)..
if ( $users =
$db->get_results(“SELECT name, email FROM users”)
)
{
// Loop through the resulting array on
the index $users[n]
foreach
( $users as $user )
{
//
Access data using column names as associative array keys
echo
“$user->name - $user->email<br>”;
}
}
else
{
// If no users were found then if
evaluates to false..
echo
“No users found.”;
}
Output:
Amy - amy@hotmail.com
Tyson
- tyson@hotmail.com
Returning results as
an associative array is useful if you would like dynamic access to column
names. Here is an example.
//
Extract results into the array $dogs (and evaluate if there are any results at the
same time)..
if ( $dogs =
$db->get_results(“SELECT breed, owner, name FROM dogs”,
ARRAY_A) )
{
// Loop through the resulting array on
the index $dogs[n]
foreach
( $dogs as $dog_detail )
{
// Loop through the
resulting array
foreach
( $dogs_detail as $key => $val )
{
//
Access and format data using $key and $val pairs..
echo
“<b>” . ucfirst($key) . “</b>: $val<br>”;
}
// Do a P between
dogs..
echo
“<p>”;
}
}
else
{
// If no users were found then if
evaluates to false..
echo “No dogs
found.”;
}
Output:
Breed: Boxer
Owner: Amy
Name: Tyson
Breed: Labrador
Owner: Lee
Name: Henry
Breed: Dachshund
Owner: Mary
Name: Jasmine
Returning results as a numerical array is
useful if you are using completely dynamic queries with varying column
names
but still need a way to get a handle on the results. Here is an example of this
concept in use. Imagine that this
script
was responding to a form with $type being submitted as either ‘fish’ or ‘dog’.
//
Create an associative array for animal types..
$animal
= array ( “fish” => “num_fins”, “dog” => “num_legs” );
//
Create a dynamic query on the fly..
if
( $results = $db->(“SELECT $animal[$type] FROM $type”,ARRAY_N))
{
foreach
( $results as $result )
{
echo
“$result[0]<br>”;
}
}
else
{
echo
“No $animal\s!”;
}
Output:
4
4
4
Note: The dynamic query would be
look like one of the following...
·
SELECT num_fins FROM fish
·
SELECT num_legs FROM dogs
It
would be easy to see which it was by using $db->debug(); after the dynamic
query call.
$db->debug
$db->debug – print last sql query and returned results (if any)
Description
$db->debug(void)
$db->debug() prints last sql query and its results (if any)
If you need to know
what your last query was and what the returned results are here is how you do
it.
//
Extract results into the array $users..
$users =
$db->get_results(“SELECT name, email FROM users”);
// See what just
happened!
$db->debug();
$db->vardump
$db->vardump – print the
contents and structure of any variable
Description
$db->vardump(void)
$db->vardump() prints the
contents and structure of any variable. It does not matter
what the structure is be it an object, associative array or numerical array.
If you need to know
what value and structure any of your results variables are here is how you do
it.
//
Extract results into the array $users..
$users = $db->get_results(“SELECT
name, email FROM users”);
// View the contents
and structure of $users
$db->vardump($users);
$db->get_col
$db->get_col – get one column from query (or
previously cached results) based on column offset
Description
$db->get_col( string
query / null [, int column offset] )
$db->get_col() extracts one column as one
dimensional array based on a column offset. If no offset is supplied the offset
will defualt to column 0. I.E the first column. If a null query is supplied the
previous query results are used.
//
Extract list of products and print them out at the same time..
foreach (
$db->get_col(“SELECT product FROM product_list”) as
$product)
{
echo $product;
}
//
Extract results into the array $users..
$users =
$db->get_results(“SELECT * FROM users”);
// Work out how many
columns have been selected..
$last_col_num =
$db->num_cols - 1;
// Print the last
column of the query using cached results..
foreach ( $db->get_col(null,
$last_col_num) as $last_col )
{
echo $last_col;
}
$db->get_col_info
$db->get_col_info - get
information about one or all columns such as column name or type
Description
$db->get_col_info(string
info-type[, int column offset])
$db->get_col_info() returns meta
information about one or all columns such as column name or type. If no
information type is supplied then the default information type of name
is used. If no column offset is supplied then a one dimensional array is
returned with the information type for ‘all columns’. For access to the full
meta information for all columns you can use the cached variable
$db->col_info
Available Info-Types:
//
Extract results into the array $users..
$users =
$db->get_results(“SELECT id, name, email FROM users”);
// Output the name
for each column type
foreach (
$db->get_col_info(“name”) as $name )
{
echo “$name<br>”;
}
Output:
id
name
email
//
Extract results into the array $users..
$users = $db->get_results(“SELECT
id, name, email FROM users”);
//
View all meta information for all columns..
$db->vardump($db->col_info);