PHP download

PHP code example of model / query-builder

1. Go to this page and download the library: Download model/query-builder library. Choose the download type require.

2. Extract the ZIP file and open the index.php.

3. Add this code to the index.php.

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

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

model / query-builder example snippets


use Model\QueryBuilder\QueryBuilder;
use Model\DbParser\Parser;

// Initialize with a Parser instance
$parser = new Parser($pdo);
$qb = new QueryBuilder($parser);


// Insert a single row
$query = $qb->insert('users', [
	'name' => 'John Doe',
	'email' => '[email protected]',
	'age' => 30,
]);
// INSERT INTO `users`(`name`,`email`,`age`) VALUES('John Doe','[email protected]',30)


// Insert multiple rows
$query = $qb->insert('users', [
	['name' => 'John', 'email' => '[email protected]'],
	['name' => 'Jane', 'email' => '[email protected]'],
	['name' => 'Bob', 'email' => '[email protected]'],
]);


$query = $qb->insert('users', $data, [
	'replace' => true,          // Use REPLACE instead of INSERT
	'validate_data' => true,    // Validate data against schema (default: true)
	'cast_null' => false,       // Cast null values to appropriate defaults (default: false)
]);


// Insert empty row (all default values)
$query = $qb->insert('users', []);
// INSERT INTO `users`() VALUES()


// Update by ID
$query = $qb->update('users', 5, [
	'name' => 'Jane Doe',
	'age' => 31,
]);
// UPDATE `users` SET `users`.`name`='Jane Doe',`users`.`age`=31 WHERE `users`.`id`=5


$query = $qb->update('users', ['active' => 1], [
	'status' => 'verified',
]);


$query = $qb->update('users', ['role' => 'admin'], 
	['verified' => 1],
	[
		'alias' => 'u',
		'joins' => [
			'profiles' => [
				'on' => 'user_id',
				'fields' => [],
			],
		],
	]
);


$query = $qb->update('users', $where, $data, [
	'alias' => 'u',              // Table alias
	'joins' => [],               // Array of joins
	'operator' => 'AND',         // WHERE operator (AND/OR)
	'validate_where' => true,    // Validate WHERE clause
	'validate_data' => true,     // Validate data
	'cast_null' => false,        // Cast null values
]);


// Delete by ID
$query = $qb->delete('users', 5);
// DELETE `users` FROM `users` WHERE `users`.`id`=5


$query = $qb->delete('users', [
	'active' => 0,
	'created_at' => ['<', '2020-01-01'],
]);


$query = $qb->delete('users', ['verified' => 0], [
	'alias' => 'u',
	'joins' => [
		'profiles' => [
			'where' => ['completed' => 0],
		],
	],
]);


// Select all columns
$query = $qb->select('users');
// SELECT `users`.*,ST_AsText(`users`.`location`) AS `location` FROM `users`

// Select by ID
$query = $qb->select('users', 5);
// SELECT `users`.* FROM `users` WHERE `users`.`id`=5


$query = $qb->select('users', [], [
	'fields' => ['id', 'name', 'email'],
]);


$query = $qb->select('users', [], [
	'fields' => [
		'id' => 'user_id',
		'name' => 'full_name',
		'email',
	],
]);
// SELECT `users`.`id` AS `user_id`,`users`.`name` AS `full_name`,`users`.`email` FROM `users`


$query = $qb->select('users', [], [
	'fields' => ['COUNT(*) as total', 'MAX(age) as max_age'],
	'raw_fields' => true,
]);


$query = $qb->select('orders', [], [
	'fields' => ['user_id'],
	'count' => ['id' => 'total_orders'],
	'sum' => ['amount' => 'total_amount'],
	'avg' => ['amount' => 'avg_amount'],
	'min' => ['amount' => 'min_amount'],
	'max' => ['amount' => 'max_amount'],
	'count_distinct' => ['product_id' => 'unique_products'],
	'group_by' => ['user_id'],
]);


$query = $qb->select('orders', [], [
	'fields' => ['user_id'],
	'count' => ['id' => 'total'],
	'group_by' => ['user_id'],
	'having' => ['total > 5'],
]);


// Simple order
$query = $qb->select('users', [], [
	'order_by' => 'created_at DESC',
]);

// Multiple fields
$query = $qb->select('users', [], [
	'order_by' => [
		['name', 'ASC'],
		['created_at', 'DESC'],
	],
]);


