Okay - so I have a custom function
selectQuery($query, $fetch = 'fetch', $rowCount = 1,
$onlyRowCount = false, $outputerror = false)
Which works fine. However, I have one particular query where the fetch-method is fetchAll(PDO::FETCH_COLUMN,0), and if I pass that as the $fetch-value in the function-call and try to use that in the output, it just returns NULL(because it doesn't work - the query works fine if run directly, or hard-coded).
So, what I'm asking, is there a way to use a value of a variable (which, at least to start with, is a string) to assign a fetch method?
I've tried the following:
$stmt->fetchAll(PDO::FETCH_COLUMN,0); //works, but is hard-coded
$stmt->$fetch //returns NULL
$stmt->{$fetch} //returns NULL
So, is there a way to use that variable $fetch as the assignment directly? Casting the variable to something, or some other way?
This function is horrible. That long tail of parameters alone! And the lack of prepared statements. And a database related function that decides on its own whether to output an error or not, as though database interaction is something different from other code.
Do yourself a favor, make this function this way
function query($query, $parameters = []) {
$pdo = // here your way of getting a PDO instance.
// DON't TELL ME YOU ARE CREATING A NEW ONE EVERY TIME
if (!$parameters)
{
return $this->query($sql);
}
$stmt = $pdo->prepare($sql);
$stmt->execute($parameters);
return $stmt;
}
This is ALL you need and it's MUCH better than you have at the moment.
Returning a statement is the key. It lets you to attach any fetch method you like to the function call - the most natural way of getting different result types from such a function. Or not to fetch anything at all, if a query happens to be UPDATE or INSERT.
Want a row count?
$count = query("DELETE FROM usars")->rowCount();
want a fetch?
$user = query("select * from users where id=?", [$id])->fetch();
want fetchAll with PDO::FETCH_COLUMN? here you are
$users = query("select name from users")->fetchAll(PDO::FETCH_COLUMN);
Simple, usable, flexible, readable and secure.
If you don't know how to make this function connect only once, here is a link to a simple PDO wrapper I wrote. Note the examples section. It is so exciting that I'd better put it here:
# Table creation
DB::query("CREATE temporary TABLE pdowrapper
(id int auto_increment primary key, name varchar(255))");
# Prepared statement multiple execution
$stmt = DB::prepare("INSERT INTO pdowrapper VALUES (NULL, ?)");
foreach (['Sam','Bob','Joe'] as $name)
{
$stmt->execute([$name]);
}
$id = DB::lastInsertId());
# Getting rows in a loop
$stmt = DB::run("SELECT * FROM pdowrapper");
while ($row = $stmt->fetch())
{
echo $row['name'], PHP_EOL;
}
# Getting one row
$id = 1;
$row = DB::run("SELECT * FROM pdowrapper WHERE id=?", [$id])->fetch();
# Getting single field value
$name = DB::run("SELECT name FROM pdowrapper WHERE id=?", [$id])->fetchColumn();
# Getting array of rows
$all = DB::run("SELECT name, id FROM pdowrapper")->fetchAll(PDO::FETCH_KEY_PAIR);
# Update
$new = 'Sue';
$count = DB::run("UPDATE pdowrapper SET name=? WHERE id=?", [$new, $id])->rowCount();
I think you want to do a variable function call. But the problem here is that you pass the function name fetchAll with its parameters (PDO::FETCH_COLUMN,0) as one argument to your selectQuery function.
I think you need to pass the target function name as one argument, and the target function parameters as another argument.
selectQuery($query, $fetch = 'fetch', $rowCount = 1,
$onlyRowCount = false, $outputerror = false, $params= array())
{...
call_user_func_array($stmt->$fetch, $params);
...}
https://secure.php.net/manual/en/function.call-user-func-array.php
This works in my test to run a different fetch method per the argument:
function query($sql, $fetch) {
$pdo = new PDO('mysql:host=127.0.0.1;dbname=test', 'root', 'xxxx');
$stmt = $pdo->query($sql);
$result = $stmt->$fetch();
return $result;
}
(I'm not suggesting making a new connection for every query, that's just what I did for my test.)
However, trying to make a dynamic call to control the fetch mode and other arguments would be more complex. I'd suggest creating a callable:
function query($sql, $fetch, $setFetchModeArgs = null) {
$pdo = new PDO('mysql:host=127.0.0.1;dbname=test', 'root', 'masterkey');
$stmt = $pdo->query($sql);
if ($setFetchModeArgs != null) {
call_user_func_array([$stmt, "setFetchMode"], $setFetchModeArgs);
}
$result = $stmt->$fetch();
return $result;
}
query("SELECT ...", "fetchAll", [PDO::FETCH_COLUMN, 0]);
Anyway, that's how to make what you're trying to do work. But I don't think this is a good design.
After rewieving the answer by @Your Common Sense, I ended up with this function inside my DB-class:
public static function Query($query, $prepared = [], $outputerror = true) {
/* Lets a user run a query directly via $core->Query()
/* First variable is the actual query
/* Second variable is the variables array (for prepared queries) [':var1'=>$var1],[$var1,$var2] and so on
/* Third variable tells whether or not to output errormessages - default no (false)
/* Use the function like $row = $core->Query(); Minimum you need to enter $query */
$dbprefix = Config::read('dbprefix');
$core = Core::getInstance();
$stmt = '';
if (!empty($query)) {
if (!$prepared) {
$stmt = $core->dbh->query($query);
if ($stmt !== false) {
return $stmt;
} elseif ($outputerror === true) {
echo displayErrors($stmt, true, __FILE__);
}
} else {
$stmt = $core->dbh->prepare($query);
if ($stmt !== false) {
$stmt->execute($prepared);
} elseif ($outputerror === true) {
echo displayErrors($stmt, true, __FILE__);
}
}
return $stmt;
}
} // end function Query
Works perfectly, involves a bit of a rewrite in my other files, but that was expected
I've rewarded the answer to @Your Common Sense