SQL return if not found

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

MHardeman25
Forum Commoner
Posts: 42
Joined: Mon Jul 12, 2010 10:34 am

SQL return if not found

Post by MHardeman25 »

what exactly does mySQL return if something is not found? like in the example code.

Code: Select all

$result = mysql_query("SELECT * FROM table1 WHERE field1 = '$var1' AND field2 = '$var2'"); 
like lets say it can't find any in that table that match the conditionals, what does it return?
and if I tried to do something like would that evaluate true if the conditionals exist, and false if it wasn't found.

Code: Select all

if($result){
//code here
} else {
//code here
}
MHardeman25
Forum Commoner
Posts: 42
Joined: Mon Jul 12, 2010 10:34 am

Re: SQL return if not found

Post by MHardeman25 »

Ok, at http://us.php.net/manual/en/function.mysql-query.php it says if the query fails, it returns boolean false, and if it succedes it returns a resource file.
MHardeman25
Forum Commoner
Posts: 42
Joined: Mon Jul 12, 2010 10:34 am

Re: SQL return if not found

Post by MHardeman25 »

Which is what I thought, and in which case my code should work, but it doesn't...
MHardeman25
Forum Commoner
Posts: 42
Joined: Mon Jul 12, 2010 10:34 am

Re: SQL return if not found

Post by MHardeman25 »

ok, here is the full code, if someone can spot the error, then bravo, I have tried to find it for like an hour.
index.php main file.

Code: Select all

<html>
	<head>
		<title>Title comes later</title>
		<link rel='stylesheet' href='index-layout.css'>

		<?php			
			//login box code
			
			session_start();
			if(isset($_SESSION)){
				session_unset();
			}
			
			include('data/SQL_Connect.php'); //all sql database handleing
			include('data/basics.php');	//some basic functions used
			
			//Connects to the server MySQL database 'users'
			$con = connectToServer();
			if($con){
				$dat = connectToDatabase('users');
				if(!$dat){
					print('Could not connect to database');	
				}
			} else {
				print('Could not connect to server');
			}

			//Global Variables
			$username = '';
			$password = '';
			
			/* NOT DONE needs SQL database */
			$ipAddress = $_SERVER['REMOTE_ADDR']; //gets users ip address
			/*
			if( ipAddress is on banlist ){
				header('Location: data/banned.html');
			}*/

			/* 	
			This code will only be executed if the user clicks the submit button in index.html
			The information in the forms username and password will be sent here.
			*/
			if(isset($_POST['submit1']))
			{
				//strip tags to remove hamful scripting input into the boxes
				$username = strip_tags($_POST['username1']);
				$password = strip_tags($_POST['password1']);
				
				$file_handle = query("SELECT * FROM tb_users WHERE username = '$username' AND password = '$password'"); //get tb_users
				
				//if it username and password match
				if($file_handle){
					$userInfo = query("SELECT * FROM tb_users WHERE username = '$username'");
					foreach(getElements($userInfo) as $field => $value){
						if(isset($_SESSION[$field])){
							unset($_SESSION[$field]);
						}
						$_SESSION[$field] = $value;
					}
				
					closeDatabase(); //close database
					header('Location: data/main.php'); //goto the main page
				}
				print 'Username or password are incorrect <br>';
			}

		?>

	</head>

	<body>
		<div id='login'>
			<form class='log' name='form1' method='POST' action='index.php'>
				<p>Username</p>
				<input class='field' name='username1' type=Text><br>
				<p>Password</p>
				<input class='field' name='password1' type='Password'><br><br>
				<input class='button' name='submit1' type='Submit' value='Login'>
				<p><a href=signup.php>Create an Account</a></p>
			</form>
		</div>
	</body>
</html>
SQL_Connect.php

Code: Select all

<?php

	/*
		Class Name: SQL_Connect.php
		
		This class will contain most SQL database handleing

		to use these functions in a website use the include()
		function at head of the desired webpage
		EX.
			include(SQL_Connect.php)
		
		please make sure this file is hidden on server
		it contains the server root username and password
		include calls to this file should also be hidden
		in source code.
	*/

	//Global Variables
	$server_handle; //resource id of database
	$db_name; //current database name
	
	//returns true if connected to mySQL
	if (!function_exists('connectToServer')) {
		function connectToServer(){
			Global $server_handle;
			$server_handle = mysql_connect('127.0.0.1', 'root', '') or die(mysql_error());
			if($server_handle){
				return True;
			} else {
				return False;
			}
		}
	} else {
		print('connectToServer() already exists');
	}

	//returns true if connected to a database in mySQL
	if (!function_exists('connectToDatabase')) {
		function connectToDatabase($name){
			Global $db_name, $server_handle;
			if($server_handle == null){
				return False; //if you havent connected to the server yet
			} else {
				$db_name = $name;
				$result = mysql_select_db($db_name, $server_handle) or die(mysql_error());
				if($result){
					return True;
				} else {
					return False;
				}
			}
		}
	} else {
		print('connectToDatabase() already exists');
	}
	
	//returns a query
	//each query can only be used once
	if (!function_exists('query')) {
		function query($query){
			return mysql_query($query) or die(mysql_error());
		}
	} else {
		print('query() already exists');
	}

	//returns true if all fields == all vars match
	if (!function_exists('getElements')) {
		function getElements($file_handle){
			$db_field = mysql_fetch_assoc($file_handle);
			foreach ($db_field as $element => $value) {
				if(!isset($arr)){
					$arr = array();
				}
				$arr[$element] = $value;
			}
			return $arr;
		}
	} else {
		print('getElements() already exists');
	}
	
	//closes current database resets global variables
	if (!function_exists('closeDatabase')) {
		function closeDatabase(){
			Global $server_handle, $db_name;
			mysql_close($server_handle) or die(mysql_error()); //close
			
			//reset
			$server_handle = null;
			$db_name = '';
		}
	} else {
		print('closeDatabase() already exists');
	}
