Single Value Mysqli

Question

I am trying to write a function that will check for a single value in the db using mysqli without having to place it in an array. What else can I do besides what I am already doing here?

function getval($query){
    $mysqli = new mysqli();
    $mysqli->connect(HOST, USER, PASS, DB);
    $result = $mysqli->query($query);
    $value = $mysqli->fetch_array;
    $mysqli->close();
    return $value;
}

Answer 1

How about

$name = $mysqli->query("SELECT name FROM contacts WHERE id = 5")->fetch_object()->name; 

Answer 2

The mysql extension could do this using mysql_result, but mysqli has no equivalent function as of today, afaik. It always returns an array.

If I didn't just create the record, I do it this way:

$getID = mysqli_fetch_assoc(mysqli_query($link, "SELECT userID FROM users WHERE something = 'unique'"));
$userID = $getID['userID'];

Or if I did just create the record and the userID column is AI, I do:

$userID = mysqli_insert_id($link);

Answer 3

Always best to create the connection once at the beginning and close at the end. Here's how I would implement your function.

$mysqli = new mysqli();
$mysqli->connect(HOSTNAME, USERNAME, PASSWORD, DATABASE);

$value_1 = get_value($mysqli,"SELECT ID FROM Table1 LIMIT 1");
$value_2 = get_value($mysqli,"SELECT ID FROM Table2 LIMIT 1");

$mysqli->close();

function get_value($mysqli, $sql) {
    $result = $mysqli->query($sql);
    $value = $result->fetch_array(MYSQLI_NUM);
    return is_array($value) ? $value[0] : "";
}

Answer 4

Here's what I ended up with:

function get_col($sql){
  global $db;
  if(strpos(strtoupper($sql), 'LIMIT') === false) {
    $sql .= " LIMIT 1";
  }
  $query = mysqli_query($db, $sql);
  $row = mysqli_fetch_array($query);
  return $row[0];
}

This way, if you forget to include LIMIT 1 in your query (we've all done it), the function will append it.

Example usage:

$first_name = get_col("SELECT `first_name` FROM `people` WHERE `id`='123'");

Answer 5

Even this is an old topic, I don't see here pretty simple way I used to use for such assignment:

list($value) = $mysqli->fetch_array;

you can assign directly more variables, not just one and so you can avoid using arrays completely. See the php function list() for details.

Answer 6

First and foremost,

Such a function should support prepared statements

Otherwise it will be horribly insecure.

Also, such a function should never connect on its own, but accept an existing connection variable as a parameter.

Given all the above, only acceptable way to call such a function would be be like

$name = getVal($mysqli, $query, [$param1, $param2]);

allowing $query to contain only placeholders, while the actual data has to be added separately. Any other variant, including all other answers posted here, should never be used.

function getVal($mysqli, $sql, $values = array())
{
    $stm = $mysqli->prepare($sql);
    if ($values)
    {
        $types = str_repeat("s", count($values));
        $stm->bind_param($types, ...$values);
    }
    $stm->execute();
    $stm->bind_result($ret);
    $stm->fetch();
    return $ret;
}

Which is used like this

$name = getVal("SELECT name FROM users WHERE id = ?", [$id]);

and it's the only proper and safe way to call such a function, while all other variants lack security and, often, readability.

Answer 7

This doesn't completely avoid the array but dispenses with it in one line.

function getval($query) {
    $mysqli = new mysqli();
    $mysqli->connect(HOST, USER, PASS, DB);
    return $mysqli->query($query)->fetch_row()[0];
}

Answer 8

Try something like this:

$last = $mysqli->query("SELECT max(id) as last FROM table")->fetch_object()->last;

Cheers