PHP: Making this grabbing from db easier/simpler way?

Question

Is there any method i could do this easier:

$query = mysql_query("SELECT full_name FROM users WHERE id = '$show[uID]'");
$row = mysql_fetch_array($query);
echo $row["full_name"] . " ";

as i only need to grab the full_name, then i make a var for the fetch_array and so, is there any way to make this simpler and echo? There was something about list(), but im not sure..

Answer 1

May be not easier, but more securely:

$id = mysql_real_escape_string($show['uID']);
$query = mysql_query("SELECT `full_name` FROM `users` WHERE id = '".$id."'");
$row = mysql_fetch_array($query);
echo $row['full_name'];

Oh you can to make id with intval:

$id = intval($id);

Answer 2

The only way to abstract this any more and thereby make the actual call shorter is by using a DAL and/or ORM like Doctrine or Propel, which you should anyway.

Answer 3

This could make further DB-questions easier;

function mysql_fetch_scalar($res)
{
    $arr = mysql_fetch_array($res);
    return $arr[0];
}

$query = mysql_query("SELECT full_name FROM users WHERE id = '".intval($show[uID])."'");
$fullname = mysql_fetch_scalar($query);
echo $fullname . " ";

Answer 4

Sure.
Moreover, you should - to make a function out of these repetitive API functions calls.
Something as simple, as this

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

have this function in your config file and use every time you want a value from database:

echo dbgetval("SELECT full_name FROM users WHERE id = '$show[uID]'");

(I hope you have $show[uID] escaped)

Of course there can be also 2 similar functions, to return a row or a rowset. Or just one but with additional parameter. Or you can combine them into class...

You can make it even escape variables for you:

function dbgetvar(){
  $args = func_get_args();
  $query = array_shift($args);
  foreach ($args as $key => $val) {
    $args[$key] = "'".mysql_real_escape_string($val)."'";
  }
  $query = vsprintf($query, $args);
  if (!$query) return false;

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

echo dbgetvar("SELECT full_name FROM users WHERE id = %s",$show['uID']);

Answer 5

Ignoring possible security breaches and the usefulness of a DAL (see @deceze's answer), I recommend the use of mysql_result() instead of mysql_fetch_assoc() (or *_array() or whatever):

$query = mysql_query("SELECT full_name FROM users WHERE id = '$show[uID]'");
$fullName = mysql_result($query, 0);
echo $fullName . " ";

Not easier per se but should be more in line with the intention of the query (fetch one field in one row).

Answer 6

That's what you have to do. You could wrap that in a helper function if you're using it a fair bit, but then you'd probably want to cache the answer you get - I don't suppose the name changes all that often...

function echoName($user_id) {
    $id = mysql_real_escape_string($user_id);
    $query = mysql_query("SELECT full_name FROM users WHERE id = '$id'");
    $row = mysql_fetch_array($query);
    echo $row["full_name"] . " ";
}

// ...

echoName($show['uID']);