Road map:
- calculate query hashCode independ of insertion order. For this change array to splheap
Index of README
- Simple SELECT query
- Model
- Query Methos
- Examples
- Improvements
$model = \Simple\Model(['table'=>'superA', 'alias'=>'A']);
$query = (new \Simple\Query($model))
->where($model->field('username'), 'superUser');
echo $query->sqlSelect();
// SELECT A.* FROM superA AS A WHERE A.username = (?)
// use 'bindParameters' propriety to bind parameters in your database statement
$query->bindParameters
// ['superUser']
// more real example
$supossed_mysql_stmt = $con->prepare($query->sqlSelect());
// you can bind_param this way
foreach ($query->bindParameters as $value) {
$supossed_mysql_stmt->bind_param(
$query->type($value), // return 'i', 'd' or 's'
$value
);
}
...
Using SQL Builder you can:
- increase security, avoid sql injection.
- create powerful query without specific order.
- use bind values.
- create easy subquery conditions using query object.
- apply patterns to interprete request and build personal queries.
- not dependent of ORMs.
Model allow to separate table structures to avoid conflicts when you want to create more complex queries.
$model = new \Simple\Model([
'table' => 'nameOfTable',
'alias' => 'shortName',
'pk' => 'id' ## default value
]);
Primary key of model(table)
#example
$model = new \Simple\Model([
'table'=>'superA',
'alias'=>'A'
]);
echo $model->pk();
# A.id
How key from model $modelB are represented inside of Model like a foreing key. Useful in joins
#example
$modelA = new \Simple\Model([
'table'=>'superA',
'alias'=>'A'
]);
$modelB = new \Simple\Model([
'table'=>'megaB',
'alias'=>'B'
]);
echo $modelA->fk($modelB);
# A.idMegaB
Useful method return anti-collision field name
$modelA = new \Simple\Model([
'table'=>'superA',
'alias'=>'A'
]);
echo $modelA->field('name');
# A.name
Return table name
return alias table
namespace My\Model;
class User extends \Simple\Model {
protected $table = 'user';
protected $alias = 'us';
}
$userModel = new \My\Model\User();
$query = new \Simple\Query($userModel);
$query->where($userModel->field('username'), $usernameParameter);
$stmt = $mysql_con->prepare($query->sqlSelect());
....
# example A
$userModel = new \Simple\Model(['table'=>'users']);
$query = new \Simple\Query($userModel);
$query->where($userModel->field('username'), $value);
$query->where($userModel->field('password'), $hashPass);
# example B
$userModel = new \Simple\Model(['table'=>'users']);
$query = new \Simple\Query($userModel);
$query->where($userModel->field('name'), 'John%', 'LIKE');
# OR condition
$query->where($userModel->field('surename'), 'John%', 'LIKE', 'OR');
...
# query equal method don't use bind parameter. equal method inject value inside of query. and work in the same way of where
$query->equal($model->field('status'), 1);
# IS NULL
$query->where($model->field('age'), 'NULL', 'IS');
# IS NOT NULL
$query->where($model->field('age'), 'NULL', 'IS NOT');
$modelUser = new \Simple\Model(['table'=>'user']);
$modelOrder = new \Simple\Model(['table'=>'order']);
// get all user are active customer in holiday month
$queryOrder = new \Simple\Query($modelOrder);
$queryOrder->field($modelOrder->fk($modelUser))
->where('createAt BETWEEN (?) AND (?)', ['2015-12-01','2015-12-31'], 'RAW')
->group($modelOrder->fk($modelUser));
$query = new \Simple\Query($modelUser);
$query->where($modelUser->field($modelUser->pk()), $queryOrder, 'IN')
->order($modelUser->field('name'))
->page(1, 20);
+'SELECT user.* FROM user WHERE user.id IN (SELECT order.idUser FROM order WHERE createAt BETWEEN (?) AND (?) GROUP BY order.idUser) ORDER BY user.name ASC LIMIT 0, 20'
Add condition. This apply to 'having' method
$query
->where($fieldName, $valueToBind, $function = '=', $operator = 'AND')
->where(...);
Support conditions functions:
'=','>','<','<=','>='
'IS'
'IS NO'
'IN'
'NOT IN'
'RAW' (not apply function condition, you should provide complete condition)
'=','>','<','<=','>=', '!='
$query->where('field', $value, '!=');
'IN' or 'NOT IN'
$query->where('field', array($value1, $value2), 'IN');
'RAW' (not apply function condition, you should provide complete condition) RAW function is a powerful function condition.
$query->where('name = (?) AND age = (?) AND actice=1', [$value1, $value2], 'RAW');
equal method add conditions that is not parsed or encoded by query. This is a danger feature but necessary when used to add keys tables in joins conditions.
$queryJoin = new \Simple\Query($modelB); $queryJoin->equal($modelA->pk(), $modelB->fk($modelA));
$query->group($model->pk());
$query->group($model->field('name'));
...
$query->order($model->field('name'), 'DESC');
// accept DESC or ASC
Joins is another complex structure that \Simple\Query help you made simple and powerful. With \Simple\Query you can join models and queries objects. Join table work with keys co-related between tables. Generally this key are someone primary key and your foreign key (that represent primary key some table)
\Simple\Model have two methods that help you organized keys pk() and fk().
#example A
$modelA = new ModelA();
$modelB = new ModelB();
$query = new \Simple\Query($modelA);
$query->join('left', $modelB);
#example A.2. Allow join queries
$modelA = new ModelA();
$modelB = new ModelB();
$query = new \Simple\Query($modelA);
$query->join('left',
(new \Simple\Query($modelB))
->equal($modelA->pk(), $modelA->fk($modelB))
->where('name', $paramName)
);
# calc limit and offset based on page and perPage values
$query->page($page, $perPage);
# or you can use limit and offset directly
$query->limit(0, 10);
$query->sqlSelect();
$query->sqlCountSelect();
$query->sqlUpdate();
$query->sqlInsert();
$query->sqlDelete();
When you work with values Simple Query Build allways work with SQL statement.
$stmt = $con->prepare($query->sqlSelect()); $query->bind($stmt)->execute()->get_result();
Or you can bind your values this way:
# more real example
$supossed_mysql_stmt = $con->prepare($query->sqlSelect());
# you can bind_param this way
foreach ($query->bindParameters as $value) {
$supossed_mysql_stmt->bind_param(
$query->type($value),#return 'i', 'd' or 's'
$value
);
}
- Create index of fields used in WHERE, ORDER and JOINs.
- Try agroup your condition fields inside of compose index.
- Use correct type length to store your data. Only use big store datatype when really required!
- Benchmark your queries and index.
- Use EXPLAIN to discovery how and what you need improve.
- Try discovery if are more fast paginate in PHP side. Benchmark this choice!
- Even COUNT queries, benchmark if are more fast calculate PHP side.
- Disable log queries in production.