CODEWITHSUNDEEP
phpmysql
Mike
Mike

Reputation: 23

Set variable from SELECT PHP

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'";

Upvotes: 2

Views: 27380

Answers (6)

Anriëtte Myburgh
Anriëtte Myburgh

Reputation: 13517

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.

Upvotes: 1

Your Common Sense
Your Common Sense

Reputation: 157872

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];
}

Upvotes: 2

Miron Vranješ
Miron Vranješ

Reputation: 300

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.

Upvotes: 5

Sebastian Paaske Tørholm
Sebastian Paaske Tørholm

Reputation: 50858

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:

Upvotes: 6

Dan Grossman
Dan Grossman

Reputation: 52372

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

Upvotes: 9

Or Weinberger
Or Weinberger

Reputation: 7472

Assuming there is only 1 result:

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

Upvotes: -3

Related Questions