Password lookup

[reub77]

I'm sure this is easy to do, I just can't think of the sql to code to get what I want. How do I take a variable and check to see if it matches a variable in the database. I want to do this for a username, password, and then of course the id must equal the same as the id for both username and password. What sort of a look up would I do? Would I select it all store on the client and then check it, or would I just do a check on the database somehow?

Thanks,
Reub77

[Mega]

SELECT * FROM users WHERE password = '$password' AND username = '$username'

[reub77]

Yes, you could do that. But how would you proceed now? That will collect matches from the database and display them. How would you use that info for testing if there were more than one user and password in the table? What would you type if you didn't get a match? These are probably stupid questions but how would you do it?

Thanks

[mrmufin]

Originally posted by reub77
Yes, you could do that. But how would you proceed now?

Well, then. How do you need to proceed?

That will collect matches from the database and display them.

No, it won't. It will return a result set identifier if the query succeeds.

How would you use that info for testing if there were more than one user and password in the table?

Hopefully, your database structure doesn't allow for that. The easiest way to assure that there is only one row that has a username column with the value of 'jake' is to make the username column UNIQUE.

What would you type if you didn't get a match?

I'd assume that either the query was malformed or the username and password entered was incorrect.

These are probably stupid questions but how would you do it?

Well, those are your words. Perhaps 'vague' is a more appropriate adjective than 'stupid', since we all start from somewhere.

You didn't specify a database engine, and PHP has support for many database engines.

You didn't describe your form used to gather the information from user-world.

You didn't describe your table structure.

[reub77]

I didn't think I was that vague. I have a table, in that table i have usernames and passwords associated with them. All I want to do is perform some kind of a check to see if the user entered something that matches something in the database. The previous person said to use a select SQL statement. Great that finds the matches in the database or your right it will return a negative result. Where do I go from here is what I'm asking? I'm sure there's a million ways of going about this, but what is the most proficient? Thats all. Is that clearer?

I'm using mySQL
I'm using a table called login and three fields: id, username, and password.
I think thats all you need to know

Thanks

[mrmufin]

Okay, since now I know that you're using MySQL, as well as a table named 'login' and its columns, I can lead you toward a more useful solution.

I'll assume that you already have established working connection and database selection code; and that you're using the POST method on your user-world form. A simple username and password check query might consist of the following code:

// First, build the query. Since we already have a username and password as provided from user-world, we should only need to select the 'id' column.
// In other words, if a row matches the username and password the *only* unknown should be the 'id':
$q = sprintf("SELECT id FROM login WHERE username=\"%s\" AND password=\"%s\"", $_POST['username'], $_POST['password']);

// Run the query. $r represents a result set identifier.
// If the query fails, there'll be no result set identifier.
$r = mysql_query($q);

// If there's no result set identifier, the query is bad and mysql should kindkly return an error message:
if(!$r) {
	echo "Query $q failed: " . mysql_error();
} else {

// Just because there's a result set identifier, doesn't necessarily mean there are rows of data associated.
// Which is another way of saying 'the query is good, but no data matches it'.
if(!mysql_num_rows($r)) {
	echo "Username or Password is incorrect.";
	// This would be a good place to include() a login form...
} else {

	// Since we're only requesting a single column, we'll stick with fecth_row() rather than fetch_array():
	while($row = mysql_fetch_row($r)) {
		$login_id = $row[0];
	}

	// Free up the resources associated with the result set. That's just good programming practice.		
	mysql_free_result($r);
}
}

// At this point, you should have $login_id available in your script for further use, or you should have seen one of the echo messages...

As I mentioned in my previous post, it's best to assure that usernames are unique by building that into your database structure. If that is the case, you should be able to safely omit the while() statement, since only one row will be present in a result set:

$row = mysql_fetch_row($r);
$login_id = $row[0];

Hope this is helpful...

[james2010]

if you're testing variables submitted via a form, say for login, you could do a check like this:

// variables pass via form are $username and $password

// create a function to connect to your database

function db_connect()
{
$result = @mysql_connect('your_host', 'user', 'password');
if(!$result)
return(false);
if(!@mysql_select_db('your_database'))
return(false);
return($result);
}

// create a function to check the values you want to check

function login($username, $password)
{
$conn = db_connect();
if(!$conn)
return("Database Error"); // if database connection error
$result = mysql_query("SELECT * FROM your_table WHERE username='$username' and password='$password'");
if(!$result)
return("Query could not be executed"); // if query error
if(mysql_num_rows($result)>0)

// if the values match values in the database
return("true");
else
return("Query could not be executed");
}

// now check the values received via the form

$result = login($username, $password);

if($result == "true")
{
print("Your have been logged in");
}
else
{
print($result);
}

[mrmufin]

Originally posted by james2010

// create a function to connect to your database

function db_connect()
{
$result = @mysql_connect('your_host', 'user', 'password');
if(!$result)
return(false);
if(!@mysql_select_db('your_database'))
return(false);
return($result);
}

// create a function to check the values you want to check

function login($username, $password)
{
$conn = db_connect();
if(!$conn)
return("Database Error"); // if database connection error
$result = mysql_query("SELECT * FROM your_table WHERE username='$username' and password='$password'");
if(!$result)
return("Query could not be executed"); // if query error
if(mysql_num_rows($result)>0)

// if the values match values in the database
return("true");
else
return("Query could not be executed");
}

// now check the values received via the form
$result = login($username, $password);

if($result == "true")
{
print("Your have been logged in");
}
else
{
print($result);
}

This is a splendid concept, though I'd make a few minor changes in the functions. If there's no $result from the db_select() or login() functions, I'd provide for some debug lines to be displayed from MySQL, which should be available in this case. These messages tend to be quite helpful when debugging, and can be easily commented out once the code goes from its 'development' to 'production' state:

function db_connect() { 
	$result = @mysql_connect('your_host', 'user', 'password'); 
	if(!$result) {
		echo 'Unable to connect to the database: ' . mysql_error();
		return false;
	}
        if(!@mysql_select_db('your_database')) { 
		echo 'Unable to select the database: ' . mysql_error();
		return false; 
        }
	return $result;
}

function login($username, $password) { 
	$conn = db_connect(); 
	if(!$conn) { 
        	return false;
	}
        $result = mysql_query("SELECT id FROM login WHERE username = '$username' and password = '$password'", $conn); 
	if(!$result) {
		echo "Query could not be executed: " . mysql_error();
		return false;
	}
	if(mysql_num_rows($result) < 1) 
		// TRUE and FALSE are pre-defined PHP constant, and therefore, shouldn't be quoted:
		echo 'User password lookup contained no rows of data.';
		return false;
	}
	// You never need an 'else' after a return 
	// But what about the data that was just selected?
	while($row = mysql_fetch_row($result)) {
		$id = $row[0];
	}
	// Free up the result set:
	mysql_free_result($result);
        return $id; 
} 

Now I guess I should go back and correct an incredibly boneheaded error in my previous response...