PHP Classes

Simple PHP Database Wrapper Class: Run common SQL queries on a given database table

Recommend this page to a friend!
  Info   View files Documentation   View files View files (7)   DownloadInstall with Composer Download .zip   Reputation   Support forum   Blog    
Last Updated Ratings Unique User Downloads Download Rankings
2023-11-01 (2 months ago) RSS 2.0 feedNot yet rated by the usersTotal: 49 All time: 10,615 This week: 118Up
Version License PHP version Categories
simple-php-db-wrappe 1.0GNU General Publi...7.4Databases, PHP 7
Description 

Author

This package can run common SQL queries on a given database table.

It can connect to a database server using PDO with connection parameters read from environment variables.

The package executes SQL queries on a table using parameter values. Currently, it can:

- Get all values from a table column that match a given condition

- Get all values from the table that match a given condition

- Get all values from the table sorted by a random order

- Get all values from the table that match a condition using prepared queries to define the condition parameter values

- Insert records in the table using the given column values

- Delete records from the table that match a given condition

- Update the records from the table that match a given condition

Picture of georgios spanos
  Performance   Level  
Name: georgios spanos <contact>
Classes: 1 package by
Country: Greece Greece

Instructions

If you use PHP composer you can import this package into your project using the command:

composer require spaniakos/simple-php-db-wrapper

DO NOT FORGET TO ADD A .env to your project OR add this to your .env

DEBUG=false DB_HOST="localhost" DB_USER="root" DB_PASS="toor" DB_NAME="test_database" DB_PORT=3306 DB_CHARSET="utf8mb4" DB_COLLATION="utf8mb4_general_ci" LOG_PATH="path of log file with proper permissions" Below are the test config for the tests:

DB_CHARSET="utf8mb4" DB_COLLATION="utf8mb4_general_ci" Please do change the above to your desired valued

Then on your script you need to require vendor autoload IF you havent already somewhere: require_once 'vendor/autoload.php';

and then you can use the library by using the above line

use Spaniakos\SimplePhpDbWrapper;

Sample usage on a class:

<?php declare(strict_types=1);

require_once 'vendor/autoload.php'; use Spaniakos\SimplePhpDbWrapper;

class MyClass {

protected $db;

/
 * Set up the test environment before each test.
 */
public function __construct() {
    // Create an instance of the Database class before each test
    $this->db = new SimplePhpDbWrapper(true);
}

public function testGetColFromTable() {
    $result = $this->db->GetColFromTable('users', 'username', 'id > 0', 'username', '5');
}

} Requirements:

php >= 7.4 composer It uses monolog for logging of error messages from the PDO sql

Dependencies:

monolog/monolog: ^3.4 symfony/dotenv: ^6.3 Dev Dependencies:

friendsofphp/php-cs-fixer: ^3.35 phpunit/phpunit: ^10.4.1 install:

composer install

Usage:

Change the credentials inside .env You can copy the env.sample to .env Require the class from a php file

DB_HOST=your_database_host DB_USER=your_database_user DB_PASS=your_database_password DB_NAME=your_database_name DB_PORT=3306 DB_CHARSET=utf8mb4 DB_COLLATION=utf8mb4_0900_ai_ci LOG_PATH=path/to/your/logfile.log How to include it in a file:

require_once('database.class.php'); $db = new Database(); How to call the function (sample):

Raw query

$query = 'select * from table; $rs = $db->Query($query); update sample: UpdateTable($table,$set,$where= ''){

