Comparing prepare() vs query() with mysqli

Question

I went to make an appointment at the bank with query() using mysqli and num_rows to return the number of lines, see the code:

$consulta = $mysqli -> query("SELECT * FROM tabela WHERE Pedido = '$pedido' AND Email = '$email' ");
$linhas = $consulta->num_rows;
echo $linhas;

So I decided to use the Prepared statements to experiment, and the code got bigger, like this:

$consulta = $mysqli -> prepare("SELECT * FROM tabela WHERE Pedido = ? AND Email = ?");
$consulta -> bind_param("ss",$pedido,$email);
$consulta -> execute();
$res = $consulta->get_result(); <----------
$linhas = $res->num_rows;

See the line in the code above, help me understand what makes this function?

Why when I use query, I don’t need to use get_result()?

So Prepared statement is like, preparing the query(prepare()), suggest parameters with bind_params(), executar() and still to get the results using get_result()?

Answer 1

In general it is safer. Not that we can not give security with the query normal but many do not know how to do it. It’s true that a lot of people who don’t know how to do it don’t care about safety either, they only care if the code appears to be working. Using this form you avoid SQL Injection.

In addition it is possible to have some performance gain because it can be compiled and curled. But depending on the usage pattern will be no different than an unprepared query. In other cases this possible gain will not even be taken advantage of. Remember that the preparation lasts only during the usually short session.

It seems that the data is also trafficked slightly more efficiently but I have no proof of this.

Some people question some of those advantages. Even security could eventually be compromised one day without you having any control over it. Still there is a general recommendation to use prepared consultations.

There are several ways to use the results.

If you think the code has gotten big, create a function that encapsulates the complexity. I notice in PHP programmers the perception that functions should be used. It is rare to see people creating utilitarian functions to simplify code. I see all the time people copying and pasting snippets of code that do the same thing.

It was the choice of the API not to generate the object with results directly, possibly to give more flexibility. In general more flexible code are usually a little larger. We can say that in the case of the query "pure" the get_result() is executed within the query() and it already returns what you want. That’s why I say that you can create a function that deals with all this and turn the 4 lines into just 1.

Answer 2

The great advantage of Prepared statements is the following:

• The query needs to be parsed (Parsed) or prepared only one but can be performed multiple times with the same or different parameters. When the query is prepared, the database will analyze, compile and optimize your plan for the execution of query. For complex queries, this process can consume time sufficient to make the application noticeably slower if there is a need to repeat the same query many times with different parameters. Using a pre-prepared command (Prepared statement) to application avoids repetition of the cycle of analysis/compilation/optimization. This means that pre-prepared commands use fewer resources and so they run faster.

• The pre-prepared command parameters (Prepared statements) nay must be between apostrophes (or "single quotation marks" or "quotation marks"), driver will handle this automatically. If an application uses exclusively pre-prepared commands, the developer may have certainty that it will not occur SQL Injection (however, if others parts of the query are being built through non input treated, will still be subject to SQL Injection).

Source: http://php.net/manual/en/pdo.prepared-statements.php

In practice, what changes is exactly what you posted in the examples above. When you run the query with query(), you may be extracting the result using fewer lines of code but you don’t take advantage of a Mysql resource which is exactly what the quote says.

Already using the function prepare(), you just "tie" (bind()) arguments and executes a query that has probably run a million times on your server. Mysql knows the best execution plan for the query and reuses it every time the query is all over again, streamlining your application.