PHPDevShell Database access
2010-01-25 22:56:58   来源:   评论:0 点击:

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 assigned 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 assigned to the given User (as array or coma-separated string)

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)
 

相关热词搜索:

上一篇:PHPDevShell Security with groups and roles
下一篇:PHPDevShell Utilities

分享到: 收藏
频道总排行
频道本月排行