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

Answers


How about

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

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

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

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

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.


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.


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

Try something like this:

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

Cheers


Need Your Help

Scala to JSON in Play Framework 2.1

json scala playframework-2.1

I'm trying to convert Scala to JSON in the 2.1RC Play Framework.

OpenXML SDK: Make Excel recalculate formula

.net excel openxml openxml-sdk

I update some cells of an Excel spreadsheet through the Microsoft Office OpenXML SDK 2.0. Changing the values makes all cells containing formula that depend on the changed cells invalid. However, d...