How to Access Databases from PHP using Less Code with the Cursor Class Similar to Android Cursor - PHP SQL Cursor package blog

In this article you will learn:

What is the Cursor Class

Cursor is a pure PHP implementation of the Android SQL Cursor without deviating from good PHP coding practices. Cursor can simplify the way you access the database and can help you write the very complex INSERT, SELECT, UPDATE and DELETE operations in just one line of code.

Cursor automatically takes care of protecting PHP applications agains SQL injection attacks when inserting into the database, so you do not need to escape your data before you execute a query, but it also provides you an easier way of doing it if you still need to escape values.

How to Use the Cursor Class

First, you must include the cursor.php class script in your PHP scripts to use the Cursor class.

A First Example

$cursor = new Cursor;
$cursor->select('users');
print_r($cursor);

The above example shows a simple use of the Cursor class that can return all the rows in the "users" table. Then it displays the resulting object to screen.

It is worth to mention here that the Cursor class returns results as objects. The first parameter of a Cursor class function is always the table name.

The Where Clause

It is very common to query the database to return a row only if a set of conditions are met. The next example shows you how to do that with the Cursor class.

$cursor = new Cursor;
$cursor->select('users', null, ['user_id' => 9, 'email' => 'example@me.com']);
print_r($cursor);

Note the second parameter is null. I will comment more on this below.

The Where clause is pretty easy to implement with the Cursor class. Just supply your conditions as an associative array of database column to their desired values.

The Second Parameter - Row Filters

The second parameter which is set to null in the previous example allows you to specify which rows will be returned by Cursor in an array. Example:

$cursor = new Cursor;
$cursor->select('users', ['name', 'email'], ['user_id' => 123]);
print_r($cursor);

Note: to get results using a conditions that filters rows that match the opposite condition, i.e. instead of where x = abc AND y = 123, you want a query like where x = abc AND y != 123, you should prefix the column name with "!". Something like this:

$cursor = new Cursor;
$cursor->select('users', ['name', 'email'], ['user_id' => 123, '!country' => 'us']);
print_r($cursor);

Cursor->likeSelect

The like select provides an easy method to write queries where every object of the where clause is something LIKE '%xxx%' or LIKE '%xxx' etc. in a simple query. Example:

$cursor = new Cursor;
$cursor->likeSelect('users', null, ['name' => 'richard']);
print_r($cursor);

The above example returns all rows in table users where name contains the string/word "richard".

Inserting Data into the Database

$cursor = new Cursor;
$id = $cursor->insert('users', [ 'name' => $_POST['name'], 'email' => $_POST['email'], 'password' => md5($_POST['password']) ]);
echo "The id of the new user is " . $id;

Inserting Data into the Database Only If It Was Not There - Cursor->insertUnique

$cursor = new Cursor;
$id = $cursor->insertUnique('users', [ 'name' => $_POST['name'], 'email' => $_POST['email'], 'password' => md5($_POST['password']) ]);
echo $id;

The above example will only insert into the database only if there is no row containing the same name, email and password as supplied above. This is a very useful tool especially when it comes to user registration.

Most often however, you will want to insert only if one or a few rows do not exist. In such case, you should supply a third array consisting of columns in the second parameter that may contain the same data. Example:

$cursor = new Cursor;
$id = $cursor->insertUnique('users', [ 'name' => $_POST['name'], 'email' => $_POST['email'], 'password' => md5($_POST['password']) ], ['password']);
echo $id;

The example above inserts a row only if there is no row containing the same name and email. It will not bother to check if the row contains the same password.

Updating Data

Updating one or more database rows is very simple with the Cursor class. An example is provided below:

$cursor = new Cursor;
$success = $cursor->update('users', ['name' => 'ore'], ['user_id' => 123]);
if($success) echo 'Database updated successfully!';

Removing a Database Entry.

Removing a row can be one of the simplest queries. It is this easy to remove a row with the Cursor class.

$cursor = new Cursor;
$success = $cursor->delete('users', ['user_id' => 123]);

The Cursor class by default does not return true or false when removing database rows as a simple test.

The Cursor class can return if the data was actually removed. To implement this feature, however will require to edit a line and add another in the cursor.php as described.

1. Locate the delete method in cursor.php. You can find it by searching for the line where you have "public function delete".

2. Locate "$sql->execute();" and replace it with "return $sql->execute();"

3. Add another line after the line following the one you just edited - The line containing just "}" if you haven't edited the source code.

4. Type "return false;".

Implementing a Trashing System

Implementing a trashing feature in Cursor is very simple and documented in the source code itself. For those who haven't explored the source code, do the following:

1. Create a new table in your database and call it "trash" or whatever you choose to call it. Lets call this name1.

2. Add a column and name it "content" or another name of your choice. Lets call this name2 and another column and call it column_id (You are advised not to change this as future releases may contain the methods to view and retrieve the trash).

3. Search in cursor.php for "Trashing before removing row".

4. Uncomment the line following it.

5. Update "trash" to name1 and "content" to name2 or leave its default value if you have used the default values.

6. Save the file.

You may retrieve content of the trash can later using the following example.

$cursor = new Cursor;
$trash = $cursor->select('trash', ['content'], ['id' => 123]);
foreach($trash as $trash_item){
    echo $trash_item->content;
}

Good Practice to Use Less Memory

$cursor->close();

Cursor->query: Old Habits Die Hard

$cursor = new Cursor;
$cursor->query('SELECT * FROM users');
print_r($cursor);

Cursor->escape

If you ever need to escape data while using Cursor->query, you can use the Cursor->escape() method. Note that you never need to use this is a regular Cursor query.

$cursor = new Cursor;
$name = $cursor->escape($_POST['name']);
$cursor->query("SELECT * FROM users WHERE name  = $name");
print_r($cursor);

$cursor->connection();

How to Download the Cursor Package or Install It with PHP Composer

The Cursor package is available for you to download as a ZIP archive by going to the download page or install it using the PHP Composer Tool by going to the installation instructions page.

Conclusion

You can use this class, extend it and have fun with it. if you ever make any useful extension to Cursor and wan't wish to share it with me by sending me a message using this package contact page.