Frequently Asked Questions v2
|FAQ Main / Answer Detail|
Author: Honza email@example.com
External SQL query should look like:
SELECT * FROM item, content WHERE item.id = content.item_id // to join the tables AND item.id = 'hey67s[o0-78z._d' // 16 characters long packed id // see util.php3 - q_pack_id() AND content.field_id = 'headline........';
This SQL query select all fields from item table (where are stored just the most common item fields - id, short_id, slice_id, status_code, post_date, publish_date, expiry_date, highlight, posted_by, edited_by, last_edit, display_count, flags, disc_count, disc_app, externally_fed) plus the content of 'headline........' field from content table (will be in content.text field). You can change 'headline........' to any field id, of course, just like 'category.......1' or 'source.........1'. Also you can use short_ids as well to identify the item.
Data for an item is not stored in one table, as it is obvious and as you probably expect. Data are divided to two tables - item and content. Item table stores the common fields, which is requiered for each item. All other columns are sored in related table content (relation 1:N). The content table is quite easy - item_id, field_id, number, text, flag. Each row holds contents (in 'text' or 'number' filed) of one field (identified by field_id - like 'headline........') for one item (identified by 'item_id' - related to id in 'item' table).
Such, say 'virtual table database structure' or 'object oriented database' allows to store items of any number of fields. It also allows to store multiple values for one field (you just add a row with the same item_id and field_id).
More complicated SQL query, which contain more fields from content table could
SELECT item.*, c1.text, c2.text FROM item, content as c1, content as c2 WHERE item.id = c1.item_id // to join the tables AND item.id = c2.item_id // to join the tables AND item.short_id = '13432' // we can use short id AND c1.field_id = 'headline........'; AND c2.field_id = 'full_text.......';
To select all extended fields for the item use:
SELECT * FROM content WHERE item_id = 'hey67s[o0-78z._d';
It is important to know how the data are stored in database, but if you want to write some feature to AA, it is strongly recomended not to use direct database access, but rather use three main data API function to access data (especially for storing data into AA database). The functions are independent on database structure behind and it takes care about feeding, clearing the cache and other stuff.
Main AA data API functions are:
function QueryIDs($fields, $slice_id, $conds, $sort="", $group_by="", $type="ACTIVE", $slices="", $neverAllItems=0, $restrict_ids=array() ) - stored in include/searchlib.php3 function GetItemContent($ids, $use_short_ids=false) - stored in include/util.php3 function StoreItem( $id, $slice_id, $content4id, $fields, $insert, $invalidatecache=true, $feed=true ) - stored in include/itemfunc.php3
By QueryIDs function you will get array of IDs of items based on your conditions, possiblly sorted. Input conditions are stored in array which you already know from design of searchform (conds[publish_date....]=...). This function allows you to get IDs based on any number of conditions. Also it is completely database independent, so you need not to know, which field is stored in item table and which is stored in content table.
The content of an item you will get by GetItemContent function. As input parameter use the IDs of the items you want to get (possibly obtained by QueryIDs). The items are then returned in array structure, which is good to know about, because all data manipulation functions in AA uses this structure to access the item data. This structure is also used as parameter to StoreItem function.
Abstract Data Structure for data manipulation in AA (returned by
define( "FLAG_HTML", 1 ); # content is in HTML define( "FLAG_FEED", 2 ); # item is fed define( "FLAG_FREEZE", 4 ); # content can't be changed define( "FLAG_OFFLINE", 8 ); # off-line filled define( "FLAG_UPDATE", 16 ); # content should be updated # if source is changed # (after feeding)
So, to access content of 'headline.........' field for item number
5e72be7289a0be63820a8723837ce21a you can type:
$text = $content['5e72be7289a0be63820a8723837ce21a']['headline........']['value'];
Last Edit: Aug 17 2011
<aafaq id=1685> ~ToDo: 1685 How to push/pull data into/from database </aafaq>
This FAQ interface was developed by Jason at Commons.ca
ActionApps is a free software content management system initiated by
the Association for Progressive Communications (APC)
APC - Internet and ICTs for social justice and development