Help pulling specific data from MYSQL

...selecting one item...

[mjar81]

i need help with the syntax of taking one item out of a mySQL database.

here's what i need to do:

1. get a user inputted variable
2. take a database and select the row with that variable
3. find another attribute from that specific row

like i have a database with usernames and passwords.

i want the user to input the username (i will handle this later) and the php script will go to mySQL and find the password that is associated with that particular username.

here's what i have and it won't return any results... if anyone could help, please let me know what i'm doinf wrong and how i can fix it.

$user = "input_from_user";
//connect to the database "localhost" using the supplied
//username and password and assign it the variable $db
$db= mysql_connect('localhost', '****xxxx', 'xxxxxx')
  or die("Cant connect: " .mysql_error());
//select the database "prayer" from mySQL 
mysql_select_db("prayer", $db);
$sql = mysql_query ('SELECT password FROM prayer_users WHERE username="' . $user . '"', $db); 
$result = mysql_query($sql); 
echo "Username: ";
echo $user;
echo "<br />Password: ";
echo $result;

[Timotheos]

Hi Mjar81,

There's a great post here [webmasterworld.com] that goes through the basics of extracting data from MySQL. One thing you're missing is the use of mysql_fetch_array [us2.php.net]. So the last part of your script might look like this.

$result = mysql_query($sql);
$row = mysql_fetch_array ($result);
echo "Username: ";
echo $user;
echo "<br />Password: ";
echo $row['password'];

[willybfriendly]

mjar81 -

It helps to conceptualize what is happening on the server.

$result = mysql_query($sql); runs the query and stores the result in memory. The variable $result simply points to where the results of that query are stored.

The following function (mysql_fetch_array($result), mysql_fetch_object($result), etc.) then makes the data in memory available for use in your script. Since the data in memory is stored in tabular form, the variable name $row is convenient to use to pull it out of memory.

Thus, the statements,

$row = mysql_fetch_array($result);
extract($row);

converts the data in that row of the table which is stored in memory available as regular assigned variables, i.e. the column name of the table in memory (e.g. 'city') becomes the variable name, so $city may now equal 'San Diego' if that was what was returned.

Now you can use the variable $city just as you would any other assigned variable.

Summary:

mysql_pconnect() establishes a connection to the database. (First you look up the number of the Pizza place.)

mysql_select_db() chooses the database to use. (Then you call the right Pizza place)

mysql_query() builds a temporary table in the server's memory based on the query statement and returns a resource ID (a pointer if you would). (Now you place your order with the guy at the Pizza place so he can start making it.)

mysql_fetch_array() returns a row of that table for use in the script. (Finally, you drive down to the Pizza place and pick up your order.)

Takes all four steps if you expect to eat the kind of pizza you were expecting;)

WBF