PHP code example of benclerc / datamanagement

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

benclerc / datamanagement example snippets

// "SELECT * FROM BOOKS;"
$db->debug()->select('BOOKS');
// "SELECT * FROM BOOKS WHERE BOOKS.books_id = :whereBOOKSbooks_id;"
$db->debug()->select('BOOKS', NULL, NULL, ['BOOKS'=>['books_id'=>42]]);
// "SELECT * FROM BOOKS INNER JOIN AUTHORS ON BOOKS.books_refauthor = AUTHORS.authors_id ORDER BY books_name ASC;"
$db->debug()->select('BOOKS', ['books_name'], ['AUTHORS'=>['INNER', 'books_refauthor', 'authors_id']]);
// "INSERT INTO BOOKS (books_name, books_refauthor) VALUES (:books_name, :books_refauthor);"
$db->debug()->insert('BOOKS', ['books_name'=>htmlentities('Super book'), 'books_refauthor'=>42]);
// "UPDATE BOOKS SET books_name=:books_name WHERE BOOKS.books_id = :whereBOOKSbooks_id;"
$db->debug()->update('BOOKS', ['books_name'=>htmlentities('Super book 2')], ['books_id'=>42]);
// "DELETE FROM BOOKS WHERE BOOKS.books_id = :whereBOOKSbooks_id;"
$db->debug()->delete('BOOKS', ['books_id'=>42]);
// "SELECT COUNT(books_id) FROM BOOKS WHERE BOOKS.books_isavailable IS NULL;"
$db->debug()->count('BOOKS', 'books_id', ['BOOKS'=>['books_isavailable'=>TRUE]]);
// "SELECT SUM(books_pages) FROM BOOKS WHERE BOOKS.books_isavailable IS NULL;"
$db->debug()->sum('BOOKS', 'books_pages', ['BOOKS'=>['books_isavailable'=>TRUE]]);

// Get all books
$res = $db->select('BOOKS');
// Get one book, id = 42
$res = $db->select('BOOKS', NULL, NULL, ['BOOKS'=>['books_id'=>42]])[0]; // Note the NULL values because we do not want order or join. And also note [0] because we know we have only one result so we can select it directly.
// Get all books with their authors, results ordered on the book name from A to Z
$res = $db->select('BOOKS', ['books_name'], ['AUTHORS'=>['INNER', 'books_refauthor', 'authors_id']]);
// Get all books with the reference in the list + their authors, results ordered on the book name from A to Z and author name from Z to A, limit to 10 results with an offset of 10 (page 2)
$referenceList = [37483, 27949, 49303, 20438];
$res = $db->select('BOOKS', ['books_name', 'authors_name'=>'DESC'], ['AUTHORS'=>['INNER', 'books_refauthor', 'authors_id']], ['BOOKS'=>['books_reference'=>$referenceList]], 10, 10);
// Get all books with their subcategories and categories
$res = $db->select('BOOKS', NULL, ['SUBCATEGORIES'=>['INNER', 'books_refsubcategory', 'subcategories_id'], 'CATEGORIES'=>['INNER', 'subcategories_refcategory', 'categories_id', 'SUBCATEGORIES']]); // Note the fourth element in the element 'CATEGORIES' in the join array.

// For request with subqueries for example 
$res = $db->customSelect('SELECT * FROM BOOKS WHERE books_id IN (SELECT orders_refbook FROM ORDERS WHERE orders_refclient = :id);', ['id'=>42]);
// Or to filter using an other operator than =
$res = $db->customSelect('SELECT * FROM BOOKS WHERE books_release > 2000-01-01');

// Simple insert, without transaction
if ($db->insert('BOOKS', ['books_name'=>htmlentities('Super book'), 'books_refauthor'=>42])['raw']) {
	echo('Success');
} else {
	echo('Error');
}

// Simple insert, with transaction
$db->beginTransaction();
$resAuthor = $db->insert('AUTHORS', ['authors_name'=>htmlentities('Super Author')]);
if ($resAuthor['raw']) {
	if ($db->insert('BOOKS', ['books_name'=>htmlentities('Super book'), 'books_refauthor'=>$resAuthor['lastInsertId']])['raw']) {
		$db->commit();
		echo('Success');
	} else {
		$db->rollback();
		echo('Error when inserting book.');
	}
} else {
	$db->rollback();
	echo('Error when inserting author.');
}

// For insert using SQL functions for example
$res = $db->customInsert('INSERT INTO BOOKS (books_name, books_release) VALUES (?, NOW());', [htmlentities('Super book')]);

// Simple update, without transaction
if ($db->update('BOOKS', ['books_name'=>htmlentities('Super book 2')], ['books_id'=>42])) {
	echo('Success');
} else {
	echo('Error');
}

// Simple update, with transaction
$db->beginTransaction();
$resBook1 = $db->update('BOOKS', ['books_name'=>htmlentities('Super book 1')], ['books_id'=>41]);
if ($resBook1) {
	if ($db->update('BOOKS', ['books_name'=>htmlentities('Super book 2')], ['books_id'=>42])) {
		$db->commit();
		echo('Success');
	} else {
		$db->rollback();
		echo('Error when updating book 2.');
	}
} else {
	$db->rollback();
	echo('Error when updating book 1.');
}

// Simple delete, without transaction
if ($db->delete('BOOKS', ['books_id'=>42])) {
	echo('Success');
} else {
	echo('Error');
}

// Simple delete, with transaction
$db->beginTransaction();
$resBook1 = $db->delete('BOOKS', ['books_id'=>41]);
if ($resBook1) {
	if ($db->delete('BOOKS', ['books_id'=>42])) {
		$db->commit();
		echo('Success');
	} else {
		$db->rollback();
		echo('Error when deleting book 2.');
	}
} else {
	$db->rollback();
	echo('Error when deleting book 1.');
}

// Simple count
$res = $db->count('BOOKS', 'books_id', ['books_isavailable'=>TRUE]);
// Count with join
$res = $db->count('BOOKS', 'authors_id', ['books_isavailable'=>TRUE], ['AUTHORS'=>['INNER', 'books_refauthor', 'authors_id']]); // Return the number of authors who have at least one book available.

// Simple sum
$res = $db->sum('BOOKS', 'books_pages', ['books_isavailable'=>TRUE]); // Return the total number of pages of available books.

// For uncommon SQL queries
$res = $db->customSQL('UPDATE BOOKS SET books_isold IS TRUE WHERE books_release < 2000-01-01');