Fetch data from database using MySQLi and prepared statements?

0 like 0 dislike
8 views
Now, many recommend to use the new MySQLi extension for database queries, which promises support of all new features, more conveniences and more high-speed operation.

One thing I wonder: why such a "new" and "cool" the extension did not come up with a method that allows you after executing prepared statements in advance to an unknown number of fields? I mean a sample:
SELECT * FROM table;

In the preparation function of the result of the expression is not even possible to pass a single variable-the array keys and values to which would be the result of sampling.


As a result, if we have a table of the form:

FieldType
idINT(10)
field1VARCHAR(20)
field2VARCHAR(20)


then when you query:
$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 if we add in table one more field, as this code will cause a critical error from the disparity between the number of variables and fields.

Why is it done?
by | 8 views

4 Answers

0 like 0 dislike
Faced with a similar problem long used the crutch of review zvirusz. But recently I came across information about the PDO. PDO allows you to:
1) Easy to change the SQL platform when you use the compatible query;
2) Use prepared statements
3) to associate the array without crutches using PDO::FETCH_ASSOC
\r
The code becomes something like
\r
$stmt = $db->prepare("SELECT * FROM foo WHERE a = ?"); $stmt->execute(array($a)); $result = $stmt->fetchAll(PDO::FETCH_ASSOC); 

\r
Going to translate your project to PDO, maybe you should look in that direction, not yet bogged down with MySQLi.
by
0 like 0 dislike
One thing I wonder: why such a "new" and "cool" the extension did not come up with a method that allows you after executing prepared statements in advance to an unknown number of fields?

Crutch, you can almost always find: ru.php.net/manual/en/mysqli-stmt.fetch.php#82742
\r
\r
Why is it done?

Probably because mindless sampling of all fields — moveton.
by
0 like 0 dislike
New rasshirenie opportunities. You choose the mode "preparing SQL", ie the first time you run kompilacja SQL statement in the compiled SQL is stored in the cache by the query. This saves time in compilation and increases productivity.
If You change the table structure, then Your accompliance expression will not fit the new structure. Hence the error. Conclusion:
or set all fields, or do not use "prepared expression"
by
0 like 0 dislike
What is it new? For the fourth year, as officially the main tool for working with mysql.
by

Related questions

0 like 0 dislike
1 answer
0 like 0 dislike
2 answers
0 like 0 dislike
2 answers
0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
asked May 11, 2019 by Ambrel
110,608 questions
257,186 answers
0 comments
28,729 users