URGENT - cannot grab ID from database - prepared statements

[ab24]

Hello everyone, i hope another set of eyes will help me figure out why this isn't working. I am writing a job application site, using the basis of a login system from nettuts.com (which can be found here http://is.gd/4j6T6)

I am trying to grab the ID of the user currently logged in, i use sessions to define if a user is logged in or not, and store the username and password in variables named $currentUN and $currentPWD respectively, i want to get the id and put it in a variable called $currentID.

$currentUN = $_SESSION['username'];
$currentPWD = $_SESSION['password'];
            
$mysql = new mysqli(DB_SERVER, DB_USER, DB_PASSWORD, DB_NAME) or die('There was a problem connecting to the database');
$stmt = $mysql->prepare('SELECT DISTINCT id FROM users WHERE username=? AND password=? LIMIT 1');
$stmt->bind_param('ss',$currentUN, $currentPWD);
$stmt->execute();
$stmt->bind_result($currentID); 

I cannot stress how urgent this is, appreciate any help you guys can offer.
Thx in advance,

(a very weary and tired) Aaron

/update/ The current script always returns 0, not the correct ID

[9three]

echo out those variables make sure they are actually containing what you need it to contain to pull the correct information from the database.

Use mysqli_stmt_error() to check for error messages.

[ab24]

I have the variables being echoed out already, there are showing the correct values.
This one really has me beat at the mo

[SituationSoap]

$currentUN = $_SESSION['username'];
$currentPWD = $_SESSION['password'];
            
$mysql = new mysqli(DB_SERVER, DB_USER, DB_PASSWORD, DB_NAME) or die('There was a problem connecting to the database');
$stmt = $mysql->prepare('SELECT DISTINCT id FROM users WHERE username=? AND password=? LIMIT 1');
$stmt->bind_param('ss',$currentUN, $currentPWD);
$stmt->execute();
$stmt->bind_result($currentID); 
$stmt->fetch(); 

Try that. You bound the results of the query, but you never actually fetched them. That's an issue which has stumped me before, as well.

Also, not a bad idea to check to make sure your SQL is valid, if you're still having errors. We can't confirm that for you.

[ab24]

@SituationSoap - I LOVE YOU!
worked a treat, thx ever so much mate. i believe the saying 'so near, yet so far' would fit here, it's so helpful to get a second pair of eyes on code sometimes i find.
thx

Aaron

[r937]

i love the cross-your-fingers-and-hope-like-hell attitude in that SELECT statement

it's not bad enough that multiple rows in the users table can have the same username and password, but multiple rows with the same id can have the same username and password, and then of course let's use LIMIT 1 so that we pick up only the first of these

hopefully this weak SQL is due to the tutorial you worked from, and not your own approach

[ab24]

i love the cross-your-fingers-and-hope-like-hell attitude in that SELECT statement

it's not bad enough that multiple rows in the users table can have the same username and password, but multiple rows with the same id can have the same username and password, and then of course let's use LIMIT 1 so that we pick up only the first of these

hopefully this weak SQL is due to the tutorial you worked from, and not your own approach

I have ajax checks against the db prior to a user signing up, usernames and password combos MUST be unique although i concur with your point.

Awesome book by the way R, it never leaves my side on projects, quite surreal having the author of the book in front of me replying to one of my threads thx for the input.

A

[r937]

thanks for the kind words, aaron

the best way to ensure that username is unique is ~not~ to do a SELECT first, but simply to have a UNIQUE key on it and then go ahead and do the INSERT

when new users are added, the INSERT statement will fail with a "duplicate key" error, which you can trap

[ab24]

i will give it a go R, my sql skills are ok (always room for improvement) but will see how i get on.
On another note, if you are planning on another sql sitepoint book, ( which i hope u are ) i came up with a possible title...
'SIMPLY SQL....the sequel'
thoughts?

[AnthonySterling]

Off Topic:

On another note, if you are planning on another sql sitepoint book, ( which i hope u are ) i came up with a possible title...

'SIMPLY SQL....the sequel'

*chuckles*

[r937]

'SIMPLY SQL....the sequel'
thoughts?

cute, but i try to stay away from that particular word, as it has nothing to do with sql

[ab24]

true, was meant as jest, i jus couldn't resist writing it.
Thx for all input.
take care,

A