Skip to content

Documentation

Jump to bottom Edit New page
Christian edited this page Jul 11, 2020 · 14 revisions

Documentation Introduction

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.

Important Note: If you use ezsql inside a function you write, you will need to put global $db; at the top.

In version 3 and higher there are global functions available to retrieve the object. getInstance(), tagInstance(getTagCreated)

If you need more help, try reading these articles:

Any articles referencing WordPress database engine is a good source of what kind of ecosystem can be built with the flexibility of what this library provides. However, the ease of use and process this library initially fostered, spread to other areas, leading to hard to follow, and bad coding habits by today's standards.

Version 4 of this library attempts to, beside all the additional features, remove some bad coding styles, bring the library to modern coding practices of which, follow proper OOP and be PSR compliant.

This version further break things introduced in version 3 that broke version 2.1.7.

See the CHANGE LOG for more information.

ezSQL Methods (functions) Table of Contents

Click any of the methods below to be taken to the detailed documentation

Basic Actions

$db->query -- send a query to the database (and if any results, cache them)

$db->get_var -- get one variable, from one row, from the database (or previously cached results)

$db->get_row -- get one row from the database (or previously cached results)

$db->get_results -- get multiple row result set 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_col_info -- get information about one or all columns such as column name or type

General Methods

coming soon...

Shortcut Table Methods

innerJoin -- Create an INNER JOIN in your SQL query

leftJoin -- Create a LEFT JOIN in your SQL query

rightJoin -- Create a RIGHT JOIN in your SQL query

fullJoin -- Create a FULL JOIN in your SQL query

more coming soon...

Shortcut SQL Methods

$db->escape -- Format a string correctly to stop accidental mal formed queries under all PHP conditions

These can now be found on the dedicated Shortcut Methods page.

Old Methods

$db->select -- select a new database to work with

Information about the following methods have been moved to the Troubleshooting page: $db->debug, $db->debugOn, $db->debugOff, $db->varDump, $db->hide_errors, $db->show_errors

Detailed Documentation

Basic Actions Content

$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

Example 1

 // Insert a new user into the database..
$db->query("INSERT INTO users (id,name) VALUES (1, 'Amy')");

Example 2

 // Update user into the database..
$db->query("UPDATE users SET name = 'Tyson' WHERE id = 1");

Example 3

 // 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, OBJECT, 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.

Example 1

 // Get total number of users from the database..
$num-users = $db->get_var("SELECT count(\*) FROM users") ;

Example 2

 // 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,OBJECT,1);

 // Both are the same value..
 echo $user-email;
 echo $user->email;

Example 3

 // 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.";
}

Example 4

 // 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 [, OBJECT / ARRAY_A / ARRAY_N [, int row offset]])

$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.

Example 1

 // 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

Example 2

// 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",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

Example 3

 // Get second row of cached results.
 $user = $db->get_row(null,OBJECT,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

Example 4

 // Get one row as a numerical array..
 $user = $db->get_row("SELECT name,email,address FROM users WHERE id = 1",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_results() 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.

Example 1 – Return results as objects (default)

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

Example 2 – Return results as associative array

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

Example 3 – Return results as numerical array

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->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.

Example 1

 // 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;
}

Example 2 – Working with cached results

 // 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

mySQL

· name - column name

· table - name of the table the column belongs to

· max-length - maximum length of the column

· not-null - 1 if the column cannot be NULL

· primary-key - 1 if the column is a primary key

· unique-key - 1 if the column is a unique key

· multiple-key - 1 if the column is a non-unique key

· numeric - 1 if the column is numeric

· blob - 1 if the column is a BLOB

· type - the type of the column

· unsigned - 1 if the column is unsigned

· zerofill - 1 if the column is zero-filled

ibase

· name - column name

· type - the type of the column

· length - size of column

· alias - undocumented

· relation - undocumented

MS-SQL / Oracle / Postgress

· name - column name

· type - the type of the column

· length - size of column

SQLite

· name - column name Example 1

 // 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

Example 2

 // 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);

General Methods Content

coming soon...

Shortcut Table Methods Content

joining

This documentation is covering all 4 join types

innerJoin -- Create an INNER JOIN in your SQL query

leftJoin -- Create a LEFT JOIN in your SQL query

rightJoin -- Create a RIGHT JOIN in your SQL query

fullJoin -- Create a FULL JOIN in your SQL query

Description

xxxJoin( string $leftTable, string $rightTable, string $leftColumn, string $rightColumn, string $tableAs, $condition )

For multiple select joins, combine rows from tables where on condition is met

Example 1

 // Create a selecting statement
 $result = $db->selecting("my_table", "*", leftJoin("my_table", "their_table", "my_table_id", "their_table_my_id"));

 // Will output
 "SELECT * FROM my_table LEFT JOIN their_table AS their_table ON my_table.my_table_id = their_table.their_table_my_id"

Example 2

 // Create a selecting statement
 $result = $db->selecting("my_table", "*", leftJoin("my_table", "my_table", "my_table_parent_id", "my_table_id", "parent_table"));

 // Will output
 "SELECT * FROM my_table LEFT JOIN my_table AS parent_table ON my_table.my_table_parent_id = parent_table.my_table_id"

Shortcut SQL Methods Content

$db->escape

$db->escape – Format a string correctly in order to stop accidental malformed queries under all PHP conditions.

Description

$db->escape( string )

$db->escape() makes any string safe to use as a value in a query under all PHP conditions. I.E. if magic quotes are turned on or off. Note: Should not be used by itself to guard against SQL injection attacks. The purpose of this function is to stop accidental malformed queries.

Example 1

 // Escape and assign the value..
 $title = $db->escape("Justin's and Amy's Home Page");

 // Insert in to the DB..
$db->query("INSERT INTO pages (title) VALUES ('$title')") ;

Example 2

 // Assign the value..
 $title = "Justin's and Amy's Home Page";

 // Insert in to the DB and escape at the same time..
$db->query("INSERT INTO pages (title) VALUES ('". $db->escape($title)."')") ;

Old Methods Content

$db->select - for mysql only.

$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.

Example

 // 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");

Disk Caching

ezsql has the ability to cache your queries which can make dynamic sites run much faster.

If you want to cache EVERYTHING just do..

$db->setUse_Disk_Cache(true);

$db->setCache_Queries(true);

$db->setCache_Timeout(24);

Add a custom footer
Add a custom sidebar
Clone this wiki locally