$query = $qb->select('users', [], [
	'limit' => 10,
	'offset' => 20,
]);
// SELECT ... FROM `users` LIMIT 20,10


$query = $qb->select('users', [], [
	'joins' => [
		'profiles',  // Will auto-detect FK relationship
	],
]);


$query = $qb->select('users', [], [
	'joins' => [
		'profiles' => ['bio', 'avatar'],
	],
]);


$query = $qb->select('users', [], [
	'joins' => [
		'profiles' => [
			'on' => ['id' => 'user_id'],
			'fields' => ['bio', 'avatar'],
		],
	],
]);


$query = $qb->select('users', [], [
	'alias' => 'u',
	'joins' => [
		[
			'table' => 'profiles',
			'alias' => 'p',
			'on' => ['u.id' => 'p.user_id'],
			'fields' => ['bio' => 'user_bio'],
		],
	],
]);


$query = $qb->select('users', [], [
	'joins' => [
		'profiles' => [
			'where' => ['verified' => 1],
			'fields' => ['bio'],
		],
	],
]);


$query = $qb->select('users', [], [
	'joins' => [
		[
			'table' => 'profiles',
			'type' => 'LEFT',  // INNER (default), LEFT, RIGHT, etc.
			'fields' => ['bio'],
		],
	],
]);


$query = $qb->select('users', $where, [
	'alias' => null,              // Table alias
	'joins' => [],                // Array of joins
	'fields' => null,             // Fields to select (null = all)
	'min' => [],                  // MIN aggregations
	'max' => [],                  // MAX aggregations
	'sum' => [],                  // SUM aggregations
	'avg' => [],                  // AVG aggregations
	'count' => [],                // COUNT aggregations
	'count_distinct' => [],       // COUNT DISTINCT aggregations
	'raw_fields' => false,        // Use raw field names
	'group_by' => null,           // GROUP BY clause
	'having' => [],               // HAVING clause
	'order_by' => null,           // ORDER BY clause
	'limit' => null,              // LIMIT
	'offset' => null,             // OFFSET
	'operator' => 'AND',          // WHERE operator
	'validate_where' => true,     // Validate WHERE clause
]);


// Key-value pairs (uses = operator)
$where = [
	'active' => 1,
	'status' => 'verified',
];

// Specify by ID
$where = 5;  // Equivalent to ['id' => 5]


$where = [
	'age' => ['>', 18],
	'created_at' => ['<', '2023-01-01'],
	'status' => ['!=', 'deleted'],
	'rating' => ['>=', 4.5],
];


$where = [
	'deleted_at' => null,  // IS NULL
	'email' => ['!=', null],  // IS NOT NULL
];


$where = [
	'age' => ['BETWEEN', [18, 65]],
	'created_at' => ['BETWEEN', ['2023-01-01', '2023-12-31']],
];


$where = [
	'status' => ['IN', ['active', 'pending', 'verified']],
	'role' => ['NOT IN', ['banned', 'deleted']],
];

// Empty array handling
$where = [
	'status' => ['IN', []],  // Results in (1=2) - impossible condition
	'status' => ['NOT IN', []],  // Ignored
];


$where = [
	'name' => ['LIKE', '%John%'],
	'email' => ['LIKE', '%@gmail.com'],
];


// Simple match
$where = [
	'description' => ['MATCH', 'search terms'],
];

