WEBvivo SQL abstraction library

SQL wrapper index

• Overview
• Quick examples
• Introduction
• Functions resume
• Variables resume
• Functions and examples
  • new dbw()
  • query()
  • get_var()
  • get_row()
  • get_results()
  • get_results_array()
  • get_col()
  • vardump()
  • debug

Quick Examples

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

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

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

echo $var;

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

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

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

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

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

// Same as above ‘but quicker’
foreach ( $wv->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..
$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;

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

$newpdo = new PDO("mysql:host=localhost;dbname;charset=utf8mb4";username,password);

$newdb = new dbw($newpdo);

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

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

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

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

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

}

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

}

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

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

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

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

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

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

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

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

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

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

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