Created by: Ravi Budhu
SPD is a very very simple PHP MySQL database layer for those who do not want to use a framework and want more control over their database back-end and queries.
Clone the repository into a directory on your web server. Modify the following files as detailed in the files themselves.
autoload.php
Query.php
ConnectionFactory.php
SPD relies on the fact that your tables and columns are named a certain way.
- Table names should be plural. For example "persons" instead of "people" or "person."
- Column names made up of multiple words should separate the words with an underscore (_). For example, "first_name" or "last_name."
- That's it!
Navigate to classgen.php using your browser. Type in the name of the table you wish to generate a PHP class for and the name of the class.
Typically the name of the class should be the singular capitalized version of the database table name. For example, if the database table name is
"persons" the class name should be "Person."
Click the Generate button to create the class. The class is written to a file in the same directory as the classgen.php script.
The PHP class generated will have getters and setters for all columns in the table.
Getters and setters will be camel case depending on the name of the column. For example, for a column named first_name a getter and setter are created called getFirstName and setFirstName respectively.
After you have generated the PHP classes that map to your database tables, you are ready to start quering.
Near the top of every PHP page in which you are going to execute a database query, you must include this line:
include_once($_SERVER["DOCUMENT_ROOT"] . "/php/autoload.php");
Assuming the autoload.php script is located in the "php" subdirectory under your DOCUMENT_ROOT.
Create a new Query object with the name of the table you are selecting from.
$q = new Query('persons');
The Query object has some useful select methods on it which can be chained:
select(class name)
$q->select('Person');
execute()
$persons = $q->select('Person')->execute();
Execute executes the query and returns an array of objects
where(column name, column value)
$q->select('Person')->where('firstName', 'Joe')->execute();
Notice how column names are camel case. The generated SQL will turn the camel casing into underscores. So, firstName becomes first_name.
butNot(column name)
$persons = $q->select('Person')->where('firstName', 'Joe')->butNot('lastName')->execute();
This will select all of the rows where first_name is Joe, but will not fetch the last_name column. Can be chained like so:
$persons = $q->select('Person')->where('firstName', 'Joe')->butNot('lastName')->butNot('middleName')->execute();
butOnly(column name)
$persons = $q->select('Person')->where('firstName', 'Joe')->butOnly('lastName')->execute();
This will select all of the rows where first_name is Joe, but will only fetch the last_name column. Can be chained like so:
$persons = $q->select('Person')->where('firstName', 'Joe')->butOnly('lastName')->butOnly('middleName')->execute();
orderBy(column name)
$persons = $q->select('Person')->where('firstName', 'Joe')->orderBy('lastName')->execute();
Create a new Query object with the name of the table you want to insert into
$q = new Query('persons');
Create the PHP object, derived from classgen.php, representing the data you want to insert.
$person = new Person();
$person->setFirstName('Jane');
$person->setLastName('Doe');
Insert it like so:
$q->insert($person)->execute();
If your table has an auto-increment column (like an id column), then do something like so:
$q->insert($person)->butNot('id')->execute();
The insert method also supports the butNot and butOnly chained methods.
Create a new Query object with the name of the table you want to update
$q = new Query('persons');
The Query object has some useful update methods on it which can be chained:
update(object)
$q->update($person)->execute();
set(column name, column value)
$q->update($person)->set('firstName', 'Jane')->execute();
The update query also supports the where, butNot, and butOnly chained methods.
$q->update($person)->set('firstName', 'Jane')->where('lastName', 'Doe')->butNot('id')->execute();
Create a new Query object with the name of the table you want to delete from
$q = new Query('persons');
The Query object has some useful delete methods on it which can be chained:
delete()
$q->delete()->execute()
The delete query supports the where method.
$q->delete()->where('firstName', 'Joe')->where('lastName', 'Blo')->execute();