// Match with mode
$where = [
	'description' => ['MATCH BOOLEAN', '+


$where = [
	'active' => 1,
	'verified' => 1,
	'age' => ['>', 18],
];
// WHERE active=1 AND verified=1 AND age>18


$where = [
	'OR' => [
		'status' => 'active',
		'status' => 'pending',
	],
];

// Alternative syntax
$where = [
	['OR', [
		'status' => 'active',
		'status' => 'pending',
	]],
];


$where = [
	'active' => 1,
	'OR' => [
		'role' => 'admin',
		'AND' => [
			'role' => 'moderator',
			'verified' => 1,
		],
	],
];
// WHERE active=1 AND (role='admin' OR (role='moderator' AND verified=1))


$where = [
	'AND' => [
		'active' => 1,
		'OR' => [
			'status' => 'premium',
			'AND' => [
				'status' => 'free',
				'credits' => ['>', 0],
			],
		],
	],
];


$where = [
	[
		'sub' => [
			'role' => 'admin',
			'verified' => 1,
		],
		'operator' => 'OR',
	],
];


// Two-element array: [column, value]
$where = [
	['status', 'active'],
];

// Three-element array: [column, operator, value]
$where = [
	['age', '>', 18],
	['created_at', '<', '2023-01-01'],
];


$where = [
	'YEAR(created_at) = 2023',
	'MONTH(updated_at) = ' . date('m'),
];


$where = [
	'users.active' => 1,
	'profiles.verified' => 1,
];


$query = $qb->unionSelect([
	[
		'table' => 'users',
		'where' => ['active' => 1],
		'options' => ['fields' => ['id', 'name', 'email']],
	],
	[
		'table' => 'archived_users',
		'where' => ['restored' => 1],
		'options' => ['fields' => ['id', 'name', 'email']],
	],
], [
	'order_by' => 'name ASC',
	'limit' => 100,
]);


// Insert with POINT
$qb->insert('locations', [
	'name' => 'Office',
	'coordinates' => ['lat' => 40.7128, 'lng' => -74.0060],
]);

// Query with POINT
$qb->select('locations', [
	'coordinates' => ['lat' => 40.7128, 'lng' => -74.0060],
]);


// Insert with JSON
$qb->insert('settings', [
	'config' => ['theme' => 'dark', 'language' => 'en'],
]);


$qb->insert('events', [
	'name' => 'Conference',
	'start_date' => new DateTime('2023-06-01'),
]);


$whereString = $qb->buildQueryString(['active' => 1, 'role' => 'admin'], [
	'table' => 'users',
	'operator' => 'AND',
	'validate' => true,
]);


$parsed = $qb->parseColumn('name', 'users');
// `users`.`name`


$parsed = $qb->parseValue('John', 'varchar');
// 'John'

$parsed = $qb->parseValue(null);
// NULL


$fields = $qb->getFieldsInvolvedInWhere([
	'name' => 'John',
	'age' => ['>', 18],
	'OR' => [
		'status' => 'active',
		'verified' => 1,
	],
]);
// Returns: ['name', 'age', 'status', 'verified']


// Disable validation for specific operations
$qb->insert('users', $data, ['validate_data' => false]);
$qb->update('users', $where, $data, ['validate_where' => false, 'validate_data' => false]);
$qb->select('users', $where, ['validate_where' => false]);


$qb->insert('users', [
	'name' => null,  // Will be cast to '' for varchar
	'age' => null,   // Will be cast to 0 for int
], ['cast_null' => true]);


try {
	$query = $qb->select('users', ['nonexistent_column' => 1]);
} catch (\Exception $e) {
	// Handle error: Column "nonexistent_column" does not exist in table "users"
}


$page = 1;
$perPage = 20;

$query = $qb->select('users', ['active' => 1], [
	'order_by' => 'created_at DESC',
	'limit' => $perPage,
	'offset' => ($page - 1) * $perPage,
]);


$query = $qb->select('products', [
	'category_id' => ['IN', [1, 2, 3]],
	'price' => ['BETWEEN', [10, 100]],
	'active' => 1,
	'OR' => [
		'name' => ['LIKE', '%' . $searchTerm . '%'],
		'description' => ['LIKE', '%' . $searchTerm . '%'],
	],
], [
	'order_by' => 'created_at DESC',
	'limit' => 50,
]);


$query = $qb->select('orders', ['orders.status' => 'completed'], [
	'alias' => 'o',
	'fields' => [
		'id',
		'total',
		'created_at',
	],
	'joins' => [
		[
			'table' => 'users',
			'alias' => 'u',
			'on' => ['o.user_id' => 'u.id'],
			'fields' => [
				'name' => 'customer_name',
				'email' => 'customer_email',
			],
		],
		[
			'table' => 'order_items',
			'alias' => 'oi',
			'type' => 'LEFT',
			'on' => ['o.id' => 'oi.order_id'],
			'fields' => [],
		],
	],
	'count' => ['oi.id' => 'total_items'],
	'sum' => ['oi.quantity' => 'total_quantity'],
	'group_by' => ['o.id'],
	'order_by' => [['o.created_at', 'DESC']],
]);


$query = $qb->update('users',
	[
		'last_login' => ['<', date('Y-m-d', strtotime('-30 days'))],
		'status' => 'active',
	],
	[
		'status' => 'inactive',
		'updated_at' => date('Y-m-d H:i:s'),
	]
);