Database Queries - Advanced Security - PHP Register/Login System

Advanced Security comes with a simple database abstraction class that you can use to communicate with the database. ASdatabase class extends native PDO class, you can use any PDO function you want. You can learn more about PDO inside the PHP documentation.

Opening the Connection

You can get the instance of ASDatabase class out of the container, like following:

$db = app('db');

Since it is resolved out of the container as a singleton, every time you call app('db') you will get the same instance of ASDatabase class, which prevents simultaneous database connections during the same HTTP request.

SELECT

Just write regular SQL query and use parameters instead of variables (:id is parameter in this case). The second method parameter is bind array. This is an array where the key represents the name of SQL query parameter (id in this case, without ":") and value for that key should be the value you want to replace that parameter inside SQL query:

$result = $db->select(
    "SELECT * FROM `as_user_details` WHERE `user_id` = :id",
    array ("id" => $userId)
);

If you don't have any parameter inside SQL query, just don't pass anything as a second method parameter, as follows:

$result = $db->select("SELECT * FROM `as_users`");

The result will always be an array!

If result should be only one database row or only one database column, you can access it like this:

//result of first query
$userDetails = $result[0];

If there will be multiple rows, you can iterate through them with simple foreach:

foreach($result as $user) {    
    echo $user['email'];    
    echo $user['username'];    
}

INSERT

In order to insert something into the database, insert method need 2 parameters.

The first parameter is table name and the second one is an array where keys represent names of database columns, and values represent what should be written into that database column.

So, if you want to insert a new user into your database, you need to write this:

$db->insert('as_users', array(
    "email" => $email,
    "username"  => $username,
    "password"  => $password,
    "confirmation_key" => $key,
    "register_date" => $date
));

UPDATE

Update method needs 4 parameters.

First one is database table that should be updated.
Second one is array where keys are names of columns that should be updated and values are new values for those columns.
Third parameter is SQL WHERE query part. Remember that you need to use sql parameters for every variable that you want to pass to the query to prevent SQL Injection.
And fourth parameter is bind array with key => value pair for every sql parameter you have added into sql query.

So, if you want to update user's password, and you have $userId for that user, you can do it like this:

$db->update(
    'as_users',
    array ("password" => $newPassword),
    "user_id = :id",
    array("id" => $user_id)
);

This is actually converted to

"UPDATE `as_users` SET `password` = '$newPassword' WHERE `user_id` = '$user_id'"

but we use PDO prepared statements to prevent SQL injection!

DELETE

In order to delete something from database, you need to pass 3 parameters to delete method:

First one is database table name from which you want to delete.
Second is SQL WHERE query part, using parameters.
And third is an array with key => value pair for every SQL parameter inside your WHERE query part.

So, if you want to delete all comments posted by user with specific $userId, you need to do the following

$db->delete(
    "as_comments",
    "posted_by = :id", 
    array("id" => $userId)
);