mySQL selecting data from a specific row

[Darkstars31]

Ok, i have this slight problem.

For fun I begain writting a tournmemnt ladder script php and mysql.
I need to know how to modify my code to make sure that when a user logs into the website, that it only allows them to login with there password.

I need it to only authenticate the user with his corresponding password?

Currently you can login with a User name and password, however any combination of user names and passwords allows a user to login under that username which could be dangerous to the validity of the members/admins.
------------------Code Below---------------------
<?php
include 'config.php';
include 'connectdb.php';

$userId = $_POST['iUserName'];
$ipassword = $_POST['iPassword'];

$result = mysql_query( "SELECT User_id FROM $dbnametwo" )or die("SELECT Error: ".mysql_error());

$result2 = mysql_query( "SELECT User_password FROM $dbnametwo" )or die("SELECT Error: ".mysql_error());

$result3 = mysql_query( "SELECT adminlvl FROM $dbnametwo" )or die("SELECT Error: ".mysql_error());

while ($get_info = mysql_fetch_row($result)){
foreach ($get_info as $field)
if ($field == $userId){
while($get_info2 = mysql_fetch_row($result2)){
foreach ($get_info2 as $field2)
if($field2 == $ipassword){

setcookie("Username", $userId, time()+7200);
header('Location: index.php');
}
}
}
}
?>

[Demaestro]

You need to combine the select statment and pass in the vairables from the login form

SO:

$result = mysql_query( "SELECT User_id, User_password FROM $dbnametwo where password = $form_password and User_id = $form_User_id")or die("SELECT Error: ".mysql_error());

If result is null then no record in the database matched the user_name and password and you can deny login... if it returns a record then you know it matched.... You might want to look at some form of encryption for passwords as well...

[Darkstars31]

im a bit confused on how to use the 'where' instructions

SELECT User_id,User_password FROM $dbnametwo where password = $form_password and User_id = $form_User_id

This part : "where password = $form_password and User_id = $form_User_id"

where password = $ipassword AND User_id = $UserId? how does that compare them to see if they are the same? or does it and i just over looked it?

[Demaestro]

Let say this is your table

user_name ¦ password
************
user1 ¦ test
user2 ¦ poop
user3 ¦ code
user4 ¦ high

So lets say I am user1 and I want to login.

<form action="login">
<input type="text" name="form_user">
<input type="text" name="form_password">
</form>

I would put in "user1" and "test" into the form.

the select statement would look like this:

select * from user_table where user_name = "user1" and password = "test"

Now there is only one record in your database that would match both the criteria of the select statement.

Lets say they tried login in with a different password that was still in the database.

So in the form the put "user1" and "code"

the select statement would be:

select * from user_table where user_name = "user1" and password = "code"

Now this won't return anything.... the reason?.. no single row has a user_name of user1 with a password of code.

Think of the "where" clause as an if statement on the select...

So select * from user_table IF user_name = "user1" and IF password = "code"

I hope this makes sense.

[Darkstars31]

yea that makes more sence to me. Thanks for the help. Sorry if i sound like an amature, well i kind of am, but thats not the point.

Never used the where statement before.

[Demaestro]

Hey man don't worry about it.. we all learned some way.... I have been taken by the hand and walked through some stuff on here that I wasn't understanding by other members and I am always happy to pay the community back.

You will learn to love the "where statement" it is "where" it is at.

If you need help with anything else let me know... I love DB stuff.

[edited by: Demaestro at 8:13 pm (utc) on Sep. 7, 2007]

[Darkstars31]

Ok, so just so that im clear,

$result = mysql_query( "SELECT User_id,User_password FROM $dbnametwo WHERE User_password = $ipassword and User_id = $userId")

Does the $result end up being the 'null' value when the userid and passwords dont match any in the database or do i still need all my while loops and mysql_get_row() and foreach statements?

I don't mean for any one to write the script for me, but i just don't fully understand where all the values are and what i need exactly.

*Frowny face*

[Demaestro]

This is not perfect PHP so you will have to clean it up a bit but your SQL should only ever return 1 row or nothing at all.

so if the sql returns 1 row then results will be an sql_record_set.... if nothing then it will be null.

$result = mysql_query( "SELECT User_id,User_password FROM $dbnametwo WHERE User_password = $ipassword and User_id = $userId")

if (results!= null) {
..$row = mysql_fetch_row($result)
..$userId= $row['User_id']
..setcookie("Username", $userId, time()+7200);
} else {
..alert('login failed')
}

Does that make sense? If you can't get it working post back and I will slog through it with ya.

[edited by: Demaestro at 9:00 pm (utc) on Sep. 7, 2007]

[Darkstars31]

<?php
include 'config.php';
mysql_connect($dbhost,$user,$password);
@mysql_select_db($database) or die( "Unable to select database at "+ $dbhost);
$userId = $_POST['iUserName'];
$ipassword = $_POST['iPassword'];

$result = mysql_query( "SELECT User_id,User_password FROM $dbnametwo WHERE User_password = $ipassword and User_id = $userId")or die("SELECT Error: ".mysql_error());

if($result!= null)
{
$row = mysql_fetch_row($result);
$userId= $row['User_id'];
setcookie("Username", $userId, time()+7200);
header('Location: index.php');
}
else
{ alert('Login Failed');
}
?>
---------------Code Above-------------

Heres the error message i recieved
SELECT Error: Unknown column 'e3' in 'where clause'

Where to now? becuz my User_password = $ipassword is before the User_id = $userId? or that doesnt matter. I think that would be why i got the Unknown Column e3? or i got it cuz e3 is my current test password and its not a column? lawls confusion

[edited by: Darkstars31 at 9:34 pm (utc) on Sep. 7, 2007]

[Demaestro]

Could be some tpye of syntax problem..

I am not so great at PHP I do know SQL though.

It could be you have to concatenate the string...

So like:

$result = mysql_query( "SELECT User_id,User_password FROM " + $dbnametwo + " WHERE User_password = " + $ipassword + " and User_id = " + $userId)or die("SELECT Error: ".mysql_error());

You see how I pulled the variables out of the string and used the "+" operator to add the string together... I would try that first... maybe someone with some PHP syntax knowhow can tell us what the issue may be... It doesn't matter what order you pass them to the statement.

[edited by: Demaestro at 4:19 pm (utc) on Sep. 10, 2007]