SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation URGENT - cannot grab ID from database - prepared statements

    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.

    PHP Code:
    $currentUN $_SESSION['username'];
    $currentPWD $_SESSION['password'];
                
    $mysql = new mysqli(DB_SERVERDB_USERDB_PASSWORDDB_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
    Last edited by ab24; Oct 14, 2009 at 05:06. Reason: update status

  2. #2
    Non-Member
    Join Date
    Oct 2008
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have the variables being echoed out already, there are showing the correct values.
    This one really has me beat at the mo

  4. #4
    SitePoint Addict
    Join Date
    Apr 2009
    Posts
    248
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $currentUN $_SESSION['username'];
    $currentPWD $_SESSION['password'];
                
    $mysql = new mysqli(DB_SERVERDB_USERDB_PASSWORDDB_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.

  5. #5
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    @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

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  10. #10
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Off Topic:


    Quote Originally Posted by ab24 View Post
    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*
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ab24 View Post
    'SIMPLY SQL....the sequel'
    thoughts?
    cute, but i try to stay away from that particular word, as it has nothing to do with sql

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    true, was meant as jest, i jus couldn't resist writing it.
    Thx for all input.
    take care,

    A


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •