aslamhus / db
A class for handling mysql database
Requires
- vlucas/phpdotenv: ^5.4
README
A Database class for mysqli prepared statements. Connect to your database and easily prepare statements, bind variables and execute queries. Currently Has methods for MYSQL select, insert, update and delete.
Install
composer require aslamhus/db
Dependencies
- Composer
- composer package "vlucas/phpdotenv"
- MYSQL database with access privileges
- PHP
Getting started
Configuring your database
DB configures your database settings using environment variables. To set those up you'll need to install vlucas/phpdotenv
and create a .env file with your database name, host, username and password.
1. Install
If you have cloned the repo, install all dependencies.
composer install
If you are installing @aslamhus/db
as a package,
simply run
composer require @aslamhus/db
You should now have vlucas/phpdotenv
in your vendor directory.
More information on vlucas/dotenv
2. Create .env file in your project root directory with the following variables
// sets the development mode (possible values, "DEV" or "PRODUCTION") DEV = "DEV" // your db hostname DB_HOST = "localhost" // your db name DB_NAME = "db_name" // your db user name DB_USER = "db_user" // your db password DB_PASS = "db_password" // your local db password DB_LUSER = "local_db_user" // your local db username DB_LNAME = "local_db_password"
3. Load your environment variables
create a config.php file with the following:
Make sure you specify your path to vendor/autload and your .env file
// require composer autoload require_once '../vendor/autoload.php'; // path to your .env file $dotenv = Dotenv\Dotenv::createImmutable(dirname(__DIR__), '.env'); // load the variables $dotenv->load() ; // ensure the correct env variables are loaded $dotenv->required(['DEV', 'DB_HOST', 'DB_NAME', 'DB_USER', 'DB_PASS', 'DB_LUSER', 'DB_LNAME'])->notEmpty(); $dotenv->required('DEV')->allowedValues(['DEV', 'PRODUCTION']);
4. Connect to the database
Require your config.php file and then instantiate the DB class.
require_once 'config.php' use Database\DB; $db = new DB();
That's it! You should now be connected to your database. You can begin making queries.
Optional: Allowlist for table names and columns
If you would like to add an extra layer of security, you can create an allow list of valid table names and valid column names in your mysql queries.
Create two text files in the src directory, '.validcolumns' and '.validtables'
Separate names of valid tables and valid columns by line breaks.
DB
will then only perform queries on valid columns or tables.
If the files are left blank or do not exist, all tables and all columns will be allowed.
Testing
To see DB
in action you can run test/index.php
in a local environment with mysql and php installed.
Before running these examples you'll need to set up a test database.
- Login to your local mysql with admin privileges
- create a test database/test table by executing test/db/schema.sql
- add values to your test database by executing seeds.sql
Now you can run test/index.php in your local environment.
Examples
select
select
takes two parameters with an optional third. The first parameter is the columns to select, the second is the tablename and the third is an optional array of conditions.
Select everything
$result = $db->select('*', 'tablename'); print_r($result);
Select with conditions
In this example we select everything from the table 'users' where a the username has a value of the variable $username.
$username = 'bob sacamano'; $result = $db->select('*','users', [ 'username = ?' => $username ] ); print_r($result);
... more examples to come!
Author
aslamhus