Help to get the right return from a function connecting to mysql

PHP
1

Hi,

Trying to get myself back into PHP but getting a little stuck and thinking I have been looking at this far too long to see what is probably obviously wrong!

Following a tutorial from here I created a function as part of an access page to check if the user is logged in, set an active session and what role the user has and then use it to determine whether access should be granted to a page. Everything so far is working up to determining the users role. I know it’s not working because the control element refuses to let the user see a page because their role is not ‘Administrator’.

Anyway, the function:


function userHasRole($role)
{
	include '../includes/db-connect.inc.php';

	try
	{
		$sql = "SELECT COUNT(*) FROM users
			INNER JOIN userrole ON users.id = userid
			INNER JOIN role ON roleid = role.id;
			WHERE username = :username AND role.id = :roleId";

		$s = $PDO->prepare($sql);
		$s->bindValue(':username', $_SESSION['username']);
		$s->bindValue(':roleId', $role);
		$s->execute();
		
	}
	catch (PDOException $e)
	{
		$error = 'Error searching for author roles.';
		echo $error;
		exit();
	}

	$row = $s->fetch();

	if ($row[0] > 0)
	{
		return TRUE;
	}
	else
	{
		return FALSE;
	}
}

And the database table values:



mysql> select * from role;
+---------------+--------------------+
| id            | description        |
+---------------+--------------------+
| Administrator | Full access rights |
| Member        | Limited access     |
+---------------+--------------------+
2 rows in set (0.00 sec)

mysql> select * from userrole;
+--------+---------------+
| userid | roleid        |
+--------+---------------+
|      1 | Administrator |
|      2 | Member        |
+--------+---------------+
2 rows in set (0.00 sec)

mysql> select * from users;
+-----------+----------+-----------------------+----------+---------------------
-------------+----+
| firstname | lastname | email                 | username | password
             | ID |
+-----------+----------+-----------------------+----------+---------------------
-------------+----+
| Test       | User1  | [email]test@gmail.com[/email] | Lee7997 | bcd4c48cac866845f881
29813be42bff |  1 |
| Test     | User2     | [email]test2@gmail.com[/email] | Lee1234    | 2a8b9e5416d2df83754f
500d2a72bcb4 |  2 |
+-----------+----------+-----------------------+----------+---------------------
-------------+----+
2 rows in set (0.00 sec)

In the control page I have this:


if (!userHasRole('Administrator'))
{
	$error = 'User is not an admin.';
	echo 'Logged in as: ' . $_SESSION['username'];    /* Check if session data is available - Remember to remove this */
	echo $error;
	exit();
}

Every time I get the user is not an admin message. Any help appreicated :slight_smile:

2

PDO’s fetch function will only fetch a single row of the result set each time it’s used, you need to use:

$s->fetchAll(PDO::FETCH_ASSOC);

Which will fetch the entire result set

3

Also - your query seems to be only returning the count of some fields, not any actual field values. Is that what you want? Your query is pretty cryptic to me, so I may be reading this incorrectly.

Try doing a var_dump on $row and examine it closely to see if you are generating what you expect.

4

I perhaps did not post enough of the code but the function above is one of 3, the first takes the values from the login form if they exist and opens a session if the details entered are contained in the users table.

So once they are logged in and then click on the admin login button the above function then checks whether they have the role of ‘Administrator’ assigned to them in order to get access to the admin area.

So all I am looking for this function to do is check whether they are admin and pass true or false back to the control file which is waiting for if (!userHasRole(‘Administrator’))

I have checked the value of $row and it is returning 4 which would indicate it is actually returning the number of total rows in the table. If I run the query directly in mysql it returns either 0 or 1 depending on the user so there is something wrong around the way I am executing $sql I think.

From dump

array(2) { [“COUNT(*)”]=> string(1) “4” [0]=> string(1) “4” }

5

Fixed!

Look up at the sql query, there is a ; after role.id that I put in by mistake that then prevented the next line working, so it was returning 4 all the time because the last line was running to identify either the 0 or 1 user that matched!

And of course it was working fine when I was trying it directly in mysql because I was not typing the ;

Sometimes the harder you look at something the less you see!

Thanks all for the assistance.