Chek user role and loggedin status?

[jacob21]

Code what i made so far. Your comments at what should i do differently.

 

My configs.php

<?php

$userQuery = 'SELECT * FROM users WHERE id = :id';
$user = $db->prepare($userQuery);
$user->bindParam(':id', $_SESSION['userId'], PDO::PARAM_INT);
$user->execute();
$userInfo = $user->fetch(PDO::FETCH_ASSOC);

?>

functions.php

<?php

function loginCheck(){
	
	global $db;
	
	if(isset($_SESSION['userId'], $_SESSION['loginString'])){
		
		$query = 'SELECT username FROM users WHERE id = :id';
    	$user = $db->prepare($query);
		$user->bindParam(':id', $_SESSION['userId'], PDO::PARAM_INT);
		$user->execute();
		$row = $user->fetch(PDO::FETCH_ASSOC);
		
		if($user->rowCount() == 1){
			
			if(hash('sha512', $row['username'].$_SERVER['HTTP_USER_AGENT']) == $_SESSION['loginString']){
				
				return true;
				
			}else{
				
				return false;
			}
			
		}else{
			
        	return false;
       	}
		
	}else{
		
    	return false;
		
    }	
		
}

function checkUserRole(){//can be user, admin and moderator
	
	global $userInfo;
	
	if($userInfo['userRole'] == 'admin' or $userInfo['userRole'] == 'moderator'){
		
		return true;
		
	}else{
		
		return false;
	
	}
	
}

?>

shoutbox.php

Can this be done with one query?

global $db, $userInfo;

$sbQuery = 'SELECT * FROM shoutbox ORDER BY dateCreated DESC LIMIT 30';
$sb = $db->query($sbQuery);
    
$usersQuery = 'SELECT * FROM users WHERE shoutBoxBan = "yes"';
$users= $db->query($usersQuery);
$usersRow = $users->fetch(PDO::FETCH_ASSOC);

$hiddenAction = '';
	
