I am trying to have my function return both the username and the roleid. Here is the code that i have for this task:
session_start();
if($_SESSION['LoggedIn']){
$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 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);
}
I have three tables that look like this:
mysql> select * from role;
+---------------+----------------------------------+
| id | description |
+---------------+----------------------------------+
| administrator | add, remove and edit manuscripts |
| reviewer | review manuscripts |
| site user | read manuscripts |
+---------------+----------------------------------+
3 rows in set (0.00 sec)
mysql> select * from userrole;
+--------+---------------+
| userid | roleid |
+--------+---------------+
| 1 | administrator |
| 2 | revinothingewer |
| 3 | other |
+--------+---------------+
3 rows in set (0.01 sec)
mysql> select * from user;
+----+----------+----------------------------------+
| id | username | password |
+----+----------+----------------------------------+
| 1 | kamau | 80ce10e582e13ec085f13409c3add5a4 |
| 2 | admin | db43b86da58631629adada27f1db5841 |
| 3 | amaina | 7fc961ec5b6b5d51d2445e97775bfc0d |
+----+----------+----------------------------------+
The roleid value is derived from the following script:
$query = "select * from user inner join userrole on user.id = userrole.userid inner join role on role.id = userrole.roleid";
$sql = $con->prepare($query);
$sql->execute();
$row = $sql->setFetchMode(PDO::FETCH_ASSOC);
while ($row = $sql->fetch()){
$roleid = $row ['roleid'];
}
My question is why is var_dump (getUserRole($username,$roleid)) returning false?
why is var_dump (getUserRole($username,$roleid)) returning false? Because
if($row > 0){
Every time this condition going to false part so only. If you want get username and roleid. You have to do following steps. 1. You know username.
$username = $_SESSION['username'];
2. You need to know roleid. So get roleid value by using following code.
$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'];
Thats it. Now you know username value and roleid value. Keep it simple buddy. Cheers.
...
After Question Editing 1 By OP
What is the value of $roleid? You have at this point still not set what the value of roleid plugged into the function is so the function is not running and PHP is returning an error (which you are failing to detect).
After Further Question Editing By OP
You need to think about your logic and think about what you are actually doing. You seem like you're trying to run before you can walk.
getUserRole takes two parameters, you only pass one non-null parameter, so the value of $roleid within the function will always be null.Your SQL:
"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"
Read this query, actually read it and establish what it is doing. You are looking for values from the tables where username = <value> and roleid = <value>, but the value of roleid is null because nothing has been passed to the function from the page script.
So you are looking for username value where there is a NULL roleid value, and your tables show that every roleid value is populated so that will always only ever return nothing values.
Also, because you're using INNER JOIN it means if any part of the query returns nothing, the query as a whole will return nothing, so this is why you're getting no response.
This is the principle reason why your SQL is returning false
You need to also fix your definitions, as you are referencing more than one table, each table column should be referenced as <table_name>.<column_name> such as
user.id = userrole.userid
Throughout your SQL query.
Several options:
Use LEFT JOIN instead of INNER JOIN.
Set the value of roleid as being not-null in the function or the page call to the function.
remove roleid from the function parameters and instead derive the value from a separate SQL query at the start of the function.
The choice is yours. Please read what you're actually requesting the Database to do, to understand why it doesn't do it. Also employ error logging for helpful feedback!
Useful Links: