In addition to what I said in the comments, I would like to add that writing scripts in procedural form using the PDO
, It can be a little more complicated if you’re using related functions. In the examples you put in your question, you omitted the first part in all the examples, leaving the problem vague.
Another thing is the problem. Like you said yourself, you’re concatenating parts to get a queryString end that is executed without any problems.
Though you did quote «I’m picking up function parameters (column, Where, order, etc.)», was unclear about what you were referring to, and at this point I still don’t know if spine or Where are individual functions or arguments of a single function. At least you can see that you are not using OOP
, but it’s still unclear if you’re writing code by blocks grouped into functions or blocks separated by different files.
Anyway I will answer the question with an example, although I have made use of functions and have broken the code in different parts to increase readability as much as possible, I hope that it complies with the intended.
A priority factor is the handling of errors, launched through exceptions by PDO
, however I only dealt with the errors while connecting to the server. For this we made something simple, we made use of a closure, and also set 2 variables crucial to the script.
connect
$prepare = $rows = "";
$pdo = function ()
{
try{
$pdo = new PDO('mysql:host=localhost;dbname=example;charset=utf8;', 'root', '');
return $pdo;
} catch(PDOException $e){
die($e->getMessage());
}
};
The second thing to be done was to create a function that could execute any query SQL
provided to you, and returns true, if such consultation is carried out.
execute any query
function query($sql, $parametros)
{
global $pdo;
global $prepare;
if($prepare = $pdo()->prepare($sql))
{
foreach($parametros as $key=> &$valor){
$prepare->bindParam(":{$key}", $valor);
}
//return $prepare->debugDumpParams();
if($prepare->execute()){
return true;
}
}
return false;
}
So far everything is normal and working, but still, the idea remains vast, both for different types of consultation - select, delete, Insert, update - as for the various clauses and other functions that can be executed in the final query through this function query
. What to do is obvious, distribute tasks, creating third parties capable of providing queryString in a state where the query function will only have to execute, regardless of what comes through it.
run queries of type select and delete
function accao($accao, $tabela, $argumentos = array())
{
if(!empty($accao) && !empty($tabela) && !empty($argumentos)){
$campo = $argumentos[0];
$valor = strtoupper($argumentos[1]) === "LIKE" ? array($campo=>"%{$argumentos[2]}%") : array($campo=>$argumentos[2]);
$operador = $argumentos[1];
$order = isset($argumentos[3]) ? order($accao, $argumentos[3]) : order($accao);
$marcador = strtoupper($operador) === "IN" ? '(' . parametros($valor, 'param', true) . ')' : parametros(array($campo=>$valor), 'param', true);
$sql = "{$accao} FROM {$tabela} WHERE {$campo} {$operador} {$marcador} {$order}";
if(query($sql, $valor)){
return true;
}
}
return false;
}
This function receives a command accao
- select, Insert, delete, update - and analyses the remaining parameters for the queryString based on that action, and puts them as part of it, so you always get a queryString valid.
As part of this function we also have the function order
, which only resolves the question of order, and works only if the consultation concerns the SELECT
, otherwise no additional parameter is returned - «empty string ».
ordain
function order($args, $set = null)
{
if(strtoupper($args) === "SELECT" || "SELECT *"){
if(isset($set) && !is_numeric($set)){
if(!empty($set)){
$order = "ORDER BY {$set} ASC";
} else {
$order = "ORDER BY {$set} DESC";
}
} else {
$order = "";
}
return $order;
}
return false;
}
INSERT
function insert($tabela, $campos=array())
{
if(!empty($tabela)){
if(!empty($campos) && is_array($campos)){
$valores = parametros($campos, 'param', true);
$colunas = parametros($campos, 'coluna');
$sql = "INSERT INTO {$tabela} ({$colunas}) VALUES ({$valores})";
if(query($sql, $campos)){
return true;
}
}
}
return false;
}
UPDATE
function update($tabela, $id, $campos)
{
if(!empty($tabela) && !empty($id)){
if(!empty($campos) && is_array($campos)){
$campos['id'] = $id;
$set = parametros($campos, 'update', true);
$equals = parametros(['id'=>$id], 'update', true);
$sql = "UPDATE {$tabela} SET {$set} WHERE {$equals}";
if(query($sql, $campos)){
return true;
}
}
}
return false;
}
Both the function Insert as the update depend on the function parameters, responsible for providing the parameters :named/(?), and also some additional parameters.
parameters
function parametros($args = array(), $tipo=null, $named = false)
{
$coluna = isset($args['colunas']) ? $args['colunas'] : array_keys($args);
$i=1;
$param='';
foreach($args as $key=>$arg){
if(!empty($tipo) && $tipo === 'update'){
if($named){
$param .= "{$key} = :{$key}";
} else {
$param .= "{$key} = ?";
}
} else {
if($named){
$param .= ":{$key}";
} else {
$param .= "?";
}
}
if($i < count($args)){
$param .= ', ';
}
$i++;
}
if(!empty($tipo)){
switch($tipo){
case 'coluna':
$retorno = implode(', ', $coluna);
break;
case 'param':
$retorno = $param;
break;
default:
$retorno = ['colunas'=>implode(', ', $coluna), 'values'=>$param];
break;
}
return $retorno;
}
return ['colunas'=>implode(', ', $coluna), 'values'=>$param];
}
The function select and delete depend solely on the function accao that does much of the work for them.
SELECT
function select($tabela, $campos=array())
{
global $prepare;
if(accao("SELECT *", $tabela, $campos)){
$all = $prepare->fetchAll(PDO::FETCH_OBJ);
return $all;
}
return false;
}
DELETE
function delete($tabela, $campos=array())
{
global $prepare;
global $rows;
if(accao("DELETE", $tabela, $campos)){
$rows = $prepare->rowCount();
return true;
}
return false;
}
To execute any kind of query, it would be like this:
query('SELECT * FROM games WHERE name = ? ', ['name'=>'Edilson', 'surname'=>'Samuel']));
To perform an Insert:
var_dump(Insert('games', ['name'=>'Something Awful', 'console'=>'XBOX']));
To perform an update:
var_dump(update('games', 17, ['name'=>'Something Creepy', 'console'=>'XBOX']));
To perform a select:
var_dump(select('games', ['id','>', '10']));
To perform a delete:
var_dump(delete('game', ['id','=', '18']));
In consultation with the delete the variable was used $rows
to store the number of queries removed, and this value can be read normally.
if(!empty($rows)){
print "Removidos:" . $rows;
}
Despite being an extensive example, one can see how various types of query are formed, through various parameters that may or may not vary depending on the type of query requested. All queries end the function query, where the values/variables are linked(as) to the respective markers and subsequently executed.
This is an old example, with superficial modifications, and works similarly to the methods that the framework uses, although more complex and improved have a similar function. One queryString can be built through simpler processes, the use of functions/methods simply makes these blocks reusable, and also organizes them.
You can not say exactly, what you can not insert direct variables in the query, on the other question see => http://answall.com/a/33528/91
– rray
In the case of query, I’m taking the function parameters (column, Where, order, limit and like) doing a treatment on them (how to concatenate the variable expression
where
with the string"WHERE"
etc) and then concatenating all these parameters into the variablesql
, an example of the variablesql
would be"SELECT * FROM usuarios WHERE nome = 'stack' ORDER BY ASC
". That would be safe?– Leonardo
It depends on where this data comes from that you concatenate in the query.
– bfavaretto
They usually turn from forms of other pages by
post
(except the id that will be byget
) .– Leonardo
You speak of something like this ? -
$sql = "{$accao} FROM {$tabela} WHERE {$campo} {$operador} {$marcador} {$order}";
- Where these variables may or may not have functionsSQL
and/or parameters received from the form, or even have no value ?– Edilson
It would be more or less that, in fact, it would be
$sql = $coluna.$tabela.$where.$like.$ordem.$limite;
where$coluna = "WHERE coluna ";
$tabela = "FROM tabela ";
and so on...– Leonardo
If it is just this, there is no harm, since this sql query is generated correctly, having the fields and parameters perfectly organized before being prepared using the
prepare
or already containing the parameters in all cases with the methodquery
.– Edilson
Look this answer, or even this answer here, despite using the method
bind_param
which is equal tobindParam
ofPDO
you can get the same done. As I said before, as long as the query is properly formed there will be no problems when you use thebind
or even theexecute([array])
.– Edilson