Set variable from SELECT PHP

Question

I simply want to define the variable "$read" as whatever its value is in the database. How can I do this?

$read =  "SELECT `read` FROM `users` WHERE `id` = '$id'";

Answer 1

$read = mysql_result(mysql_query("SELECT read FROM users WHERE id = $id"),0);

Answer 2

Beware of the answers given using mysql_query, as they're vulnerable to SQL injection.

If $id is supplied by a user, you should never directly put it into the SQL query, but rather use a prepared statement.

One way of doing this, is by using PDO, in a manner similar to this:

$dbh = new PDO($connStr, $user, $pass);
$sql = "SELECT `read` FROM `users` WHERE `id` = :id";
$statement = $dbh->prepare($sql);
$statement->execute( array('id' => $id) );
$read = $statement->fetchColumn();

For more information on how to use PDO, see the following:

• Are there good tutorials on how to use PDO?

Answer 3

One way to accomplish this is as follows:

// Run the query
$db_result = mysql_query("SELECT read FROM users WHERE id = $id");
// Get the first row (in this case you'll only get one row)
$row = mysql_fetch_array($db_result, MYSQL_NUM);
// Get the first column (you should only have one column anyway) and put it into your variable
$read = $row[0];

As pointed out below, I should add that if you don't trust $id to be properly escaped, you could be vulnerable to SQL injection. To overcome this, you should either make sure you properly escape and validate $id or use some kind of binding or prepared statement to do it for you, like in this question or in the example below.

Answer 4

I know it's almost impossible to teach someone something, especially if they don't want to learn. But in hope it will be useful for someone else

All modern programming languages supports such a thing called "user defined functions".
A very handy feature.

A programmer, who wants to have their code real neat, can make a function out of some repetitive code and make calling this code REAL small, just almost as it was phrased in the OP:

$read = dbgetvar("SELECT `read` FROM `users` WHERE `id` = %d",$id);

another benefit from such an approach - your code could contain all necessary things, like parameter sanitization and error handling. And still calling this code would be shorter than all codes above, made ugly and unmantainable in pursue for shortness.

An example of such a function

function dbgetvar(){
  $args = func_get_args();
  $query = array_shift($args); 
  $query = str_replace("%s","'%s'",$query); 
  foreach ($args as $key => $val) { 
    $args[$key] = mysql_real_escape_string($val); 
  } 
  $query = vsprintf($query, $args);

  $res = mysql_query($query);
  if (!$res) {
    trigger_error("dbget: ".mysql_error()." in ".$query);
    return FALSE;
  }
  $row = mysql_fetch_row($res)
  if (!$row) return NULL;
  return $row[0];
}

Answer 5

I would do the following:

// leave the single quotes around $id because it most probably is an INT
// LIMIT 1 will make the query a bit faster
$result = mysql_query("SELECT `read` FROM `users` WHERE `id` = $id LIMIT 1");

$row = mysql_fetch_row($result);
$read = $row[0];

Hope it works for you.

Answer 6

Assuming there is only 1 result:

$read = mysql_fetch_array(mysql_query("SELECT read FROM users WHERE id = $id"));
$read = $read[0];