When I run a query in the MySQL database, the Execute command freezes the database completely, whereas the Query command normally runs the query. What is the difference between these two commands?
When I run a query in the MySQL database, the Execute command freezes the database completely, whereas the Query command normally runs the query. What is the difference between these two commands?
Good morning, I will answer in a general way using MySQLi and PDO, since you do not specify the type of query class:
query()
Execute a standard SQL query to the database, this requires correctly escaping the data to avoid sql injections (see: How to avoid SQL injection in PHP? ), it is generally used when you will obtain data without any condition or characteristic, ie without using WHERE .
Example:
$sql = 'SELECT nombre, color, calorias FROM frutas ORDER BY nombre';
foreach ($conexion->query($sql) as $valor) {
echo $valor['nombre'] . "\t";
echo $valor['color'] . "\t";
echo $valor['calorias'] . "\n";
}
As shown in the example we only return direct data, that is, we do not seek to use WHERE to condition the results, in the case of wanting to do so we must assign parameters, for that the execute is used, since this accompanies sentencias preparadas (see: link ).
Another problem of query() , is that you must declare the sentence again to use the query again.
Making queries less efficient.
execute()
Run or execute a sentencia preparada that allows you to assign parameters that avoid SQL injection since you do not have to use quotation marks or escape characters, in case you want to execute the query to the database again, just execute execute again to do so.
This also requires that the statement be prepare before.
Example:
$consulta = $conexion->prepare('SELECT nombre, color, calorias FROM frutas
WHERE calorias < :calorias AND color = :color');
$consulta->bindValue(':calorias', $calorias);
$consulta->bindValue('color', $color);
$consulta->execute();
Where:
$consulta->bindValue('calorias', $calorias) -> Asignar parametros
We assign $ calories to the calorie parameter, which corresponds to the calories field of the fruits table.
As you observe in the SQL statement when we equal calories =: calories, two points are presented, that represents: calories is a box where the value of the assigned variable is then measured.
This is because the sentences prepared first send the sentence to MySQL:
SELECT nombre, color, calorias FROM frutas
WHERE calorias < :calorias AND color = :color
And then send the values
:color, $color
So that when you execute it, both combine something like this:
SELECT nombre, color, calorias FROM frutas
WHERE calorias < $calorias AND color=$color
But everything happens within the same MySQL, not in the PHP.
In conclusion:
query(): simple queries without conditional (insecure if you do not know how to structure the SQL query).
execute(): queries with parameters passed by assignment (more secure, if PDO or MySQLi is used, they allow defining the type of data they will receive)
Execute command completely freezes the database
Now, what we do with this, I would need you to publish the query to see what error it could present.
To learn more about sql injection and prepared statements I invite you to read:
Query executes a standard SQL statement and can cause SQL Injections and other problems.
Execute executes a prepared statement that allows you to bind parameters to avoid the need to escape or quote the parameters. It will also work better if you are repeating a query several times.
$sth = $dbh->prepare('SELECT name, colour, calories FROM fruit
WHERE calories < :calories AND colour = :colour');
$sth->bindParam(':calories', $calories);
$sth->bindParam(':colour', $colour);
$sth->execute();
In summary, Execute, in a certain way is more secure. Greetings.
I would like to provide other differences between query and execute , taking into account the PDO and MySQLi APIs, which are currently the two recommended by PHP to handle data in a secure manner. Since the mysql_* extension is insecure and therefore obsolete.
Make a query to the database and you can use it without danger when it does not have values that are obtained from outside.
There is a small difference with respect to query in terms of the values returned if you use PDO or MySQLi.
PDO :: query () returns an object PDOStatement , or
FALSEin case of error.
mysqli :: query Returns
FALSEin case of error. If a query of type SELECT, SHOW, DESCRIBE or EXPLAIN is successful,mysqli_query()will return an object mysqli_result For other successful queries ofmysqli_query()will return TRUE . That is, it does not return a set of results in all the types of queries.
Prepare an SQL statement for execution.
There are also differences if you work with PDO or MySQLi:
PDO :: exec () executes a SQL statement in a single call to the function, returning the number of rows affected by the statement.
PDO :: exec () does not return results from a SELECT statement. For a SELECT statement that only needs to be executed once in the program, the use of PDO :: query () should be considered. For a statement that must be executed multiple times, prepare a PDOStatement object with PDO :: prepare () and execute it with PDOStatement :: execute ().
return values
PDO :: exec () returns the number of rows modified or deleted by the executed SQL statement. If there are no affected rows, PDO :: exec () returns 0.
Important note:
Since execute is used with prepared queries, it is important to note the following:
PDO will emulate prepared statements / replacement parameters for drivers that do not support it natively , and can rewrite the style of the substitution parameters by a more appropriate form, if the Driver supports one style but not the other.
This can be dangerous in the face of SQL Injection. Therefore, whenever we use PDO we must set the emulation of prepared queries to off mode. For more details you can see this answer the question: How to avoid SQL injection in PHP?
mysqli_stmt :: execute Execute a query that has been previously prepared using the mysqli_prepare function ( ). When any existing parameter marker was executed, it will be automatically replaced with the appropriate data.
If the statement is UPDATE, DELETE, or INSERT, the total number of affected rows can be determined using the mysqli_stmt_affected_rows () function. Likewise, if the query produces a set of results, the mysqli_stmt_fetch () function is used.
When using mysqli_stmt_execute (), the mysqli_stmt_fetch () function must be used to obtain the data before making any additional query.
return values
mysqli_prepare() returns a statement object or FALSE if an error occurs. Unlike PDO, it does not make exceptions.