Coding a PHP 7 Framework #6 - Code Workshop

In part 6 of Coding a PHP Framework, we cover where how to use the /library/Lectric/lecPDO.class.php class.



Coding a PHP 7 Framework #6 - Code Workshop

1st October 2018 in   PHP Tutorials by Elliott Barratt

You can view the finished framework at any time by visiting the GitHub repo at https://github.com/erbarratt/lectric.

If you need more space to see the code, click "Hide Sidebar >" above the Article Categories box to the right ->

The basic premise of this class is to parameterise the process of common database functions (SELECT, UPDATE, INSERT, DELETE) and by doing so ensure security via prepared statements. 

Let's take the imaginary table `users`. It has the fields `name` (varchar), `age`(int) and `dob` (date).

Note: You should always surround working with this class in a try/catch control statement (see below).

Select

The basic (strict) select function selStrict() requires just one argument, the table, and can accept a variadic argument list of strings for various settings. With only one argument the function will return the whole table in an associative array where the rows are the child elements within, by 0 based index:

try{
	$queryObject = new lecPDO($DBH);
	$usersLoaded = $queryObject->selStrict('`users`');
} catch (\Exception $e){
	echo 'Oops, something when wrong: '.$e->getMessage();
}

---------------------

$usersLoaded: {
	0:{
		'name':'Elliott',
		'age':29,
		'dob': '1989-04-03'
	},
	1:{
		'name':'Lisa',
		'age':37,
		'dob': '1980-08-21'
	}
}

From now on we will omit the try/catch control statement to save space - but you shouldn't!

TABLED

But what if we want our rows nested into a table sub element?, well then apply the \Lectric\lecPDO::TABLED argument:

$usersLoaded = $queryObject->selStrict('`users`', \Lectric\lecPDO::TABLED);

---------------------

$usersLoaded: {
	0:{
		'users' : {
			'name':'Elliott',
			'age':29,
			'dob': '1989-04-03'
		}
	},
	1:{
		'users' : {
			'name':'Lisa',
			'age':37,
			'dob': '1980-08-21'
		}
	}
}

Having your results always be the child of a table index is useful when writing longer code where it's useful to know where the data has come from.

SINGLE Record Return

How about if we just want a single row from the table? Use \Lectric\lecPDO::SINGLE:

$userLoaded = $queryObject->selStrict('`users`', \Lectric\lecPDO::SINGLE);

---------------------

$userLoaded: {
	'name':'Elliott',
	'age':29,
	'dob': '1989-04-03'
}

Without the \Lectric\lecPDO::SINGLE argument, any results will be nested inside 0 based index element like in the first example. This is the same as setting \Lectric\lecPDO::MULTI.

ECHO the SQL Query

And if during development we want to echo out the query for debugging? \Lectric\lecPDO::ECHO

$usersLoaded = $queryObject->selStrict('`users`', \Lectric\lecPDO::ECHO);

Output: "SELECT * FROM `users`"

Selection Fields

In case we don't want the whole table row to be returned, we can limit the fields we are returned by passing an array of fields to setSelectFields():

$pdo->setSelectFields(['name','dob']);
$usersLoaded = $queryObject->selStrict('`users`', \Lectric\lecPDO::ECHO);

Output: "SELECT `name`,`dob` FROM `users`"

---------------------

$usersLoaded: {
	0:{
		'name':'Elliott',
		'dob': '1989-04-03'
	},
	1:{
		'name':'Lisa',
		'dob': '1980-08-21'
	}
}

Advanced Selection Functions

In some cases, you may want to run a native SQL function in your data selection. Just input the function call as one of the array elements in setSelectFields():

$pdo->setSelectFields(['name','COUNT(`age`) AS "age_count"']);
$usersLoaded = $queryObject->selStrict('`users`', \Lectric\lecPDO::ECHO);

Output: "SELECT `name` COUNT(`age`) AS "age_count" FROM `users`"

---------------------

$usersLoaded: {
	0:{
		'name':'Elliott',
		'age_count': '143'
	},
	1:{
		'name':'Lisa',
		'age_count': '143'
	}
}

Bear in mind, just as in the checkFunction() function we coded in the lecPDO class, the string calls to insert a function must:

  • Not contain a ';' character.
  • Have no white space at the start or end of the string
  • Start with the function call, i.e. 'FUNCTION(.....'
  • End with either a ')' or '"' character. The '"' character allows you to stipulate a temporary field name with 'AS'

