SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Addict
    Join Date
    Aug 2007
    Posts
    328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How Can I retrieve a specific record from a database?

    Hi Guys,
    Pretty basic question.
    I simply need to look in a table for a value that a website visitor has supplied.
    Specifically, it's a login system, when someone tries to login, I want to search the database for what they've typed in.

    I've had a quikc look on the internet and can only find references to code that returns all records in a database


    Here's the code I've got so far:

    Code:
    $username=$_POST['username'];
    $password=$_POST['password'];
    
    $userIsEmpty = empty($username);
    $passIsEmpty = empty($password);
    
    if(($userIsEmpty || $passIsEmpty) == 1)
    	echo "Please fill in a username and password <br />";
    else{
    	echo "Thankyou for properly filling in the form <br />";
    };
    
    echo "Username is ".$userIsEmpty;
    echo "<br />";
    echo "Password is ".$passIsEmpty . "<br />";
    
    
    //mysql_real_escape_string($username);
    //mysql_real_escape_string($password);
    
    $query = "SELECT * from users where username=".$username;
    $result = mysql_query($query);
    echo "Name :{$row['username']} <br>" .
    "password :{$row['password']}";
    I'm pretty such it must be a problem with the "$result" variable, as everything else seems to work fine.

    I'm fairly confident with doing simple retrievals from sql databases, and all I'm saying is return as a result everything from the "users" table where the column "username" is equal to the users input in the variable $username.

    What have I missed?

  2. #2
    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)
    This should get you going.
    PHP Code:
    <?php
    $sSQL 
    sprintf(
        
    "SELECT password FROM users WHERE username = '%s'",
        
    mysql_real_escape_string($_POST['username'])
    );
    $rResult mysql_query($sSQL);
    $aRecord mysql_fetch_assoc($rResult);
    printf(
        
    'The password for user %s is %s.',
        
    $_POST['username'],
        
    $aRecord['password']
    );
    ?>
    @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.

  3. #3
    SitePoint Addict
    Join Date
    Aug 2007
    Posts
    328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the quick reply.

    Could you please explain the code.
    What is '%s' for example?

    Is it a sort of variable/placeholder for $_POST['username'], i.e. $_POST['username'] goes here?

    Why can't we put it in the first part, where you've got '%s'?
    Is that just how it works? Or are there alternatives?

    So $sSQL is the query, $rRESULT is saying "This is a mysql query"
    And $aRECORD is returning an associative array of the results?

    And the result of the print statement would be "the password for user 'john' is 'smith'." for example?

    What would be displayed if 2 records or more were found?

    Sorry for all the questions, I just want to get a firm grip on the basics.

  4. #4
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by stevex33 View Post
    Could you please explain the code.
    What is '&#37;s' for example?

    Is it a sort of variable/placeholder for $_POST['username'], i.e. $_POST['username'] goes here?

    Why can't we put it in the first part, where you've got '%s'?
    Is that just how it works? Or are there alternatives?
    %s acts as a placeholder for $_POST['username'], correct. Have a look at the sprintf manual page for a full explanation of how this function works.

    It's just a way of doing it, which some folks say looks cleaner and makes the whole thing a bit easier to understand. Mr SilverBullet is known around these parts for being a bit of an sprintf junkie

    The other way of doing it would be something along these lines:

    PHP Code:
    $sSQL 'SELECT password FROM users WHERE username = \''mysql_real_escape_string($_POST['username']) .'\''
    Can you see why SilverBulletUK opted for the first method? The one I just posted gets very confusing, especially when you've got to escape the quotes for the query and break out of the quotes to include the username.

    So $sSQL is the query, $rRESULT is saying "This is a mysql query"
    And $aRECORD is returning an associative array of the results?
    Pretty much, yeah, $rResult is telling the MySQL server to execute $sSQL. $aRecord is an associative array containing the first row returned by the query.

    And the result of the print statement would be "the password for user 'john' is 'smith'." for example?
    Yes indeed

    What would be displayed if 2 records or more were found?
    Just the first result.

  5. #5
    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)
    Quote Originally Posted by SJH View Post
    Mr SilverBullet is known around these parts for being a bit of an sprintf junkie.


    I forgive you Sam, although these slanderous comments of yours will be noted in my 'ickle book for future libel case(s).
    @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.

  6. #6
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SilverBulletUK View Post


    I forgive you Sam, although these slanderous comments of yours will be noted in my 'ickle book for future libel case(s).
    I don't think I've seen a single post by you that hasn't used sprintf in some form You've actually got me using it now and my code is looking much cleaner as a result, so I can only thank you.

  7. #7
    SitePoint Addict
    Join Date
    Aug 2007
    Posts
    328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    With your code I get the following error:
    "Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource on line 30"

    Line 30 is:
    "$aRecord = mysql_fetch_assoc($rResult);"

    What's wrong with it?

    I have a user called john, and a password of smith, but when I try to retrieve the record via your method, it returns the string, but with a blank space for the password

  8. #8
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Change:

    PHP Code:
    $rResult mysql_query($sSQL); 
    to:

    PHP Code:
    $rResult mysql_query($sSQL) or die(mysql_error()); 
    And paste the error in here

  9. #9
    SitePoint Enthusiast
    Join Date
    May 2009
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SJH View Post
    PHP Code:
    $sSQL 'SELECT password FROM users WHERE username = \''mysql_real_escape_string($_POST['username']) .'\' LIMIT 1'
    You can use LIMIT 1, to just return 1 result. It will make the execution a bit faster as well.

  10. #10
    SitePoint Addict
    Join Date
    Aug 2007
    Posts
    328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've changed the line of code you suggested and the error I've got is:
    "No database selected"

    I specified this in a seperate dc connect php file.

    Can anyone shed any light on this?

    ****EDIT***
    I've just looked at my dbconnect.php file and it doesn't have a database name in there, (well it does, but it was commented out). Can someone explain how I should properly implement this connection please?

    Here's the dbconnect.php file code:
    Code:
    <?php
    $username = "test";
    $password = "test";
    $hostname = "localhost:/tmp/mysql5.sock"; 
    
    //connection to the database
    $dbhandle = mysql_connect($hostname, $username, $password)
      or die("Unable to connect to MySQL");
    echo "Connected to MySQL<br>";
    
    //select a database to work with
    $selected = mysql_select_db("makeagre_test",$dbhandle)
      or die("Could not select examples");
    ?>
    What I'm a little unsure of is where I'm supposed to say "connect to this database", "select from that table" etc, I don't know how I should be separating these bits of code.

    Any help would be greatly appreciated.

  11. #11
    SitePoint Addict
    Join Date
    Aug 2007
    Posts
    328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Got it working now, sorry for the silly error, newbie mistake

    I put in the following and it gave me the result I expected:

    Code:
    //select a database to work with
    $selected = mysql_select_db("makeagre_test",$dbhandle)
      or die("Could not select examples");


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
  •