CODEWITHSUNDEEP
phpmysqlmysqliprepared-statement
Tural Ali
Tural Ali

Reputation: 23290

Mysqli operating with multiple tables

My full code looks like that. As you see, I used 2-3 queries for 1 signin process. I'll be glad if you can help me to optimize the process, (maybe the count of queries can be reduced):

foreach ($_POST as $k => $v)
    $$k = $v;

$stmt = $db->prepare("SELECT u.id, u.fname, u.lname, u.mname, u.level, u.pass, u.salt, u.approved, u.ban, u2.logged_in FROM `users` AS u, `ulog` AS u2 WHERE u.email=? AND u2.user_id=u.id") or die($db->error);
$stmt->bind_param("s", $email) or die($stmt->error);
$stmt->execute() or die($stmt->error);
$stmt->store_result();
if ($stmt->num_rows > 0) {
    $stmt->bind_result($id, $fname, $lname, $mname, $level, $db_pass, $salt, $approved, $ban, $logged_in) or die($stmt->error);
    $stmt->fetch() or die($stmt->error);
    if ($logged_in == 0) {
        $fullname = $lname . ' ' . $fname . ' ' . $mname;
        $stmt->close();
        if ($approved == 1) {
            if ($ban == 0) {
                $hash = hash('sha256', $salt . hash('sha256', $pass));
                if ($hash == $db_pass) {
                    $ip = ip2long($ip);
                    if (isset($rememmber) && $rememmber == "on") {
                        $ckey = GenKey();
                        $stmt = $db->prepare("INSERT INTO `ulog` (`user_id`, `signin_time`,`ip`, `logged_in`, `ckey`, `ctime`) VALUES (?, NOW(), ?, 1, ?, NOW())") or die($db->error);
                        $stmt->bind_param("iis", $id, $ip, $ckey) or die($stmt->error);
                        validateUser($id, $fullname, $level, $ckey, $rememmber);
                    } else {
                        $stmt = $db->prepare("INSERT INTO `ulog` (`user_id`, `signin_time`,`ip`, `logged_in`) VALUES (?, NOW(), ?, 1)") or die($db->error);
                        $stmt->bind_param("ii", $id, $ip) or die($stmt->error);
                        validateUser($id, $fullname, $level);
                    }
                    $stmt->execute() or die($stmt->error);
                    $stmt->close();
                    response('success', 'Daxil oldunuz. Səhifənizə yönləndiriləcəksiniz.');
                    exit;
                } else {
                    response('error', 'Şifrə düzgün deyil');
                    die();
                }
            } else {
                response('error', 'Bu istifadəçi müvəqqəti olaraq ban edilib.');
                die();
            }
        } else {
            response('error', 'Bu email aktiv edilməyib');
            die();
        }
    } else {
        response('error', 'Siz bir dəfə daxil olmusunuz.');
        die();
    }
} else {
    response('error', 'Bu email adresi ilə istifadəçi bazamızda mövcud deyil.');
    die();
}

Upvotes: 1

Views: 912

Answers (1)

jonstjohn
jonstjohn

Reputation: 60346

Here are a few thoughts:

  1. Do not display the login form for users that are current logged in.
  2. Use a simple method to determine if a user is logged in. If you are using the session only, just check for a session variable. If you are using a cookie, store the cookie identifier in the user table.
  3. If a user re-logs in somehow (using the back button to somehow re-access the form), you can still check to see if they are already logged in using the session or cookie identifier, then just continue. Personally, I would prefer to just re-log them in.
  4. If you want to keep a login log, great, use it. But don't try to use it to determine if they are logged in.

Another option if you really want to keep your current logic is to change your query to use a left join. That way you are going to get a row if the email matches whether or not the there is a row in the login log. Here is how you would write the left join:

SELECT u.id, u.fname, u.lname, u.mname, u.level,
    u.pass, u.salt, u.approved, u.ban, u2.logged_in
FROM `users` AS u 
    LEFT JOIN `ulog` AS u2 ON u.id = u2.user_id
WHERE u.email=?

Upvotes: 2

Related Questions