IF statement using mysql data

[john.muckley]

Hey guys,

 

I'm trying to figure out the best way to do an if statement based on the value of "accesslevel", which is a row in my mysql database.

 

At the minute my project has multiple login pages for various types of user, but i figured if i threw an extra row into my users table containing their access level (ie 1=admin,2=manager,3=user) then

i could just have the 1 login page, check this accesslevel and redirect to the appropriate page on successful login based on their access level.

 

I'm a bit of a php noob jumping in at the deepend, so apologies!!

 

but yeah, Any help appriciated!

Edited April 7, 2013 by john.muckley

[Andy-H]

$redirect_to = array(1 => 'admin', 'manager');
$query = "SELECT accesslevel FROM users WHERE...";
$sth = $dbh->prepare($query);
$sth->execute(array('param', 'param2'));
if ( ($access_level = $sth->fetchColumn()) !== false ) {
  header('Location: '. isset($redirect_to[$access_level]) ? $redirect_to[$access_level] .'.php' : 'user.php');
} else {
  // login failed
}

 

Something like that

[john.muckley]

I nailed it with this...

 

$dbcon = mysql_connect("$host","$username","$password");
if (!$dbcon)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("$db_name", $dbcon);
$query_result = mysql_query("SELECT * FROM $tbl_name WHERE username='$myusername'");
while($result_array = mysql_fetch_array($query_result))
  {
  $accesslv=($result_array['accesslv']);
  if($accesslv==1){
  header("location:adminhome.php");
  }
  else 
  if($accesslv==2){
  header("location:managerhome.php");
  }
    else 
  if($accesslv==3){
  header("location:userhome.php");
  }
    echo $accesslv;
  echo "<br />";
  }
mysql_close($dbcon);

Thanks for help!

 

If i want to do multiple checks on a record before proceeding, is it best to mimimise mysql connections and get all the info in one go rather than opening a connection to check one thing then opening another connection to check the next?

[Andy-H]

I don't get what you mean? You only need to "fetch" the data once and store it to a variable, then use the variable to check the value. Also, I am assuming username is unique, so you don't need a loop and can use a LIMIT 1 on your query.

 

Also, do you know that the mysql_ functions are deprecated? You should look into mysqli or PDO

 

You shouldn't really use select * when you're only using one field either.

 

Anyhow, I would rewrite the above like so:

 

// connect to database
include 'db/connection.php'; // it would be best to connect to the database in a self contained file for re-use
// we will use mysql_real_escape_string to prevent SQL INJECTION
$query = "SELECT accesslvl FROM $tbl_name WHERE username = '". mysql_real_escape_string($myusername) . "' LIMIT 1";
$result = mysql_query($query);
if ( !mysql_num_rows($result) ) {
  // login failed, give an error message
} else {
  $user = mysql_fetch_object($result);
  // we can use a switch statement here rather than if/elseif/else... block, this way we can add a default and it will be easier to add more cases later
  switch($user->accesslv) {
    case 1:
      header('location: adminhome.php');
      exit; // we should use exit after header location
    break;
    case 2:
      header('location: managerhome.php');
      exit;
    break;
    default:
      header('location: userhome.php');
      exit;
    break;
  }
}

 

See:

switch

mysql_real_escape_string