MySQL Question

[jfoto]

Hi,

I am a newbie to PHP and SQL and have got stuck. I am trying to get logon page that looks at the database to see if the user is an admin (has a yes in the admin column of the database)

I have got the following script that does a normal logon but I am unsure how to get it to chek the admin column. any help would be gratefully appreciated.

Thanks

Nik

case "login":

$connection = mysql_connect($host, $user,$password)
or die ("Couldn't connect to server.");
$db = mysql_select_db($database, $connection)
or die ("Couldn't select database.");

  $sql = "SELECT loginName FROM Member 
          WHERE loginName='$_POST[fusername]'";       
  $result = mysql_query($sql)
              or die("Couldn't execute query.");      
  $num = mysql_num_rows($result);                     
  if ($num == 1)  // login name was found             
  {
     $sql = "SELECT loginName FROM Member 
             WHERE loginName='$_POST[fusername]'
             AND password=password('$_POST[fpassword]')";
     $result2 = mysql_query($sql)
               or die("Couldn't execute query 2.");   
     $num2 = mysql_num_rows($result2);
     if ($num2 > 0)  // password is correct           
     {
       $_SESSION['auth']="yes";                       
       $logname=$_POST['fusername']; 
       $_SESSION['logname'] = $logname;               
       $today = date("Y-m-d h:m:s");                  
       $sql = "INSERT INTO Login (loginName,loginTime)
               VALUES ('$logname','$today')";
       mysql_query($sql) or die("Can't execute query.");
       header("Location: gillian.php");           
     }
     else    // password is not correct               
     {
       unset($do);                                    
       $message="The Login Name, '$_POST[fusername]' 
                 exists, but you have not entered the 
                 correct password! Please try again.<br>";
       include("admin_form.inc");                     
     } 
  }                                                   
  elseif ($num == 0)  // login name not found         
  {   
     unset($do);                                      
     $message = "The Login Name you entered does not 
                 exist! Please try again.<br>";
     include("admin_form.inc");
  }
break;

[rincewind456]

This would be a better way of doing it, adapted from the Manual, with security in mind!

<?php
function quote_smart($value)
{
   // Stripslashes
   if (get_magic_quotes_gpc()) {
       $value = stripslashes($value);
   }
   // Quote if not integer
   if (!is_numeric($value)) {
       $value = "'" . mysql_real_escape_string($value) . "'";
   }
   return $value;
}

$connection = mysql_connect($host, $user,$password)
or die ("Couldn't connect to server.");
$db = mysql_select_db($database, $connection)
or die ("Couldn't select database.");

// check if the user id and password combination exist in database
$sql = sprintf("SELECT fusername FROM member 
WHERE fusername=%s 
AND  fpassword=%s
AND admin = 'Yes'",
quote_smart($_POST['fusername']),
quote_smart($_POST['fpassword']));

$result = mysql_query($sql) or die('Query failed. ' . mysql_error());

$admin == mysql_num_rows($result);

if ($admin == 1){
	$_SESSION['auth']="yes";
	$logname=$_POST['fusername'];
	$_SESSION['logname'] = $logname;
	$today = date("Y-m-d h:m:s");
	$sql = "INSERT INTO Login (loginName,loginTime)
VALUES ('$logname','$today')";
	mysql_query($sql) or die("Can't execute query.");
	header("Location: gillian.php");
} else {
	print 'You are not authorised for this page';
}
?>

[jfoto]

Thanks a million