A few valid examples:

'DAY(`date_field`)'
'SUM(`field`) AS "sum"'
'CIEL(`field`)'
'CONCAT(`field_1`, "-", `field_2`) AS "new_field"'

A few invalid examples:

'NOW(); DELETE *' //boo
'CIEL(`field`) AS new_field' //no " characters
' DAY(`date_field`) ' //white space at begining

WHERE Clause

If we want to select rows from the table using a WHERE clause, we can by doing the following:

$queryObject->setWhereFields(['name'=>'Lisa']);
$queryObject->setWhereOps('=');
$userLoaded = $queryObject->selStrict('`users`', \Lectric\lecPDO::SINGLE, \Lectric\lecPDO::ECHO);

Output: "SELECT * FROM `users` WHERE `name` = :name"

---------------------

$userLoaded: {
	'name':'Lisa',
	'age':37,
	'dob': '1980-08-21'
}

The two function calls setWhereFields() and setWhereOps() tell the class to construct a WHERE clause. Better yet, because all statements are prepared the string passed to setWhereFields() is automatically sanitised for SQL execution. The ECHO argument above shows us in the output what the query actually looks like. Note the :name handle, used by the statement preparation mechnism to insert the passed value at point of execution.

Compound WHERE Clause

Perhaps we need to be more specific in our selection. Imagine there are two records, both that have a name of "Lisa", however one has an age of 37 and one of 45. To add more fields to the where clause, you extend the array within setWhereFields() and the string within setWhereOps():

$queryObject->setWhereFields(['name'=>'Lisa', 'age'=>37]);
$queryObject->setWhereOps('==');
$userLoaded = $queryObject->selStrict('`users`', \Lectric\lecPDO::SINGLE, \Lectric\lecPDO::ECHO);

Output: "SELECT * FROM `users` WHERE `name` = :name AND `age` = :age"

---------------------

$userLoaded: {
	'name':'Lisa',
	'age':37,
	'dob': '1980-08-21'
}

WHERE OPS

There are many 'ops' available, not just '='. For the full list, see the getOp() function. For example, maybe we want to select all users whose age is less than or equal to 40?:

$queryObject->setWhereFields(['age'=>40]);
$queryObject->setWhereOps('L');
$usersLoaded = $queryObject->selStrict('`users`', \Lectric\lecPDO::MULTI, \Lectric\lecPDO::ECHO);

Output: "SELECT * FROM `users` WHERE `age` <= :age"

---------------------

$usersLoaded: {
	0:{
		'name':'Elliott',
		'age':29,
		'dob': '1989-04-03'
	},
	1:{
		'name':'Lisa',
		'age':37,
		'dob': '1980-08-21'
	}
}

WHERE Ranges

But what if we need to stipulate the same field more than once? Lets select every user between a date range, by putting the required fields into child arrays within the main setWhereFields() array argument:

	$queryObject->setWhereFields([
		['dob'=>'1979-12-32'],
		['dob'=>'1990-01-01']
	]);
	$queryObject->setWhereOps('><');
	$usersLoaded = $queryObject->selStrict('`users`', \Lectric\lecPDO::MULTI, \Lectric\lecPDO::ECHO);

Output: "SELECT * FROM `users` WHERE `dob` > :dob1 AND `dob` < :dob2"

---------------------

$usersLoaded: {
	0:{
		'name':'Elliott',
		'age':29,
		'dob': '1989-04-03'
	},
	1:{
		'name':'Lisa',
		'age':37,
		'dob': '1980-08-21'
	}
}

Complex SELECT Statements

If the query you need to create is more complicated than can be achieved using the strict function, there is a lax function selLax() that will accept a query, an array of bound parameters as well as the additional variadic arguments that selStrict() had:

$usersLoaded = $queryObject->selLax('SELECT * FROM `users` WHERE `dob` > :dob1 AND `dob` < :dob2', ['dob1'=>'1979-12-32', 'dob2'=>'1990-01-01'], \Lectric\lecPDO::MULTI);

---------------------

$usersLoaded: {
	0:{
		'name':'Elliott',
		'age':29,
		'dob': '1989-04-03'
	},
	1:{
		'name':'Lisa',
		'age':37,
		'dob': '1980-08-21'
	}
}

