Blog

CakePHP is one of those frameworks where it is easy to set up and get an application running in a minimal amount of time. It provides different securities, helps, and functions in the framework so that your application can run smoothly and be safe. As with all applications, the level of security and functionality depends on the developer, not the code, not the language, not the database. An application is only as secure, functional and reliable as the person/team who is coding it. One of the reasons I do like Cake is that the built in security and helpers offer a developer a great way to secure data, validate it, and display it. And that can also be one of the more trickier parts of getting the application to work correctly, finding the data to do something with it.

CakePHP provides some functionality for finding the data, this is done using the “find” method. You can read more about this at the Cookbook. Using this, one can grab data from many different tables if needed, or just one table. For this exercise, I am going to use dummy data to show how to find data, using simplistic finds, and using joins, and sub-queries. So first lets examine the data tables. Not all of these are going to be connected. This is a very simplistic, quickly drawn up solution to a lending library

In the image above, shows some sample tables, and these are just made up, and contain sample rows that may exist in those tables. The BOOKS and AUTHORS tables are a HABTM relationship, with a connecting table. The PUBLISHERS has many BOOKS. And the INVEN_ITEMS contains a multiple list of books that show whether the book is available or not. So we can start grabbing the data.

First we need to get a count for all books.

$total_books = $this->Book->find('count'); 

Now grab all authors, age, bio, book name, total pages, publish date for a single book with the ID of 212:

$details = $this->Book->find('first', 
    array(
        'conditions' => array(
            'Book.id' => '212',
        ), 
        'fields' =>array(
            'Book.name',
            'Book.pages',                    
            'Book.publish_date',
            'Author.name',
            'Author.age',
            'Author.bio',
        ),
        'recursive' => 0, 
    )
);

This will create a query statement to grab the data for the set fields, and only for the book id that equals 212. Recursive is set to 0 so it will grab all related data. Remember that this is a HABTM item, so CakePHP will take that into account and grab all authors for the book. We can leave off the recursive, I just find it is best practice to put this in in order to make the query more readable by someone who did not develop the application.

Find all Publishers:

$publishers = $this->Publisher->find('all'); 

Now three example of using the find technique in a basic way. More examples are available in the cookbook. Now what if we need to grab all the detailed information on the inventory items, and the tables are not connected in a CakePHP relationship? Without using a framework, the query statement would need to contain JOIN statements to get all the data we need. So the following is an example of this, again getting detailed info for the Book ID of 212:

$details = $this->find('first',
    array(
        'joins' =>array(
            array(
                'table' => 'books',
                'alias' => 'Book',
                'type' => 'LEFT',
                'conditions' => array(
                    'Book.id = Inven_item.book_id',
                ),
                'recursive' => -1
            ),
            array(
                'table' => 'books_authors',
                'alias' => 'BookAuthor',
                'type' => 'LEFT',
                'conditions' => array(
                    'BookAuthor.book_id = Book.id',
                ),
                'recursive' => -1
            ),
            array(
                'table' => 'authors',
                'alias' => 'Author',
                'type' => 'LEFT',
                'conditions' => array(
                    'Author.id = BookAuthor.author_id',
                ),
                'recursive' => -1
            ),
            array(
                'table' => 'publishers',
                'alias' => 'Publisher',
                'type' => 'LEFT',
                'conditions' => array(
                    'Publisher.id = Book.publisher_id',
                ),
                'recursive' => -1
            ),
        ),
        'conditions' => array(
            'InvenItem.book_id' => '212',
        ),
        'fields' =>array(
            'Book.name',
            'Book.pages',
            'Book.publish_date',
            'Book.quick_overview',
            'Author.name',
            'Author.age',
            'Author.bio',
            'Author.country',
            'Publisher.name',
            'Publisher.location',
            'Publisher.state',
            'Publisher.country',
            'InvenItem.isbn',
            'InvenItem.available',
        ),
        'recursive' => -1
    )
);

For whatever reason, the Inventory was not connected to the other tables, and so we force a join on the query. In the JOINS, we set this to a -1 recursive so it does not pull in all the other data. Now this is only one solution to put an example of a JOIN on a query. We have the tables, joined in tables, conditions, and recursive set. Once you get the data, then it will show all inventory items, details for the book, and whether or not the copy is available at the present time.

Now this may not be the best way to grab the data, and this is just for example purposes as well. Next week I will go further into detail about setting multiple conditions using ‘OR’ instead of ‘AND’, creating a simple search on possible conditional search terms and areas, and I may even create a quick little app to demonstrate this in real life.