0

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?

4
Contributors
7
Replies
42
Views
1 Year
Discussion Span
Last Post by diafol
1

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.

Edited by cereal

0

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?

0

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.

1

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;
}
0

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";

Edited by Amaina

1

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.

Edited by diafol

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.