?>
The error is that, if the user tries to login, if he uses the wrong password, he is rejected which is good, but if he uses the right username, then the password can be anything. It doesn't matter, the program will pass him along to the next page.
User avatar
liljester
Forum Contributor
Posts: 400
Joined: Tue May 20, 2003 4:49 pm

Re: SQL return if not found

Post by liljester »

MHardeman25 wrote:Ok, at http://us.php.net/manual/en/function.mysql-query.php it says if the query fails, it returns boolean false, and if it succedes it returns a resource file.
while that is a true statement, your query is not failing (failing indicates there were sql errors). your query can return an empty result set and thats not considered a failed query. it just returns 0 rows. it still returns a valid resource. so what you need to do is query the db to see if the username and password match a given user, then count the number of rows returned with mysql_num_rows(). if mysql_num_rows == 1, then you know you have a valid user.
MHardeman25
Forum Commoner
Posts: 42
Joined: Mon Jul 12, 2010 10:34 am

Re: SQL return if not found

Post by MHardeman25 »

Oh! I see. Basically, in the next page, I check if the person coming in has a username in the $_SESSION, if not, then he get's kicked back to login. So what was happening is no matter what the user inputs, the thing returns a resource file, the thing evals true. It then tries to put the users info into the $_SESSION by looking up the username. When the user input a correct username, it gets the info and passes you on. Then on the next page, you have a username so you stay there, the password didn't matter. If they didn't input a correct username, then the info can't be put in to $_SESSION, and when they get to the next page, it sends them back to the login. What a weird combination of errors.
MHardeman25
Forum Commoner
Posts: 42
Joined: Mon Jul 12, 2010 10:34 am

Re: SQL return if not found

Post by MHardeman25 »

Thanks!
MHardeman25
Forum Commoner
Posts: 42
Joined: Mon Jul 12, 2010 10:34 am

Re: SQL return if not found

Post by MHardeman25 »

wait... that doesn't seem to be working...
User avatar
liljester
Forum Contributor
Posts: 400
Joined: Tue May 20, 2003 4:49 pm

Re: SQL return if not found

Post by liljester »

can you post your updated code?
MHardeman25
Forum Commoner
Posts: 42
Joined: Mon Jul 12, 2010 10:34 am

Re: SQL return if not found

Post by MHardeman25 »

index.php

Code: Select all

<html>
	<head>
		<title>Title comes later</title>
		<link rel='stylesheet' href='index-layout.css'>

		<?php			
			//login box code
			
			session_start();
			if(isset($_SESSION)){
				session_unset();
			}
			
			include('data/SQL_Connect.php'); //all sql database handleing
			include('data/basics.php');	//some basic functions used
			
			//Connects to the server MySQL database 'users'
			$con = connectToServer();
			if($con){
				$dat = connectToDatabase('users');
				if(!$dat){
					print('Could not connect to database');	
				}
			} else {
				print('Could not connect to server');
			}

			//Global Variables
			$username = '';
			$password = '';
			
			/* NOT DONE needs SQL database */
			$ipAddress = $_SERVER['REMOTE_ADDR']; //gets users ip address
			/*
			if( ipAddress is on banlist ){
				header('Location: data/banned.html');
			}*/

			/* 	
			This code will only be executed if the user clicks the submit button in index.html
			The information in the forms username and password will be sent here.
			*/
			if(isset($_POST['submit1']))
			{
				//strip tags to remove hamful scripting input into the boxes
				$username = strip_tags($_POST['username1']);
				$password = strip_tags($_POST['password1']);
				
				$file_handle = query("SELECT * FROM tb_users WHERE username = '$username' AND password = '$password'"); //get tb_users
				
				//if it username and password match
				if(checkNumRows($file_handle) == 1){
					$userInfo = query("SELECT * FROM tb_users WHERE username = '$username'");
					foreach(getElements($userInfo) as $field => $value){
						if(isset($_SESSION[$field])){
							unset($_SESSION[$field]);
						}
						$_SESSION[$field] = $value;
					}
				
					closeDatabase(); //close database
					header('Location: data/main.php'); //goto the main page
				}
				print 'Username or password are incorrect <br>';
			}

		?>

	</head>

	<body>
		<div id='login'>
			<form class='log' name='form1' method='POST' action='index.php'>
				<p>Username</p>
				<input class='field' name='username1' type=Text><br>
				<p>Password</p>
				<input class='field' name='password1' type='Password'><br><br>
				<input class='button' name='submit1' type='Submit' value='Login'>
				<p><a href=signup.php>Create an Account</a></p>
			</form>
		</div>
	</body>
