PHP database class
Switch branches/tags
Nothing to show
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
_tests
LICENSE
README.md
cl_database.php

README.md

ChristopherL Database Class

The cl_database() class endeavors to make it easy to add database support to PHP scripts for a variety of needs by abstracting query construction.

  • Supports MySQL/MariaDB, PostgreSQL, and SQL Server
  • Uses prepared statements to prevent SQL injection
  • Logs all queries for reference
  • Automatically applies table prefix to statements
  • Works in PHP 5.6-7.0

Examples

cl_database class supports standard database query actions with a simplified array syntax.

Connecting to server:

$db = new \ChristopherL\Database([
    'server' => 'mysql',
    'host' => '127.0.0.1',
    'username' => 'dbuser,
    'password' => '123456,
    'database' => 'mydb',
    'port' => 3306,
    'prefix' => 'tbl_'
]);

Querying connected server:

Selecting data:

$db->select(
    'my_table',
    [
        'my_table' => ['string_column'],
        'my_other_table' => ['string_column{my_alias}']
    ],
    [
        'AND' => [
            'my_table' => [
                'date_column[>=]' => date('Y-m-d'),
            ],
            'my_other_table' => [
                'date_column[>=]' => date('Y-m-d')
            ]
        ]
    ],
    [
        '[>]my_other_table' => ['fk_column', 'k_column']
    ]
);

Resulting query

SELECT  my_table.string_column, my_other_table.string_column AS my_alias 
FROM my_table 
	LEFT JOIN my_other_table ON (my_table.fk_column = my_other_table.k_column)  
WHERE my_table.date_column >= '2017-05-03'
	AND my_other_table.date_column >= '2017-05-03'

Inserting data:

$db->insert(
    'my_table',
    [
        [
            'number_column' => 500,
            'string_column' => 'some text',
            'boolean_column' => 1,
            'date_column' => date('Y-m-d'),
            'time_column' => date('H:i:s')
        ],
        [...]
    ]
);

Resulting query

INSERT INTO my_table(number_column, string_column, boolean_column, date_column, time_column) 
VALUES(500, 'some text', 1, '2017-05-03', '01:49:31')

Multiple records can be inserted by including multiple column => value arrays.

Updating data:

$db->update(
    'my_table',
    [
        'string_column' => 'new value',
    ],
    [
        'AND' => [
            'my_table' => [
                'date_column[>]' => date('Y-m-d'),
                'boolean_column[=]' => 1
            ]
        ]
    ]
);

Resulting query

UPDATE my_table 
SET string_column = 'new value'
WHERE my_table.date_column >= '2017-05-03' AND my_table.boolean_column = 1;

Deleting data:

$db->delete(
    'my_table',
    [
        'AND' => [
            'my_table' => [
                'date_column[>]' => date('Y-m-d'),
                'boolean_column[=]' => 1
            ]
        ]
    ]
);

Resulting query

DELETE FROM my_table WHERE my_table.date_column >= '2017-05-03' AND my_table.boolean_column = 1;

More Complex Queries

Advanced joins, where conditions, and aggregate methods are also supported.

$hcdb->select(
    'locations',
    [
        'locations' => ['PkID', 'Name', 'Address', 'Address2', 'City', 'State', 'Country','Zip', 'Lat', 'Lon', 'URL', 'Phone'],
        'events' => ['LocID[count]', 'StartDate[min]']
    ],
    [
        'AND' => [
            'locations' => [
                'Lon[!null]' => null,
                'Lat[!=]' => '',
                'Lon[!=]' => '',
                'IsActive[=]' => '1',
            ],
            'events' => [
                'LocID[>]' => '0',
                'IsActive[=]' => '1',
                'IsApproved[=]' => '1',
                'PkID[!null]' => null,
                'StartDate[>=]' => '2014-01-01',
            ]
        ],
        'GROUP' => [
            'locations' => ['PkID']
        ],
        'HAVING' => [
            'CountLocID[>]' => '0'
        ],
        'ORDER' => [
            'locations.Name'
        ]
    ],
    [
        '[>]events' => ['PkID', 'LocID']
    ]
);

Resulting query

SELECT hc_locations.PkID, hc_locations.Name, hc_locations.Address, hc_locations.Address2, hc_locations.City, hc_locations.State, hc_locations.Country, hc_locations.Zip, hc_locations.Lat, hc_locations.Lon, hc_locations.URL, hc_locations.Phone, COUNT(hc_events.LocID), MIN(hc_events.StartDate) 
FROM hc_locations 
	LEFT JOIN hc_events ON (hc_locations.PkID = hc_events.LocID) 
WHERE hc_locations.Lon IS NOT NULL
	AND  hc_locations.Lat != ''  
	AND  hc_locations.Lon != ''  
	AND  hc_locations.IsActive = 1  
	AND  hc_events.LocID > 0  
	AND  hc_events.IsActive = 1  
	AND  hc_events.IsApproved = 1  
	AND  hc_events.PkID IS NOT NULL  
	AND  hc_events.StartDate >= 2014-01-01  
GROUP BY hc_locations.PkID
HAVING COUNT(hc_events.LocID) > 0
ORDER BY hc_locations.Name

Thank Yous

Thanks to Medoo for the syntax inspiration which I have mimicked (nearly identically).

License

cl_database is made available under the LGPL.