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

Recommend this page to a friend!
  All package blogs All package blogs   PHP SQL Cursor PHP SQL Cursor   Blog PHP SQL Cursor package blog   RSS 1.0 feed RSS 2.0 feed   Blog How to Access Databas...  
  Post a comment Post a comment   See comments See comments (1)   Trackbacks (0)  

Author:

Updated on: 2021-04-16

Posted on: 2021-04-16

Viewers: 481 (April 2021)

Package: PHP SQL Cursor

Cursor is a class that can be used to execute SQL queries and retrieve the query results in a few lines of code.

Read this article to learn how to access databases using the Cursor class to reduce the amount of code that you need to write to implement your PHP applications.




Loaded Article

In this article you will learn:

What is the Cursor Class

How to Use the Cursor Class

A First Example

The Where Clause

The Second Parameter - Row Filters

Cursor->likeSelect

Inserting Data into the Database

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

Updating Data

Removing a Database Entry

Implementing a Trashing System

Good Practice to Use Less Memory

Cursor->query: Old Habits Die Hard

Cursor->escape

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

Conclusion


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

Inserting data into the database is very simple and straight forward with Cursor. Its this simple:
$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;
Note that Cursor->insert returns the id of the row inserted or 0 (Zero) if insertion fails. This is a very useful device to many developers who may need to immediately make use of the id of the last insert query.

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!';

Note: Not supplying the third parameter, although will result in a valid query, it will affect all the rows in your table.

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

It is a good practice if you have ever used the android cursor to always close your Cursor database connection after you use it to release some memory. You can close your Cursor using.
$cursor->close();

Cursor->query: Old Habits Die Hard

If you ever need to access the database the old way and still get a Cursor class result, or you do not know the proper Cursor query for doing it, you can still write your normal SQL queries and still get a Cursor result.
$cursor = new Cursor;
$cursor->query('SELECT * FROM users');
print_r($cursor);
See? Same result!

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);
If you ever need the Cursor connection access to extend Cursor just use this call:
$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.




You need to be a registered user or login to post a comment

Login Immediately with your account on:

FacebookGmail
HotmailStackOverflow
GitHubYahoo


Comments:

1. My class to acess database - radames kremer (2021-04-16 19:56)
Link gdrive... - 0 replies
Read the whole comment and replies



  Post a comment Post a comment   See comments See comments (1)   Trackbacks (0)  
  All package blogs All package blogs   PHP SQL Cursor PHP SQL Cursor   Blog PHP SQL Cursor package blog   RSS 1.0 feed RSS 2.0 feed   Blog How to Access Databas...  
For more information send a message to info at phpclasses dot org.