I want to do something like this:
$query=mysql_query("SELECT userid FROM users WHERE username='$username'");
$the_user_id= .....
Because all I want is the user ID that corresponds to the username.
The usual way would be that:
$query=mysql_query("SELECT * FROM users WHERE username='$username'");
while ($row = mysql_fetch_assoc($query))
$the_user_id= $row['userid'];
But is there something more efficient that this? Thanks a lot, regards
You've hit the nail right on the head: what you want to do is SELECT userid FROM users WHERE username='$username'. Try it - it'll work :)
SELECT * is almost always a bad idea; it's much better to specify the names of the columns you want MySQL to return. Of course, you'll stil need to use mysql_fetch_assoc or something similar to actually get the data into a PHP variable, but at least this saves MySQL the overhead of sending all these columns you won't be using anyway.
As an aside, you may want to use the newer mysqli library or PDO instead. These are also much more efficient than the old mysql library, which is being deprecated.
The "usual" way is to only select the columns that you need.
Any column that is part of the select and is not needed will slow down the retrieval. Partly because of network traffic and partly because the database engine can apply certain optimizations if only some columns are selected.
And select * is usually frowned upon in production code.
Your query is correct:
$query = mysql_query("SELECT userid FROM users WHERE username='$username'");
This will work if done correctly. Note that $username must be correctly escaped with mysql_real_escape_string. I'd recommend looking at parameterized queries to reduce the risk of introducing an SQL injection invulnerability.
One other thing you can change is to use mysql_result instead of your loop:
$the_user_id = mysql_result($result, 0);
This assumes that know you'll get only one row.
Not only can you, but you should. Unfortunately too many young developers get in the habit of selecting far too much data. Once you run your query, you don't need to do a while loop since you only have one record coming back. Instead, just use the following:
$sql = sprintf( "SELECT userid FROM users WHERE username = '%s'", $username );
$result = mysql_query( $sql ) or die( mysql_error() );
$data = mysql_result( $result, 0 );
if ( $data ) echo $data; // first userid returned
The second parameter of mysql_result() is the row index you would like to retrieve. 0 is first.
Yes, that's a great optimization practice!
Also, if you want to go a step further and make it super-optimized, try adding a LIMIT, like this: $query=mysql_query("SELECT userid FROM users WHERE username='$username' LIMIT 1");
This, of course, assumes you expect only one userid to be returned and will make the database engine stop once it finds it.
But, the most important thing: ALWAYS ESCAPE/SANITIZE YOUR INPUT DATA! (can't stress this well enough)
You can do this with a single line of code
echo array_values(mysqli_fetch_array($mysqli->query("SELECT name FROM user WHERE id='$userid'")))[0];
You must connect to the database as shown below before using this
$mysqli = new mysqli('HOST', 'USERNAME', 'PASSWORD', 'DATABASE');
Credits to @samuel t thomas