Database Queries


Database Queries

Advanced Security comes with a simple databae 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 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). Second method parameter is bind array. This is an array where key represent name of SQL query parameter (id in this case, without ":") and value for that key should be 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 second method parameter, as following:

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

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 database, insert method need 2 parameters.

First parameter is table name and second one is 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 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.

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:

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