我一直在寻找SO中是否回答过这个问题,但我找不到任何答案。
我基本上在一个数据库中有两个表。表 member_privileges
1 保存用户信誉,表 2 保存用户详细信息。它们大致看起来像这样。
mysql> describe member_privileges;
+---------------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(255) | YES | | NULL | |
| can_comment | int(11) | NO | | NULL | |
| can_create_articles | int(11) | NO | | NULL | |
| can_edit_articles | int(11) | YES | | NULL | |
| can_edit_timeline | int(11) | NO | | NULL | |
| can_remove_comments | int(11) | NO | | NULL | |
| can_upload_profile_images | int(11) | NO | | NULL | |
| can_upload_article_images | int(11) | NO | | NULL | |
| can_approve_new_articles | int(11) | NO | | NULL | |
| can_freez_articles | int(11) | NO | | NULL | |
+---------------------------+--------------+------+-----+---------+----------------+
这是users
表。
mysql> describe users;
+----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(255) | NO | | NULL | |
| password | varchar(255) | NO | | NULL | |
| age | int(11) | NO | | NULL | |
| country | varchar(255) | NO | | NULL | |
| email | varchar(255) | NO | | NULL | |
| link | varchar(255) | NO | | NULL | |
| reg_date | date | YES | | NULL | |
| ip | varchar(255) | YES | | NULL | |
| gender | varchar(10) | YES | | NULL | |
| about | varchar(255) | YES | | NULL | |
| is_activated | int(11) | YES | | NULL | |
| activation_key | varchar(255) | YES | | NULL | |
| image_path | varchar(255) | YES | | NULL | |
+----------------+--------------+------+-----+---------+----------------+
因此,例如,在一个页面中,我正在尝试检查用户'john'
是否具有can_edit_articles
权限集。因此,如果在member_privileges
中username
"john"将can_edit_articles
设置为 1,则该用户可以编辑。
问题是一次检查这些细节。不像我现在这样运行两个查询。
$email = $_SESSION['email'];
$get_user = "SELECT id, username FROM members WHERE email = $email;
$can_user_edit = "SELECT can_edit_articles FROM member_privileges WHERE $email = ?;
因此,如您所见,我正在运行两个查询,我想知道是否有任何方法可以在一个 mysql 语句中运行它们。...
使用内部连接并防止 sql 语句进行 sql 注入
如果要选择 id、用户名和can_edit_articles值:
"SELECT m.id, m.username, p.can_edit_articles
FROM members m inner join member_privileges p
ON m.username=p.username WHERE m.email = '$email'"
$email = $_SESSION['email'];
$can_user_edit = "SELECT can_edit_articles FROM member_privileges
WHERE username = (SELECT username FROM users WHERE email = '$email')";
您应该在查询中使用 JOIN。 喜欢这个:
SELECT can_edit_articles FROM member_privileges
P LEFT JOIN members M ON P.username = M.username WHERE email = '$email'