3. Retrieving Data

If you prefer the ORM way to retrieve data Figure 8 provides 2 simple ways to acheive this.

Figure 8. Retrieving Data
$database->getRow('user', 'user_id', 1);	// returns the row from 'user' table where 'user_id' equals 1
$database->getRows('user');					// returns all the rows from the 'user' table
For getRow(), you don't need to worry about binding values. Eden automatically will do that on your behalf.
Get Rows
It's not good practice anymore to use getRows() beyond the first argument simply because it encourages bad programming practices. Later we will go over search() a better way to get rows for complex queries.

Though we don't encourage this method as much as we should given the note above, getRows() is a beast of a method worth explaining. getRows() actually has 7 arguments.

The 7 Arguments
  1. The name of a table
  2. (optional) A list of joining methods
  3. (optional) A list of filters
  4. (optional) A list of sort columns
  5. (optional) The starting index
  6. (optional) The maximum number of results to return
  7. (optional) The actual index to return
Figure 9. A Complex Get Rows
$joins = $filter = $sort = array();
$joins[] 				= array('inner', 'profile', 'user_id=profile_user', false);
$joins[] 				= array('left', 'post', 'user_id');
$filter[] 				= array('user_name LIKE %s', $name);
$filter[] 				= array('user_active = %d', 1);
$sort['user_id'] 		= 'ASC';
$sort['user_created'] 	= 'DESC';

$rows = $database->getRows('user', $joins, $filter, $sort, 0, 25);

As you can see getRows() has the capabilities to handle almost any type of query, but the caveat is that you need to know what your doing. Joining happens when you push an array of 4 arguments. The first argument is the type of join.

Join Types
  • inner
  • left
  • right
  • outer

The second argument in the join array is the name of the table you would like to join. The third argument is describing how the two tables are related. If you are using the keyword USING you only need to set the column name that's the same. The fourth argument is a flag whether if you are using the ON or USING flags.

Filter is no where short of being as complex as joining. Filtering happens when you push an array of 1 or more arguments. The first argument is the filter clause. If you notice in our filter example in Figure 5 we use %s to delimit a binded value. You can have as many binded values per filter as you like. The following arguments need to include the binded values in order of when they occur in the filter clause.

After we define our joins and filters we then show an example of how sorting works. Sorting is defined where the column name is set as a key and the order ASC or DESC set as the value. The last 2 arguments in getRows() are the start and range.

We'll go over a better way to get rows in our next part called searching and although we now discourage this way, it's still a valid way to get results.

© 2012 Openovate Labs. All rights reserved.