我发布了一个问题,要求提供帮助以解决我遇到的问题,它是封闭的,就像本地化一样。我现在已经缩小了我的问题。
我问的原始问题可以看到php登录脚本返回相同的用户ID?。
我在登录脚本上有问题。一切似乎都很好,我没有任何错误或任何东西。
基本上,当我登录 user_uid (用户UID)时,从数据库中检索到始终为3,由于某种原因,它没有获得正确的 user_uid ,但是所有的会话中存储的其他详细信息是正确的。
引起问题的查询是这个
$stmt = $dbh->prepare("
SELECT
*
FROM
users, users_roles, users_profiles
WHERE
user_login = :username
OR
user_email = :email
LIMIT 1");
如果我删除 user_roles 和 user_profiles 从SQL查询中,只需从 users 表中检索,它获得了正确的 user_uid,必须与我从多个表中检索的事实有关,并且查询在某个地方弄乱了。
这是带有我的模式和SQL查询http://sqlfiddle.com/#!2/3cc32/1/0
的SQLFIDDLE的链接以下是值的数组,由于某种原因即使 user_uid 被认为是 6 ,登录AS AS AS AS test帐户 phplover ,它显示 user_uid '3',这是表格中的第一行,似乎在某个地方发生了冲突。
进一步测试后,它似乎从>用户表获得了正确的数据,但 user_uid ,但它从 users_profiles 和用户_roles对于数据库中的第一行,也许它正确地从用户表中获取了 user_uid ,但也许查询正在覆盖它。
>我运行了查询是phpmyadmin,它仍然可以做同样的事情,绝对与我的sql查询有关,我如何修复我的查询,因此它可以检索正确的 user_uid ?
Array
(
[user_uid] => 3 // should be 6, 3 is the user_uid of the first row in database, seems to just fetch first row :/
[user_status] => 1
[user_login] => PhpLover
[user_pass] => 5e79a29e6292e7690a6bf56484140114f1374933081d499b8cc5034685950a16668868cd0886d93f9bc634a5649a6037022a5ef62e9b5d13cda24619bbdf610b;507a7ea891f609.84619944
[user_email] => smaple@sample.com
[user_registered] => 2012-10-14 09:58:16
[user_display_name] =>
[user_failed_logins] => 0
[id] => 3 // not sure where this is coming from but should be 6 like user_uid
[user_role] => subscriber
[user_gender] =>
[user_url] =>
[user_msn] =>
[user_aim] =>
[user_yim] =>
[user_twitter] =>
[user_facebook] =>
)
这是我的登录脚本,当我将问题范围缩小到我的SQL查询时,不需要显示它,但是以为我会发布它,以防人们进一步了解正在发生的事情。
<?php
// ob_start()
ob_start();
// Include config.php
require_once("".$_SERVER['DOCUMENT_ROOT']."/de-admin/config.php");
// if user is logged in redirect them to control panel
// an already logged in user cannot login whilst already logged in!
alreadyloggedin();
// top.inc.php
require_once($top_inc);
?>
<!-- Meta start -->
<title><?php echo SITE_NAME; ?> - Member Login</title>
<meta name="description" content="<?php echo SITE_NAME; ?> - Member Login, Sign in" />
<meta name="keywords" content="sign up, member, login, signin, account, membership, <?php echo SITE_NAME; ?>" />
<!-- Meta end -->
<?php
// sidebar.inc.php
require_once($sidebar_inc);
// main.inc.php
require_once($main_inc);
?>
<?php
if(isset($_POST['username_email'], $_POST['password'], $_POST[BOT_TEST], $_POST['token'])){
// check if form token is valid
IsValidFormTokenHash();
// initialize form errors array
$error = array();
// fetch form data
$username_email = trim($_POST['username_email']);
$password = trim($_POST['password']);
$bottest = $_POST[BOT_TEST];
// validate form data
if(empty($username_email)){
$error[] = 'Please enter your username or email address';
}
if(empty($password)){
$error[] = 'Please enter your password';
}
if(!empty($bottest)){
$error[] = 'Spambot detected, if your human please try again';
}
if(!empty($username_email) && !empty($password)){
try{
// connect to database
$dbh = sql_con();
// prepare query
$stmt = $dbh->prepare("
SELECT
*
FROM
users, users_roles, users_profiles
WHERE
users.user_login = :username
OR
users.user_email = :email
AND
users.user_uid = users_roles.user_uid
AND
users.user_uid = users_profiles.user_uid
LIMIT 1");
// execute query
$stmt->execute(array(':username' => $username_email, ':email' => $username_email));
if ($stmt->rowCount() > 0) {
$result = $stmt->fetch(PDO::FETCH_ASSOC);
echo '<pre>';
print_r($result);
echo '</pre>';
$user_db_pass = $result['user_pass'];
if(!ValidatePassword($password, $user_db_pass)){
$error[] = 'Invalid Login Details';
} else {
$user_status = $result['user_status'];
if($user_status == USER_STATUS_VERIFY){
$error[] = 'You must verify your account before you can log in';
}elseif($user_status == USER_STATUS_SUSPENDED){
$error[] = 'This account has been suspended';
}elseif($user_status == USER_STATUS_SPAM){
$error[] = 'This account has been marked as potentially spam';
} else {
// user valid
// fetch user details and assign there details to there sessions
$_SESSION['user_uid'] = $result['user_uid'];
$_SESSION['user_status'] = $result['user_status'];
$_SESSION['user_login'] = $result['user_login'];
$_SESSION['user_email'] = $result['user_email'];
$_SESSION['user_registered'] = $result['user_registered'];
$_SESSION['user_display_name'] = $result['user_display_name'];
$_SESSION['user_role'] = $result['user_role'];
$_SESSION['user_gender'] = $result['user_gender'];
$_SESSION['user_url'] = $result['user_url'];
$_SESSION['user_msn'] = $result['user_msn'];
$_SESSION['user_aim'] = $result['user_aim'];
$_SESSION['user_yim'] = $result['user_yim'];
$_SESSION['user_twitter'] = $result['user_twitter'];
$_SESSION['user_facebook'] = $result['user_facebook'];
// unset (destroy) form token
UnsetFormToken();
// On successful login get URI user was on
// so we can redirect them back to URI they was on
/*if(isset($_SESSION['redirect_to'])){
// if session redirect_to is found this means
// they tried to access a membersarea()
// so we get the URI and redirect to the
// secure page they tried accessing before logged in
$redirect_to = $_SESSION['redirect_to'];
// unset the session var
unset($_SESSION['redirect_to']);
// redirect
header("Location: ".SITE_URL."$redirect_to");
exit();
} else {
header("Location: /member/control-panel");
exit();
}*/
// now logged in redirect to control panel
//header("Location: /member/control-panel");
exit;
}
}
} else {
$error[] = 'Incorrect login details';
}
// close database connection
$dbh = null;
}
catch (PDOException $e){
ExceptionErrorHandler($e);
require_once($footer_inc);
exit;
}
}
// If errors found display errors
if(!empty($error)){
$SiteErrorMessages = '';
foreach($error as $msg){
$SiteErrorMessages .= "$msg <br />";
}
}
}
// display error messages
if(isset($SiteErrorMessages)){
SiteErrorMessages();
}
// the below values is to replace placeholders in tpl
$TemplateReplacementValues = array(
'SITE_NAME' => SITE_NAME,
'FORM_TOKEN_HASH' => GenerateFormTokenHash(),
'BOT_TEST' => BotTest()
);
// signup.tpl template location
$tpl = DOCUMENT_ROOT.'inc/tpl/login.tpl';
// load signup template
PageContentTemplate($tpl, $TemplateReplacementValues);
?>
<?php
// footer.inc.php
require_once($footer_inc);
// ob_end-flush
ob_end_flush();
?>
重写以下内容:
FROM users, users_profiles, users_roles
:
FROM users
INNER JOIN users_profiles USING (user_uid)
INNER JOIN users_roles USING (user_uid)
...否则,您的查询会产生一个十字架加入(至少可以说这是非常效率的)。
如果某些users
记录可能没有users_profiles
和users_roles
中的相应记录,则应在此处替换内连接(对于这些用户,相应的列值将在返回的行集中设置为null)。
$stmt = $dbh->prepare("
SELECT
*
FROM
users, users_roles, users_profiles
WHERE
(user_login = :username OR user_email = :email)
AND users.user_uid = users_roles.user_uid
AND users.user_uid = users_profiles.user_uid
LIMIT 1");
尝试
SELECT users.*,users_roles.*,users_profiles.* FROM users
LEFT OUTER JOIN user_roles ON users.user_uid=users_roles.user_uid
LEFT OUTER JOIN users_profiles ON users.user_uid = users_profiles.user_uid
WHERE users.user_login = :username OR users.user_email = :email LIMIT 1
您正在尝试加入2个以上的表格,这就是它在小提琴内的工作方式:
SELECT *
FROM users
INNER JOIN users_roles ON users.user_uid=users_roles.user_uid
INNER JOIN users_profiles ON users.user_uid = users_profiles.user_uid
WHERE (users.user_uid=6) AND (users.user_login='phplover')
使用此查询
"从用户中select * select * welf join join users_roles在u.user_uid = us.user_uid weft join user_profiles as up u.user_uid = up.user_uid as user_profiles u.user_login =:用户名或u.user_email =:电子邮件限制1"