8 - Working with the database

First of all, to avoid confusion, it's a standard for all non core extensions to use the following schema to name their database table:
{dbprefix}__{componentname}_{tablename}

Working within your extension you should not have to call {dbprefix} (as it is always '#__') nor {componentname}, from our point of view, as they are constants.

Let's say the dealer component has a table '#__dealer_cars' with the following fields:

id
user_id
company_name
registration_code
year
mileage

Within your component you can take advantage from BPLibrary saving a lot of typing.

You may want to select all the 'dodge':
$list = BpDb::getList('cars', array('company_name' => 'dodge'));

Note that we haven't typed the full '#__dealer_cars' database name. If your query will be executed only within the component, db prefix and component name are constants and you can avoid to include them. BpDb will complete the query for you.

You may want to select all cars between 1990 and 2000:
$list = BpDb::getList('cars', array('year'=>array('1990', 'BETWEEN', '2000') ) );

You may want to select all cars with year equal or over 1990:
$list = BpDb::getList('cars', array('year'=>array('1990', '>=') ) );

You may want to select all cars with year equal or over 1990 associated with their owner's data.
You need to perform a Join over database tables:
$extraData = array();
$extraData['join']['dbtable'] = '#__users';
$extraData['join'['key1'] = 'user_id';
$extraData['join']['key2'] = 'id';
$list = BpDb::getList('cars', array('year'=>array('1990', '>=') ), $extraData );

If something goes wrong you may want to check it out on the screen: just set 'printQuery' to true as extra data to get the query displayed on screen if it fails:

$extraData = array();
$extraData['join']['dbtable'] = '#__users';
$extraData['join'['key1'] = 'user_id';
$extraData['join']['key2'] = 'id';
$extraData['printQuery'] = true;
$list = BpDb::getList('cars', array('year'=>array('1990', '>=') ), $extraData );

Combining with Bp::isDebug() you can have the query displayed on screen only when executed by your IP.

Note that we typed the full dbtable name "#__users", as we are joining an external table.

If we want to query only an external table:

$users = BpDb::getList(array('dbtable' => '#__users'));

$user = BpDb::getRow(array('dbtable' => '#__users'), array('id' => $id ) );

These are just few examples. What else you can (and can't) do? Have a look inside the code and, even better, give it a try!