WEBvivo SQL abstraction library

SQL wrapper index


  • SQL wrapper Overview

  • WEBvivo SQL wrapper is a class to keep compatibility with Justin Vincent ezSQL wrapper syntax, but using native PHP PDO. It is almost identical and can be used to replace obsolete ezSQL code. You can also use PDO with $wv->pdo method, used by this wrapper.
  • WEBvivo SQL wrapper is more than two times faster than original ezSQL library.
  • WEBvivo SQL wrapper allow migration from old WEBvivo framework (mawi versions), keeping the actual code untouched.

Quick Examples

Example 1

// Select multiple records from the database and print them out
$users = $wv->db->get_results("SELECT name, email FROM users");

foreach ( $users as $user ){
    echo $user->name;
    echo $user->email;
}


Example 2

// Get one row from the database and print it out..
$user = $wv->db->get_row("SELECT name,email FROM users WHERE id = 2");

echo $user->name;
echo $user->email;



Example 3

// Get one variable from the database and print it out..
$var = $wv->db->get_var("SELECT count(*) FROM users");

echo $var;



Example 4

// Insert into the database
$wv->db->query("INSERT INTO users (id, name, email) VALUES (NULL,'Doe','doe@foo.com')");



Example 5

// Update the database
$wv->db->query("UPDATE users SET name = 'Doe' WHERE id = 2)");



Example 6

// To display query debug, before one or more queries:
$wv->db->debug=1;

//To stop showing debug info, put below the last query:
$wv->db->debug=0;



Example 7

// Display the structure and contents of any result(s) .. or any variable
$results = $wv->db->get_results("SELECT name, email FROM users");

$wv->db->vardump($results);



Example 8

// Get 'one column' (based on column index) and print it out..
$names = $wv->db->get_col("SELECT name,email FROM users",0)

foreach ( $names as $name ){
    echo $name;
}



Example 9

// Same as above ‘but quicker’
foreach ( $wv->db->get_col("SELECT name,email FROM users",0) as $name ){
    echo $name;
}



Example 10

// Map out the full schema of any given database and print it out..
$wv->db->debug=1;
foreach ( $wv->db->get_col("SHOW TABLES",0) as $table_name ){
    $wv->db->get_results("DESC $table_name");
}
$wv->db->debug=0;

  • 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

SQL wrapper functions


$wv->db->get_results() //get multiple row result set from the database
$wv->db->get_results_array() //same as get_results, but returns a multidimensional array instead an object
$wv->db->get_results_simple() //Select only a column and returns a simple array
$wv->db->get_row() //get one row from the database
$wv->db->get_col() //get one column from query based on column offset
$wv->db->get_var() //get one variable, from one row, from the database
$wv->db->query() //send a query to the database
$wv->db->vardump() //print formated object

SQL wrapper variables


$wv->db->num_rows //Number of rows that were returned (by the database) for the last query (if any)
$wv->db->insert_id //ID generated from the AUTO_INCRIMENT of the previous INSERT operation (if any)
$wv->db->rows_affected //Number of rows affected (in the database) by the last INSERT, UPDATE or DELETE (if any)

SQL wrapper functions explained

$newdb = new dbw()

$wv->db() is already initializad in WEBvivo, you don't need to initialized it again.

If you need a new database instance, you must create a new PDO instance first:
$newpdo = new PDO("mysql:host=localhost;dbname;charset=utf8mb4";username,password);

And then, a new dbw wrapper instance:
$newdb = new dbw($newpdo);

NOTE: You can use a PDO instance for default database without initialized it, just try $wv->pdo method instead $wv->db one.

$wv->db->query()

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

Description

bool $wv->db->query(string query)

$wv->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 SQL wrapper 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
$wv->db->query("INSERT INTO users (id,name) VALUES (1,’Amy’)") 


Example 2

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


Example 3

// Query to get full user list..
$wv->db->query("SELECT name,email FROM users") ;

// Get the second row from the cached results by using a null query..
$user_details = $wv->db->get_row(null, OBJECT,1);

// Display the contents and structure of the variable $user_details..
$wv->db->vardump($user_details);

$wv->db->get_var()

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

Description

var $wv->db->get_var(string query / null [,int column offset[, int row offset])

$wv->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 $wv->db->last_results should you wish to use them.
Example 1

// Get total number of users from the database..
$num_users = $wv->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 = $wv->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 = $wv->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 = $wv->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 == $wv->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";

}

$wv->db->get_row()

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

Description

object $wv->db->get_ row(string query / null [, OBJECT / ARRAY_A / ARRAY_N [, int row offset]])

$wv->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 SQL wrapper query.
Example 1

// Get a users name and email from the database and extract it into an object called user..
$user = $wv->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=$wv->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 = $wv->db->get_row(null,OBJECT,1) ;

// Note: Row offset starts at 
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 = $wv->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
 */

$wv->db->get_results()

$wv->db->get_results() – get multiple row result set from the database (or previously cached results)

Description

array $wv->db->get_results(string query / null [, OBJECT / ARRAY_A / ARRAY_N ] )

$wv->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.
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 = $wv->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
"; } }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 = $wv->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 = $wv->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 $wv->db->vardump(); after the dynamic query call.
*/

$wv->db->get_results_array()

$wv->db->get_results_array() – same as $wv->db->get_results(), but it returns results as multidimensional arrays, instead an object.

$wv->db->get_results_simple()

$wv->db->get_results_simple(), returns results as simple array. You must select a single field in query.

$results = $wv->db->select_results_simple("SELECT name FROM addressbook");

array(
    [1] => Indalecio
    [2] => Eustaquio
    [3] => Gumersindo
)

$wv->db->get_col()

$wv->db->get_col() – get one column from query (or previously cached results) based on column offset

Description

$wv->db->get_col( string query / null [, int column offset] )

$wv->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 ( $wv->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 = $wv->db->get_results("SELECT * FROM users");

// Work out how many columns have been selected..
$last_col_num = $wv->db->num_cols - 1;

// Print the last column of the query using cached results..
foreach ( $wv->db->get_col(null, $last_col_num) as $last_col ){
    echo $last_col;
}

$wv->db->vardump()

Description

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

It can be used with any object, array or var, not necesarely related to SQL results.
Example

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 = $wv->db->get_results("SELECT name, email FROM users");

// View the contents and structure of $users
$wv->db->vardump($users);

$wv->db->debug

$wv->db->debug – enable/disable debug output
Debug object var is a switch to enable/disable debug output.

Description

put $wv->db->debug=1; in the part of the code where you want to start debugging the queries generated by DBW and $wv->db->debug=0; in the point where you want it to end. (You can also use true/false).

It can include one or more SQL statements between enable/disble switch, it will show the result of all those between the two commands.
Example

If you need to know what your last query was and what the returned results are here is how you do it.

// Extract and show command, result and data into the array $users..
$wv->db->debug=1;
$users = $wv->db->get_results("SELECT name, email FROM users");
$wv->db->debug=0;