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
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.
$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.
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.« Back to C. SQLite 4. Searching »