Reading Single Row from DB

best method

[cyberjunky]

hi guys,

for my site i need to make a profile section and up until now my MySQL reading methods have been working fine, and for my profile piece to work i need to pull out just one record. i use the following code but nothing works, whats the proper way to do this?

$query="SELECT * FROM Users WHERE Username='$user'";
$result=mysql_query($query) or die("Could not Get Profile Data using Username:$user <br>". mysql_error() );
$num=mysql_numrows($result);

then i use for example:
$username=mysql_result($result,"Username");

can anyone help me out...?
Thnx in advance, Cyberjunky

[TheBlueEyz]

Here's the best way to do that:

(note the LIMIT 1 part! very important!)

$query="SELECT * FROM Users WHERE Username='$user' LIMIT 1";
$result=mysql_query($query);

if(mysql_num_rows($result) > 0)
{
$userinfo = mysql_fetch_array($result);
}
else
{
echo "Invalid username"; // or whatever
}

The LIMIT 1 does two things for you:

1. Makes sure you don't waste precious CPU time by selecting rows you're not interested in
2. Protects you from dangerous sql injection attacks by reducing the amount of damage an attack can do. Better to return only ONE record in a hack job instead of ALL your records.

Edited to add additional note:

Whether or not you want to use "SELECT *" depends on your needs. If you really need to pull out ALL their information, by all means use *. Otherwise, only select the column you need:

$query = "SELECT username,email from Users where Username='$user' LIMIT 1";

[cyberjunky]

kool, but how do i select a particular item?

because if im using:
$userinfo = mysql_fetch_array($result);
then how do i make it do this:
$email = mysql_fetch_array($result);
$nickname = mysql_fetch_array($result);

how do i make this work as the array isnt told what field to get from the record..?

[TheBlueEyz]

Oh ok I see -

Well, you should read up on how mysql_fetch_array works.

Basically, it reads the sql row into an associative array, keyed by column name.

So if you have the following columns in your table:

username
email
name
birthday

$info = mysql_fetch_array($result);

returns an array called "$info". If you were to print out $info, it would look like:

info
{
[username]=>SomeonesUsername
[email]=>[e-mail]
[name]=>John Doe
[birthday]=>06.03.82
}

So you can assign those variables by doing this:

$info = mysql_fetch_array($result);

$username = $info['username']; //$username now equals 'SomeonesUsername'
$email = $info['email']; //email now equals '[e-mail]'
$name = $info['name']; //name now equals John Doe
$birthday = $info['birthday']; // birthday now equals..

Etc. :)

[coopster]

Welcome to WebmasterWorld, cyberjunky!

You were using the PHP mysql_result() [php.net] function incorrectly in this case. Indeed, mysql_result() returns the contents of one cell (column) from a MySQL result set, however, the second parameter is not optional and must be an integer that represents the row of the result set from which you want to return a column. You are missing this parameter. Well, actually you aren't missing it, but PHP is converting that string to a zero [php.net] and using that as the row, and then returning the very first column (or cell) of the row from the corresponding result set (offset zero) because you haven't specified the third argument. This works fine when selecting a single column to be returned in a query, such as

SELECT username FROM ...

, but when you want more than one column returned such as

SELECT * FROM ...

, you are going to get unexpected results. Here is the correct syntax to return your

$username

variable using this function:

$username=mysql_result($result, 0, "Username"); // Rows start at zero

Also, in your particular case, you should only have one row, assuming that your

Users

table contains only unique user names. If this is so, the

LIMIT

clause is not necessary.

TheBlueEyz has recommended using mysql_fetch_array() [php.net] as opposed to mysql_result(), which is a good recommendation because it is a higher-performance alternative. That, and the PHP developers recommend against using mysql_result() with calls to other functions that deal with the result set. The difference is that mysql_fetch_array() returns a result row as an associative array, a numeric array, or both, depending on whether or not you have passed the second optional parameter. mysql_fetch_assoc() [php.net] returns only an associative array as explained.

One thing you may want to do is play around a bit with the different mysql_fetch_*() functions to see how they return data differently. Here is a small snippet you can start with.

$query="SELECT * FROM Users WHERE Username='$user'"; 
$result=mysql_query($query); 
print '<pre>'; 
print_r(mysql_fetch_row($result));  
mysql_data_seek($result, 0);  
print_r(mysql_fetch_assoc($result));  
mysql_data_seek($result, 0);  
print_r(mysql_fetch_array($result)); 
print '<pre>';

Think of the result set ($result) returned by any mysql_query() as a "spreadsheet" of information in rows and columns.

[cyberjunky]

thnx a lot, it works properly now, much appreciated.