CODEWITHSUNDEEP
phpmysql
Drew Peer
Drew Peer

Reputation: 377

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

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?

Upvotes: 3

Views: 100

Answers (1)

miken32
miken32

Reputation: 42753

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.

Upvotes: 2

Related Questions