</html>
SQL_Connect.php

Code: Select all

<?php

	/*
		Class Name: SQL_Connect.php
		
		This class will contain most SQL database handleing

		to use these functions in a website use the include()
		function at head of the desired webpage
		EX.
			include(SQL_Connect.php)
		
		please make sure this file is hidden on server
		it contains the server root username and password
		include calls to this file should also be hidden
		in source code.
	*/

	//Global Variables
	$server_handle; //resource id of database
	$db_name; //current database name
	
	//returns true if connected to mySQL
	if (!function_exists('connectToServer')) {
		function connectToServer(){
			Global $server_handle;
			$server_handle = mysql_connect('127.0.0.1', 'root', '') or die(mysql_error());
			if($server_handle){
				return True;
			} else {
				return False;
			}
		}
	} else {
		print('connectToServer() already exists');
	}

	//returns true if connected to a database in mySQL
	if (!function_exists('connectToDatabase')) {
		function connectToDatabase($name){
			Global $db_name, $server_handle;
			if($server_handle == null){
				return False; //if you havent connected to the server yet
			} else {
				$db_name = $name;
				$result = mysql_select_db($db_name, $server_handle) or die(mysql_error());
				if($result){
					return True;
				} else {
					return False;
				}
			}
		}
	} else {
		print('connectToDatabase() already exists');
	}
	
	//returns a query
	//each query can only be used once
	if (!function_exists('query')) {
		function query($query){
			return mysql_query($query) or die(mysql_error());
		}
	} else {
		print('query() already exists');
	}

	//returns true if all fields == all vars match
	if (!function_exists('getElements')) {
		function getElements($file_handle){
			$db_field = mysql_fetch_assoc($file_handle);
			foreach ($db_field as $element => $value) {
				if(!isset($arr)){
					$arr = array();
				}
				$arr[$element] = $value;
			}
			return $arr;
		}
	} else {
		print('getElements() already exists');
	}
	
	if(!function_exists('checkNumRows')){
		function checkNumRows($var){
			return mysql_num_rows($var) or die(mysql_error());
		}
	} else {
		print 'checkNumRows() already exists';
	}
	
	//closes current database resets global variables
	if (!function_exists('closeDatabase')) {
		function closeDatabase(){
			Global $server_handle, $db_name;
			mysql_close($server_handle) or die(mysql_error()); //close
			
			//reset
			$server_handle = null;
			$db_name = '';
		}
	} else {
		print('closeDatabase() already exists');
	}
?>
MHardeman25
Forum Commoner
Posts: 42
Joined: Mon Jul 12, 2010 10:34 am

Re: SQL return if not found

Post by MHardeman25 »

return mysql_query($query) or die(mysql_error());

seems to return boolean
MHardeman25
Forum Commoner
Posts: 42
Joined: Mon Jul 12, 2010 10:34 am

Re: SQL return if not found

Post by MHardeman25 »

and it returns boolean every time... right or wrong with the username and password.
MHardeman25
Forum Commoner
Posts: 42
Joined: Mon Jul 12, 2010 10:34 am

Re: SQL return if not found

Post by MHardeman25 »

that should mean it's syntactically wrong, only it's got a die mysql_error() so it should show the error if there is one.
User avatar
liljester
Forum Contributor
Posts: 400
Joined: Tue May 20, 2003 4:49 pm

Re: SQL return if not found

Post by liljester »

its not boolean, it returns a resource id on success and false on failure. (the resource id does not == false). please read what i posted earlier again. your query IS SUCCESSFULL, there are just 0 rows returned. mysql_query only returns FALSE if the query FAILS, aka has sql errors.

are you getting errors, or its still not redirecting correctly? what is actualy being written to your sessions? on your main.php page, turn off the redirect and put in print_r($_SESSION);
MHardeman25
Forum Commoner
Posts: 42
Joined: Mon Jul 12, 2010 10:34 am

Re: SQL return if not found

Post by MHardeman25 »

here's what i get if I try to login
right or wrong with the username and password it doesn't matter. It seems to be returning a boolean.
Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\Program Files (x86)\EasyPHP-5.3.2i\www\Webbox\data\SQL_Connect.php on line 86
Post Reply