$db->UpdateTable("table","column='data',column2=1","column3='where' and column4=2"); GetAllFromTable($table,$where = '',$order_col = 'ID',$order = 'ASC', $limit = '') {

$rs = $db->GetAllFromTable("table","username='user1' and password='password'",'id','asc', 1000) { Iterate the results:

foreach($rs as $data){

//do

} Future work:

  • Change to Depencancy injection from env
  • Change unit tests from mysql to mysql-lite for automated testing
  • Write better examples
  • Stracture the function to use arrays instaid of plain string in order to have better and simpler understanding of the Injecttion methods
  • Consider Chainable methods

Documentation

This is a very simple Database wrapper for PHP and mysql to get you started Simple and lightwait, makes it ideal for quick projects.

You can Import via

composer require spaniakos/simple-php-db-wrapper

DO NOT FORGET TO ADD A .env to your project OR add this to your .env

DEBUG=false
DB_HOST="localhost"
DB_USER="root"
DB_PASS="toor"
DB_NAME="test_database"
DB_PORT=3306
DB_CHARSET="utf8mb4"
DB_COLLATION="utf8mb4_general_ci"
LOG_PATH="path of log file with proper permissions"

Below are the test config for the tests:

DB_CHARSET="utf8mb4"
DB_COLLATION="utf8mb4_general_ci"

Please do change the above to your desired valued

Then on your script you need to require vendor autoload IF you havent already somewhere: require_once 'vendor/autoload.php';

and then you can use the library by using the above line

use Spaniakos\SimplePhpDbWrapper;

Sample usage on a class:

<?php declare(strict_types=1);

require_once 'vendor/autoload.php';
use Spaniakos\SimplePhpDbWrapper;

class MyClass {
    protected $db;

    /
     * Set up the test environment before each test.
     */
    public function __construct() {
        // Create an instance of the Database class before each test
        $this->db = new SimplePhpDbWrapper(true);
    }

    public function testGetColFromTable() {
        $result = $this->db->GetColFromTable('users', 'username', 'id > 0', 'username', '5');
    }
}

Requirements:

php >= 7.4
composer

It uses monolog for logging of error messages from the PDO sql

Dependencies:

monolog/monolog: ^3.4
symfony/dotenv: ^6.3

Dev Dependencies:

friendsofphp/php-cs-fixer: ^3.35
phpunit/phpunit: ^10.4.1

install:

composer install

Usage:

Change the credentials inside .env You can copy the env.sample to .env Require the class from a php file

DB_HOST=your_database_host
DB_USER=your_database_user
DB_PASS=your_database_password
DB_NAME=your_database_name
DB_PORT=3306
DB_CHARSET=utf8mb4
DB_COLLATION=utf8mb4_0900_ai_ci
LOG_PATH=path/to/your/logfile.log

How to include it in a file:

require_once('database.class.php');
$db = new Database();

How to call the function (sample):

Raw query

$query = 'select * from table;
$rs = $db->Query($query);

update sample: UpdateTable($table,$set,$where= ''){

$db->UpdateTable("table","column='data',column2=1","column3='where' and column4=2");

GetAllFromTable($table,$where = '',$order_col = 'ID',$order = 'ASC', $limit = '') {

$rs = $db->GetAllFromTable("table","username='user1' and password='password'",'id','asc', 1000) {

Iterate the results:

foreach($rs as $data){
    //do
}

Future work:

* Change to Depencancy injection from env
* Change unit tests from mysql to mysql-lite for automated testing
* Write better examples
* Stracture the function to use arrays instaid of plain string in order to have better and simpler understanding of the Injecttion methods
* Consider Chainable methods

  Files folder image Files  
File Role Description
Files folder imagesrc (2 directories)
Accessible without login Plain text file composer.json Data Auxiliary data
Accessible without login Plain text file env.sample Data Auxiliary data
Accessible without login Plain text file LICENSE Lic. License text
Accessible without login Plain text file phpunit.xml Data Auxiliary data
Accessible without login Plain text file Readme.md Doc. Read me

  Files folder image Files  /  src  
File Role Description
Files folder imageSpaniakos (1 file)
Files folder imageTests (1 file)

  Files folder image Files  /  src  /  Spaniakos  
File Role Description
  Plain text file SimplePhpDbWrapper.php Class Class source

  Files folder image Files  /  src  /  Tests  
File Role Description
  Plain text file DatabaseTest.php Class Class source

 Version Control Unique User Downloads Download Rankings  
 100%
Total:49
This week:0
All time:10,615
This week:118Up