Geek Answers Handbook

Data casting using PHP MySQLi, query and fetch_assoc functions

I am using a custom PHP shell for mysqli. The class is designed to use prepared statements if the binding parameters are passed to the fetchResultSet function, otherwise, as I understand it, it stores the call in the database and uses the query function (maybe I'm wrong when saving the call, and the rest of this question can be answered simply by using prepared operators even without binding parameters).

Once the request has been executed, the fetchResult function returns one of two ResultSetObjectResult or ResultSetObjectStmt objects as from witch, which implement the ResultSetObject interface. (The ResultSetObjectStmt object wraps the stmt object, while the ResultSetObjectResult wraps the result object.)

Both classes return an associative array when a string is requested, either when fetch_assoc is called by the result, or when bind_result is called on the stmt object (basically).

What I noticed is that if the ready statement is executed, the returned data is correctly displayed in integers, real numbers and strings, depending on their types in the database; but when the result is returned and fetch_assoc is called on that result, all data is written as strings. I understand that this is mentioned in the documentation for fetch_assoc. I am curious if there is another function or something that I can do to make mysql display the results correctly.

[EDIT] I should mention that this is only a problem because json_encode puts the data in quotation marks depending on their types.

[CHANGE AGAIN] I like to know if there is anyway the correct use of the data returned from the result, without resorting to guessing using functions such as is_numeric, or by additionally invoking the database for the table schema. The use of prepared statements exclusively when extracting data, but I would very much like to keep this additional challenge of preparing for the database.

+4
source share
2 answers

I don’t know which shell you are using, but you might have a function like ...

while ($row = $query->fetch_assoc()) { $row = auto_cast($row); } function auto_cast($row) { foreach ($row as $key => $value) { if (ctype_digit($value)) { $row[$key] = (int) $value; } elseif (is_numeric($value)) { $row[$key] = (float) $value; } else { $row[$key] = (string) $value; } } return $row; }
+2
source

I will try to help here. When the usign command is prepared, PHP knows what a column type is, and depending on what stores the data in different types: string, integer, bool, etc. When using fetch_assoc it does not know this information. You can create a function that receives MySQL field / column data using mysqli_fetch_fields and uses its type. Based on this, you can come up with them. Hope this helps.

For information on the different types of Fior columns, see http://www.php.net/manual/en/mysqli.constants.php

MYSQLI_TYPE_LONG - The field is defined as INT and so on.

+1
source

More articles:


All Articles