Select same field from multiple tables

[adhbvklwqdbviabjiawdnbij]

I want to select the user from super_administrators, administrators, teachers and students, and give the user permission based from what table he "came". But is giving the "Query failed" error...

 

<?php

//Start session

session_start();



//Include database connection details

require_once('../config/config.php');



//Array to store validation errors

$errmsg_arr = array();



//Validation error flag

$errflag = false;



//Connect to mysql server

$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);

if(!$link) {

	die('Failed to connect to server: ' . mysql_error());

}



//Select database

$db = mysql_select_db(DB_DATABASE);

if(!$db) {

	die("Unable to select database");

}



//Function to sanitize values received from the form. Prevents SQL injection

function clean($str) {



	$str = @trim($str);



	if(get_magic_quotes_gpc()) {

		$str = stripslashes($str);

	}



	return mysql_real_escape_string($str);

}



//Sanitize the POST values

$email = clean($_POST['email']);

$password = clean($_POST['password']);



//Input Validations

if($email == '') {

	$errmsg_arr[] = 'O campo Email nao foi preenchido.';

	$errflag = true;

}



if($password == '') {

	$errmsg_arr[] = 'O campo Senha nao foi preenchido.';

	$errflag = true;

}



//If there are input validations, redirect back to the login form

if($errflag) {

	$_SESSION['ERRMSG_ARR'] = $errmsg_arr;

	session_write_close();

	header("location: ../index.php");

	exit();

}



//Create query

$qry = "SELECT * FROM super_administrators,administrators,teachers,students WHERE email = '$email' AND passwd = '".md5($_POST['password'])."'";

$result = mysql_query($qry);

$member = mysql_fetch_assoc($result);

$table = mysql_field_table('$result', '0');



//Check whether the query was successful or not



if($result) {



	if(mysql_num_rows($result) == 1) {



		if($table == 'super_administrators')  {

			session_regenerate_id();

			$_SESSION['SESS_ID'] = $member['id'];

			$_SESSION['SESS_NAME'] = $member['name'];

			$_SESSION['SESS_EMAIL'] = $member['email'];

			session_write_close();

			header("location: ../users/sadmin/index.php");

			exit();

		}



		if($table == 'administrators')  {

			session_regenerate_id();

			$_SESSION['SESS_ID'] = $member['id'];

			$_SESSION['SESS_SCHOOL_ID'] = $member['school_id'];

			$_SESSION['SESS_NAME'] = $member['name'];

			$_SESSION['SESS_EMAIL'] = $member['email'];

			session_write_close();

			header("location: ../users/admin/index.php");

			exit();

		}



		if($table == 'teachers')  {

			session_regenerate_id();

			$_SESSION['SESS_ID'] = $member['id'];

			$_SESSION['SESS_SCHOOL_ID'] = $member['school_id'];

			$_SESSION['SESS_NAME'] = $member['name'];

			$_SESSION['SESS_EMAIL'] = $member['email'];

			session_write_close();

			header("location: ../users/prof/index.php");

			exit();

		}



		if($table == 'students')  {

			session_regenerate_id();

			$_SESSION['SESS_ID'] = $member['id'];

			$_SESSION['SESS_SCHOOL_ID'] = $member['school_id'];

			$_SESSION['SESS_CLASS_ID'] = $member['class_id'];

			$_SESSION['SESS_NAME'] = $member['name'];

			$_SESSION['SESS_REGISTRATION'] = $member['registration'];

			$_SESSION['SESS_EMAIL'] = $member['email'];

			session_write_close();

			header("location: ../users/aluno/index.php");

			exit();

		}



	} else {



		$errmsg_arr[] = 'Suas informacoes de login estao incorreta. Por favor, tente novamente.';

		$errflag = true;

		$_SESSION['ERRMSG_ARR'] = $errmsg_arr;

		session_write_close();

		header("location: ../index.php");

		exit();

	}



} else {

	die("Query failed");

}

?>

[sunfighter]

When you query a number of tables you need to use a JOIN.

 

BUT - it would be a lot easier to have the table where you store login info (name password email etc.) also have their status as super_administrator, administrator, teacher, or students. Then the login name yields the permissions.