Further SQL Clauses

Further clauses of GROUP BY, ORDER BY and LIMIT can be set in a similar fashion using the appropriate functions (setGroupBy(), setOrderBy(), and setLimit()):

	$queryObject->setWhereFields([
		['dob'=>'1979-12-32'],
		['dob'=>'1990-01-01']
	]);
	$queryObject->setWhereOps('><');
	$queryObject->setOrderBy(['age'=>'ASC']);
	$queryObject->setGroupBy('age');
	$queryObject->setLimit([0,1]);
	$usersLoaded = $queryObject->selStrict('`users`', \Lectric\lecPDO::MULTI, \Lectric\lecPDO::ECHO);

Output: "SELECT * FROM `users` WHERE `dob` > :dob1 AND `dob` < :dob2 GROUP BY `age` ORDER BY `age` ASC LIMIT 0,1"

---------------------

$usersLoaded: {
	0:{
		'name':'Elliott',
		'age':29,
		'dob': '1989-04-03'
	}
}

setGroupBy() can accept an array or a string as it's argument, for example setGroupBy('age') or setGroupBy(['age','dob']). Likewise, the same is true of setOrderBy(), where if you pass it a single integer X, then the limit statement will always be LIMIT 0,X.

Update

Without configuring any of the private property settings, such as setWhereFields() and setWhereOps(), the updateStrict() function will update all rows, using the array set by setUpdateFields() which is a single layer key => value array, the key being the field selector:

try{
	$updateArray = ['name'=>'Kevin'];
	$pdo->setUpdateFields($updateArray);
	$pdo->updateStrict('users');
} catch (\Exception $e){
	echo 'Oops! Something went wrong: '.$e->getMessage();
}

//all rows `name` field set to 'Kevin'

Limiting the Update function

In most scenarios, you're going to want to set some sort of limitation to which rows of a given table are updated. You can do this using the aforementioned setWhereFields() and setWhereOps() functions:

$updateArray = ['name'=>'Kevin'];
$pdo->setUpdateFields($updateArray);
$pdo->setWhereFields(['name'=>'Elliott']);
$pdo->setWhereOps('=');
$pdo->updateStrict('users', \Lectric\lecPDO::ECHO);

---------------------

Output: "Update `users` SET `name` = :name WHERE `name` = :w_name;"

Insert

The insertStrict() function behaves just like the updateStrict() function except instead of setUpdateFields(), use setInsertFields():

$insertArray = ['name'=>'Kevin, 'age'=>20, 'dob'=>'1998-06-20'];
$pdo->setInsertFields($updateArray);
$id = $pdo->insertStrict('users', \Lectric\lecPDO::SQL_ECHO);

---------------------

Output: "INSERT INTO `users` (`name`,`age`,`dob`) VALUE (:name, :age, :dob);"

Delete

The deleStrict() function is probably the most dangerous of the strict functions, as without first using setWhereFields() and setWhereOps(), you could delete everything from a table!

$pdo->deleteStrict('users', \Lectric\lecPDO::SQL_ECHO);

---------------------

//delete all rows
Output: "DELETE FROM `users`;"

---------------------

$pdo->setWhereFields(['name'=>'Kevin']);
$pdo->setWhereOps('=');
$pdo->deleteStrict('users', \Lectric\lecPDO::SQL_ECHO);

---------------------

//deletes only Kevin. Suck it Kevin.
Output: "DELETE FROM `users` WHERE `name` = :name;"

Query Lax

You may have noticed in the previous article that both the insertStrict() and updateStrict() functions use the queryLax() function to actually execute the query. You can use it yourself, but be careful and sanitise, sanitise, sanitise! The first argument is the query $sql, and the second argument is the pass array that will be used to bind to the prepared statement on execution. Note, this function does not return anything.

$pdo->queryLax('DELETE FROM `users` WHERE `name` = :name', ['name'=>'Kevin']);

Conclusion

With that, we have everything we need to start building applications. In the next article we'll look at how to produce a simple website by implementing the /library/Lectric/view.class.php file.

Continue to Part 7 by clicking here.




Hide Sidebar >



Archive



Hide Sidebar >


This website uses cookies. Privacy Policy