How Can I retrieve a specific record from a database?

[stevex33]

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:

$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?

[AnthonySterling]

This should get you going.

<?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']
);
?>

[stevex33]

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.

[SJH]

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?

%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:

$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.

[AnthonySterling]

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).

[SJH]

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.

[stevex33]

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

[SJH]

Change:

$rResult = mysql_query($sSQL); 

to:

$rResult = mysql_query($sSQL) or die(mysql_error()); 

And paste the error in here

[mr.mstyle]

$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.

[stevex33]

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:

<?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.

[stevex33]

Got it working now, sorry for the silly error, newbie mistake

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

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