Database access

From PHPDevShell

(written on July 27, 2009 for version 2.7.1)

See the API documentation.

PHPDevShell provides a utility class named "db" to allow a simple access to the database specified in the configuration. It deals with both data access and paging (splitting the results into pages for easy display).

Note that PHPDevShell has been designed to access your database as efficiently as possible; to do that is tries to minimize the number of layers between your script and the database, and focuses on a single server software.

PHPDevShell stores all its data in a set of tables inside a single database (the access parameters to the database are specified inside the configuration files).

About the prefix and the table names

To prevent possible conflicts, each installation of PHPDevShell can use a different prefix, specified in the config file. For example, if you have:

$configuration['database_prefix'] = 'pds_';

All table names will start by 'pds_' (pds_core_logs, pds_core_users, etc). You can there refer to the table using the prefix '_db_' which will be substituted to the prefix from the config file, for example:

SELECT * FROM _db_core_users WHERE user_id = 1

actually refers to the table named pds_core_users.

PHPDevShell database is the default database, but as in any SQL query, you can specify one in your query:

SELECT * FROM _db_core_users LEFT JOIN myDatabase.myTable USING (user_id) WHERE user_id = 1

Don't forget that all requests are made with the login and password specified in the config file, therefore your auxiliary database must be accessible to that mysql user.

User-related checks

A few methods allow you to check various facts about the users and the groups.

role_exist ($role_id)

returns true if the given numerical ID corresponds to an existing Role in the database

group_exist ($group_id)

returns true if the given numerical ID corresponds to an existing Group in the database

belongs_to_role ($user_id = false, $user_role)

returns true if the given User has been assigned the given Role (both numerical IDs)

belongs_to_group ($user_id = false, $user_group)

returns true if the given User has been assigned the given Group (both numerical IDs)

For the last four methods, if the user_id is not provided, the currently logged user is used.

For more information, you can consult Security with groups and roles.

Building the request

Apart from the database prefix, several methods can help you build your SQL query.

Users and groups limitations :

These function gives you a list of numeric IDs to be then used in SQL requests, or as an array. If the user_id is not provided, the currently logged user is used.

get_roles ($user_id = false, $return_array = false)

returns the list of the Roles accessible to the given User (as array or coma-separated string)

get_groups ($user_id = false, $return_array = false, $alias_only = false)

returns the list of the Groups accessible to the given User (as array or coma-separated string)

Note: "accessible" mean the user can set him/herself this role/group; (s)he may or may not be currently a part of it.

For example, let's say you want to fetch all the groups the current user belongs to:

$group_list = $this->db->get_groups(); // returns something like   1,2,8
$group_sql = 'SELECT * FROM _db_user_groups WHERE user_group_id IN ('.$group_list.')';

Note that the list is coma-separated, with no parenthesis.

For more information, you can consult Security with groups and roles.

Creating an automatic Filter Search Box

When you have a list of results you can easy add a filter and search system, like the example below with ($this->db->search) :

$select_phonebookentries = $this->db->query_split("
	SELECT
		t1.user_id, t1.user_display_name, t1.user_name,
		t2.cell_number, t2.work_number, t2.fax_number, t2.home_number
	FROM
		_db_core_users t1
	LEFT JOIN
		_db_simple_phonebook t2
	ON
		t1.user_id = t2.user_id
	{$this->db->search(array(_('User ID')=>'user_id', _('Cell Number')=>'cell_number', _('Work Number')=>'work_number', 
         _('Fax Number')=>'fax_number', _('Home Number')=>'home_number', _('User Display Name')=>'user_display_name'), 'WHERE')}
");

Note that the output is handled with a normal "mysql_fetch_array" for instance. The search filter is build automatically.

Documentation to follow:

• db->
  • table_exists ($table)
  • count_rows ($table_name, $column = false)
  • new_query ($query)
  • search ($column_array = false, $where_and_or = false, $order_by_array = false, $append_html = false) (use inside new_query())
  • get_roles ($user_id = false, $return_array = false)
  • get_groups ($user_id = false, $return_array = false, $alias_only = false)
  • role_exist ($role_id)
  • group_exist ($group_id)
  • belongs_to_role ($user_id = false, $user_role)
  • belongs_to_group ($user_id = false, $user_group)
  • set_role_query ($query_request, $query_root_request = false)
  • set_group_query ($query_request, $query_root_request = false)
  • write_settings ($write_settings, $custom_prefix = false)
  • delete_settings ($settings_to_delete = false, $custom_prefix = false)
  • get_settings ($settings_required = false, $custom_prefix = false)
  • does_record_exist ($table_name, $search_column_names, $search_field_values, $column_name_for_exclusion = false, $exclude_field_value = false)
  • select_quick ($from_table_name, $select_column_name, $where_column_name, $is_equal_to_column_value)
  • delete_quick ($from_table_name, $where_column_name, $is_equal_to_column_value, $return_column_value = false)
  • name_of_new_copy ($table_name, $name_field, $orig_name)
  • cache_write ($cache_unique_name, $cache_data)
  • cache_read ($cache_unique_name)
  • cache_clear ($cache_unique_name = false)
  • cache_empty ($cache_unique_name)