Comparing prepare () vs query () with mysqli

Question

Comparing prepare () vs query () with mysqli

Navigation

#1 by (8 votes)
#2 by (7 votes)

5

I made a query in the database, 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 prepared statements to try, 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 this function does?

Why when I use query, I do not need to use get_result() ?

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

php mysql query mysqli

asked by anonymous 19.01.2015 / 15:10

2 answers

7

The great advantage of prepared statements is as follows:

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



Parameters for prepared statements ( prepared statements ) do not   need to be in single quotes (or "single quotation marks" or "quotation marks"), the   driver will handle this automatically. If an application uses   exclusively pre-prepared commands, the developer may have   sure that SQL Injection will not occur (however, if other   part of the query is being constructed through   you will still be subject to SQL Injection ).

Source: link

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 do not take advantage of a MySQL feature that is exactly what the quote says.

Already using the prepare() function, you only "bind" ( bind() ) the arguments and execute a query that probably has already run a million times on your server. MySQL knows the best execution plan for the query and reuses it every time the query is updated again, streamlining its application.

19.01.2015 / 15:31

Is there an event that captures size changes of an HTML element? Are objects similar to arrays?

score 8 · Accepted Answer

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

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

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

Some people question some of these advantages. Even security could possibly be compromised one day without you having any control over it. There is still a general recommendation for using prepared queries.

There are several ways to use the results.

If you think the code is large, create a function that encapsulates complexity. I realize in PHP programmers the perception that functions should be used. It's 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 API's choice not to generate the object with results directly, possibly to give more flexibility. In general more flexible code is usually a bit larger. We can say that in case "pure" the get_result() is executed inside the query() itself and it already returns what you want. That's why I say you can create a function that handles all of this and turn the 4 lines into just 1.