What's the proper way to return a single database result?

Question

All I want to do is get the firstname result from this function, but it feels it's too much code to do that based on session id.

//query_functions.php

function find_name_by_id($id) {
    global $db;

    $sql = "SELECT firstname FROM admins ";
    $sql .= "WHERE id='" . db_escape($db, $id) . "' ";
    $sql .= "LIMIT 1";
    $result = mysqli_query($db, $sql);
    confirm_result_set($result);
    $name = mysqli_fetch_assoc($result); // find first
    mysqli_free_result($result);
    return $name; // returns an assoc. array
  }

// admin.php

id = $_SESSION['admin_id'];
$name = find_name_by_id($id);

// what is the shortest way to get this $name result?

Answer 1

To do this properly using prepared statements you actually need more code than that:

function find_name_by_id($db, $id) {
    $stmt = $db->prepare("SELECT firstname FROM admins WHERE id=?");
    $stmt->bind_param("i", $id);
    $stmt->execute();
    $result = $stmt->get_result();
    $row = $result->fetch_assoc();
    $stmt->free_result();
    return $row[0];
}

I'm not sure what confirm_result_set is so I left it out.

Let's pretend that $db was a PDO object:

function find_name_by_id($db, $id) {
    $stmt = $db->prepare("SELECT firstname FROM admins WHERE id=?");
    $stmt->execute([$id]);
    return $stmt->fetchColumn();
}

Much less code involved. And for a higher-level API this will be abstracted to a single line of code.

In reality for all cases you'd want to do some error checking, account for no records being returned, etc. Also you should avoid global variables, they're very poor form. Put your code into a class or just use dependency injection as I've done.