while($sbRow = $sb->fetch(PDO::FETCH_ASSOC)){
		
    if(loginCheck() and checkUserRole()){
			
        $hiddenAction = " <a href=\"javascript:;\" onClick=\"deleteMessage('".$sbRow['id']."')\" class=\"shoutBoxDelete\" title=\"Delete\">x</a>";

        if($usersRow['username'] == $sbRow['username']){
				
	    $hiddenAction .= " <a href=\"javascript:;\" onClick=\"unBan('".$sbRow['username']."')\" class=\"shoutBoxBan\" title=\"Unban\">u</a>";
			
	}else{

            if($userInfo['username'] != $sbRow['username']){//admin and moderator cant ban themselves.
					
	        $hiddenAction .= " <a href=\"javascript:;\" onClick=\"banUser('".$sbRow['username']."')\" class=\"shoutBoxBan\" title=\"Ban\">o</a>";
	        $hiddenAction .= " <a href=\"javascript:;\" onClick=\"tempBanUser('".$sbRow['username']."')\" class=\"shoutBoxBan\" title=\"Temp Ban\">ø</a>";
				
	}
			
    }
			
}

....................................

[Frank_b]

Am i right that you store usernames in the shoutbox table instead of the user id?

 

You shouldn't do that.

 

Yes you can retrieve all the required information in one time

 

lets give an example:

 

Table users:

id: INT primary key, auto increment
name: VARCHAR 255
email: VARCHAR 255

...

Table shoutbox:

id: INT primary key, auto increment
user_id INT foreign key (also called Index)
message: longtext

...

[jacob21]

 

Am i right that you store usernames in the shoutbox table instead of the user id?

 

You shouldn't do that.

yep usernames. Why?

[Frank_b]

What if you have two users with exactly the same name? Or what if a moderator creates two accounts? One account as a normal user and one for moderating?

[Frank_b]

In addition to my example tables:

 

With a JOIN you can retrieve all the information you need:

SELECT
    s.id, s.message, u.id as uid, u.name
FROM
    shoutbox s
LEFT JOIN
    users u
ON
   s.user_id=u.id

after this you can also add a WHERE, ORDER BY or LIMIT.

 

Notice that i did not use a * in the SELECT but instead a list of columnnames . because both tables have a column 'id' i gave an alias for the user-id which i called uid.

 

The result of the query will look like this:

id | message      | uid | name
-------------------------------
26 | Some text    | 1   | Frank
27 | Another text | 2   | Jacob

Edited October 2, 2014 by Frank_b

[jacob21]

there is check what does not let to create 2 same username

 

yes i noticed that *

Edited October 2, 2014 by jacob21

[Frank_b]

Okay there is a check.. in this case that is possible but with many other relations between tables there is no check. So the standard is to use a autoincrement (and unique) id. And why not follow this standard with the users table?

[jcbones]

Going back to the OP.  You should keep data normalized.  If you store usernames in multiple tables, what happens when someone changes their username?  Using the relational database MySQL, you should be relating the tables to each other.  Storing the user id in the shoutbox table, then joining it together to get the username.

 

Also, you are retrieving your user data multiple times per page load.  You should only need to get data once.  There is no need to keep requesting the same data.

You have this:

<?php

$userQuery = 'SELECT * FROM users WHERE id = :id';
$user = $db->prepare($userQuery);
$user->bindParam(':id', $_SESSION['userId'], PDO::PARAM_INT);
$user->execute();
$userInfo = $user->fetch(PDO::FETCH_ASSOC);

?>

Then this should look similar to:

<?php
//You already have your userInfo, there is no need to query it again to get the username.  It exists in userInfo.
function loginCheck($userInfo){   
    if(isset($_SESSION['userId'], $_SESSION['loginString'])){
        if(hash('sha512', $userInfo['username'].$_SERVER['HTTP_USER_AGENT']) == $_SESSION['loginString']){               
                return true;               
        }   
    }  return false;        
}

function checkUserRole($userInfo){//can be user, admin and moderator   
    //global $userInfo; //do not use global.   
    if($userInfo['userRole'] == 'admin' or $userInfo['userRole'] == 'moderator'){       
        return true;       
    }       
    return false;
}

You will have to pass $userInfo into your functions, well because, global is just bad form

[jacob21]

 

In addition to my example tables:

 

With a JOIN you can retrieve all the information you need:

SELECT
    s.id, s.message, u.id as uid, u.name
FROM
    shoutbox s
LEFT JOIN
    users u
ON
   s.user_id=u.id

after this you can also add a WHERE, ORDER BY or LIMIT.

 

Notice that i did not use a * in the SELECT but instead a list of columnnames . because both tables have a column 'id' i gave an alias for the user-id which i called uid.

 

The result of the query will look like this:

id | message      | uid | name
-------------------------------
26 | Some text    | 1   | Frank
27 | Another text | 2   | Jacob

 

How do i display unban feature based on your example? Earlier i have this if i used 2 query.

if($usersRow['username'] == $sbRow['username']){
				
	    $hiddenAction .= " <a href=\"javascript:;\" onClick=\"unBan('".$sbRow['username']."')\" class=\"shoutBoxBan\" title=\"Unban\">u</a>";
			
	}else{


................................................

Edited October 4, 2014 by jacob21

[Frank_b]

It's a bit hard to understand what you want to reach..

 

What are you try to make? 

For what purpose is shoutBoxBan in the users table? Are that the administrators?

Who do you want to give unBan future? Only administrators?

SELECT
    s.id, s.message, u.id as uid, u.name, u.shoutBoxBan
FROM
    shoutbox s
LEFT JOIN
    users u
ON
   s.user_id=u.id

if($usersRow['shoutBoxBan'] == 'yes'){
 // administrator
}

[jacob21]

Anyone who dosen't follow shoutbox rules will be banned and admin and moderator can unban those users.

[Frank_b]

to get a better picture of the data that is available after a selectquery use the print_r function:

$row = $user->fetch(PDO::FETCH_ASSOC);

echo '<pre>';
print_r($row);