Single Value Mysqli

问题:

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

回答1:

How about

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

回答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);

回答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] : "";
}

回答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'");

回答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.

回答6:

Although there are many flaws in your implementation (repeated connect alone!) as well as in other answers (a one-liner at the cost of moving parameters off screen?), there is an essential one:

Such a function should have support for Prepared Statements

Otherwise it will be horribly insecure.

Thus, the only acceptable way to call such a function will be

$name = getVal($query, $param1, $param2);

or

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

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

Assuming the fact that we need other functions of the kind (like getRow(), getAll()), it would be logical to combine them all in a class. For mysqli you can use my safeMysql wrapper:

$name = $db->getOne("SELECT name FROM users WHERE id = ?i", $id);

as you can see, it has extremely concise syntax and perfectly safe.

If you don't want to use third-party wrappers, then I'll strongly advise you to use PDO as a backing API, simply because mysqli-based implementation would result in an insane amount of code. Just compare these 2 functions, one using mysqli:

function getVal($sql, $values = array())
{
    global $mysqli;

    $stm = $mysqli->prepare($sql);

    if ($values)
    {
        $types = str_repeat("s", count($values));

        if (strnatcmp(phpversion(),'5.3') >= 0)
        {
            $bind = array();
            foreach($values as $key => $val)
            {
                $bind[$key] = &$values[$key];
            }
        } else {

            $bind = $values;
        }
        array_unshift($bind, $types);
        call_user_func_array(array($stm, 'bind_param'), $bind);
    }
    $stm->execute();
    $stm->bind_result($ret);
    $stm->fetch();
    return $ret;
}

And one using PDO:

function getVal($query, $params = array())
{
    global $pdo;
    $stmt = $pdo->prepare($query);
    $stmt->execute($params);
    return $stmt->fetchColumn();
}

and you can clearly see the difference.

Either way, you'll end up using this function like this

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

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

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

回答8:

Try something like this:

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

Cheers