Syntax error or access violation In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'dbnam.users.id';

Question

Hie, am facing problems when i try to login into my php website online ,but when i login am getting this error

There is some problem in connection: SQLSTATE[42000]: Syntax error or access violation: 1140 In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'database_name.users.id'; this is incompatible with sql_mode=only_full_group_by

SO my code layout When you login the action button takes you to verify.php and code inside verify.php is attached below

<?php
    include 'includes/session.php';
    $conn = $pdo->open();

    if(isset($_POST['login'])){
        
        $email = $_POST['email'];
        $password = $_POST['password'];

        try{

            $stmt = $conn->prepare("SELECT *, COUNT(*) AS numrows FROM users WHERE email = :email");
            $stmt->execute(['email'=>$email]);
            $row = $stmt->fetch();
            if($row['numrows'] > 0){
                if($row['status']){
                    if(password_verify($password, $row['password'])){
                        if($row['type']){
                            $_SESSION['admin'] = $row['id'];
                        }
                        else{
                            $_SESSION['user'] = $row['id'];
                        }
                    }
                    else{
                        $_SESSION['error'] = 'Incorrect Password';
                    }
                }
                else{
                    $_SESSION['error'] = 'Account not activated.';
                }
            }
            else{
                $_SESSION['error'] = 'Email not found';
            }
        }
        catch(PDOException $e){
            echo "There is some problem in connection: " . $e->getMessage();
        }

    }
    else{
        $_SESSION['error'] = 'Input login credentails error';
    }

    $pdo->close();

    header('location: login.php');

?>

am assuming i need to separate these queries .

I would appreciate if anyone could show me the right way to go around that error and fix it .

any help will be appreciated

not sure if am answering you right but my users table has activation_code column, email,password , name , surname ,status (for verification check) and account type in which all users automatically have basic user rights — Hustlermt, Dec 19 '22 at 02:13
i have these columns in user table id email password type firstname lastname address contact_info photo status activate_code reset_code created_on — Hustlermt, Dec 19 '22 at 02:32
Just remove that `, COUNT(*) AS numrows` fro the query as it makes no sense. Use `if($row){` — Your Common Sense, Dec 19 '22 at 05:04

Syntax error or access violation In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'dbnam.users.id';

0 Answers0