Fetching data from a database using MySQLi and prepared expressions?

Question

PHP

Now many people recommend using the new MySQLi extension for database queries, which promises us support for all the new features of MySQL, more convenience and faster performance.
One thing is not clear to me: why didn’t they come up with a method for such a “new” and “cool” extension that allows, after executing a prepared expression, to receive a previously unknown number of fields? I mean the selection of the form: It is not even possible to pass one array variable, the keys and values ​​of which would become the result of the selection, to the function of preparing the result of the expression. As a result, if we have a table like:
SELECT * FROM table;

Field	Type
id	INT(10)
field1	VARCHAR(20)
field2	VARCHAR(20)

then when requested:

$stmt = $mysqli->prepare("SELECT * FROM table WHERE id = ?");
$stmt->bind_param('i', $itemid);
$stmt->execute();
$stmt->bind_result($id, $field1, $field2);
$stmt->fetch();

everything will be fine. But as soon as we add one more field to the table, the same code will cause a critical error of mismatch between the number of variables and fields.
Why is this done?

Answer 1

Faced with a similar problem, I used the crutch from the zvirusz comment for a long time. But recently I came across information about PDO . PDO allows you to:
1) Easily change the SQL platform when using compatible queries;
2) Use prepared statements
3) Get the associated array without crutches using PDO::FETCH_ASSOC
The code turns out to be something like

$stmt = $db->prepare("SELECT * FROM foo WHERE a = ?");
$stmt->execute(array($a));

$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

I'm going to transfer my project to PDO, maybe you should look in this direction before you get stuck with MySQLi.

Answer 2

One thing is not clear to me: why didn’t they come up with a method for such a “new” and “cool” extension that allows, after executing a prepared expression, to receive a previously unknown number of fields?

A crutch can almost always be found: ru.php.net/manual/en/mysqli-stmt.fetch.php#82742

Why is this done?

Apparently because a thoughtless selection of all fields is bad manners.

Answer 3

The new extension brings new possibilities. You have selected the "SQL prepared" mode, i.e. at the first execution, the SQL statement is compiled and stored in the compiled form in the SQL cache as a query. This saves compilation time and improves performance.
If you have changed the structure of the table, then your compiled expression will no longer fit the new structure. Hence the error. Conclusion:
either set all fields, or do not use "prepared expressions"

Answer 4

What is it new? Already the fourth year, as officially the main tool for working with mysql.