I have this code

session_start();

$username = $_SESSION['username'];
 function getUserRole($username, $roleid){
    $con=dbConnect();
    $query="select * from user inner join userrole on user.id = userrole.userid inner join role on role.id = userrole.roleid where username = $username";
    $sql=$con->prepare($query);
        $sql->bindValue(':username',$username);
        $sql->bindValue(':roleid',$roleid);
        $sql->execute();
        $row = $sql->fetch();
            $username = $row['username'];
            $roleid = $row['roleid'];   
                if($row > 0){

                    return  $username;
                }
                else{
                    return false;
                }
    }

My pain is that when i query the database where the username = $username, the query returns nothing but when i use the text for username i get results. Do session variables work inside functions? What can i be possibly be doing wrong?

Hi,

at the moment you're using the $username variable directly inside the query and without quotes:

where username = $username

change it to the placeholder:

where username = :username

as is set by the bindValue() method.

Also, if not used, remove the :roleid bindValue() as that will produce:

'SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens'

i.e. a fatal error that will stop the script.

If this still does not solve, then enable the PDO exceptions and the PHP error logging and post back with detailed errors.

Thanks @cereal i have changed the code to this:

    session_start();
    $username = $_SESSION['username'];
     function getUserRole($username, $roleid){
        $con=dbConnect();
        $query="select * from user inner join userrole on user.id = userrole.userid inner join role on role.id = userrole.roleid where username = :username";
        $sql=$con->prepare($query);
            $sql->bindValue(':username',$username);
            //$sql->bindValue(':roleid',$roleid);
            $sql->execute();
            $row = $sql->fetch();
                $username = $row['username'];
                //$roleid = $row['roleid'];   
                    if($row > 0){
                        return  $username;
                    }
                    else{
                        return false;
                    }
        }
print getUserRole($username);

The code above works fine (Thank you very much). However, i would also like to get the roleid returned and when i change the query to this:

 $query="select * from user inner join userrole on user.id = userrole.userid inner join role on role.id = userrole.roleid where username = :username and roleid = :roleid";
    $sql=$con->prepare($query);
        $sql->bindValue(':username',$username);
        $sql->bindValue(':roleid',$roleid);
        $sql->execute();
        $row = $sql->fetch();
            $username = $row['username'];
            $roleid = $row['roleid'];   
                if($row > 0){

                    return  $username . $roleid;
                }
                else{
                    return false;
                }
    }

print getUserRole($username, $roleid);

When i change the code to look like the one above, the print returns no results. Does introducing roleid variable cause this error?

Member Avatar

diafol

Where is $roleid coming from? Anyhow the SQL is a bit verbose without aliases. You should reference your "where fields" fully in case of ambiguity. Try:

$query = "SELECT * FROM user AS u 
                        INNER JOIN userrole AS ur ON u.id = ur.userid 
                        INNER JOIN role AS r ON r.id = ur.roleid 
                        WHERE u.username = :username AND ur.roleid = :roleid";

Not sure in which circumstance you'd use this though.

However, i would also like to get the roleid returned

That implies that you don't know the initial roleid. So your function should not be accepting $roleid and shouldn't be part of the WHERE clause since you don't know what it is. Try:

function getUserRole($username)
{
    $roleid = false;
    $con=dbConnect();
    $query="select userrole.roleid from user 
                    inner join userrole on user.id = userrole.userid 
                    inner join role on role.id = userrole.roleid 
            where username = :username";
    $sql=$con->prepare($query);
    $sql->bindValue(':username',$username);
    $sql->execute();
    $sql->store_result();
    if($sql->num_rows>0)
    {
        $sql->bind_result($roleid);
        $sql->fetch();
    }
    $sql->free_result();
    $sql->close();
    $con->close();
return $roleid;
}

Hi all,
I finally got a solution. The problem was that i had not initially set a value for role id. The solution is here

    $con = dbConnect();
    $query = "select roleid from user join userrole on user.id = userrole.userid where user.username = :username";
    $sql = $con->prepare($query);
    $sql->bindValue(':username', $username, PDO::PARAM_STR);
    $sql->execute();
    $row = $sql->fetch();
    $roleid = $row['roleid'];

    //  
    function getUserRole($username,$roleid){
    //
    $con = dbConnect();
    //
    //$roleid = $_GET['roleid'];
    $query="select * from user inner join userrole on user.id = userrole.userid inner join role on role.id = userrole.roleid where username = :username and roleid = :roleid";
    $sql=$con->prepare($query);
        $sql->bindValue(':username',$username);
        $sql->bindValue(':roleid',$roleid);
        $sql->execute();
        $row = $sql->fetch();
            $username = $row['username'];
            $roleid = $row['roleid'];   
                if($row > 0){

                    return  $roleid;
                }
                else{
                    return false;
                }
    }
    $userhasrole = getUserRole($username, $roleid);
    echo "Karibu $userhasrole";
Member Avatar

diafol

Hi all,
I finally got a solution. The problem was that i had not initially set a value for role id.

Yep that would do it:

Where is $roleid coming from?

And

That implies that you don't know the initial roleid. So your function should not be accepting $roleid and shouldn't be part of the WHERE clause since you don't know what it is.

Glad you got it sorted.