How to Make Sure That Values from MySQL Keep Their Type in PHP

How do I make sure that values from MySQL keep their type in PHP?

What exactly do I do to get my MySQL functions in PHP to give me the MySQL results in their native type?

You connect to the database, then you prepare your query, execute it, bind the result and then you fetch it.

Let's do these steps line-by-line:

$conn = new Mysqli('localhost', 'testuser', 'test', 'test');
$stmt = $conn->prepare("SELECT id FROM config LIMIT 1");
$stmt->execute();
$stmt->bind_result($id);
$stmt->fetch();
var_dump($id); # it's an int!

This works for me. As you wrote your code is more complex, you will need to locate the place where you query the database. Check that you're using Mysqli::prepare() and if not, introduce it.

You will also need to use Mysqli_Stmt::execute() and then Mysqli_Stmt::bind_result() otherwise the (here integer) type is not preserved for that result column.

Get MySQL Query Results as Their Native Data Type?

I don't think getting data in their native datatypes (i.e. anything else that strings) can be done in PHP 5.2...

In PHP 5.3, it becomes possible, if I remember correctly, when you are using the new (new as in PHP >= 5.3) mysqlnd (MySQL Native Driver) driver.

After more digging through my bookmarks I found this article about mysqlnd : PDO_MYSQLND: The new features of PDO_MYSQL in PHP 5.3

It says this (quote) :

Advantages of using mysqlnd for PDO

mysqlnd returns native data types when
using Server-side Prepared Statements,
for example an INT column is returned
as an integer variable not as a
string. That means fewer data
conversions internally.

But this is PHP 5.3 only (provided your version of PHP 5.3 is compiled with mysqlnd (and not the old libmysql)), and seems to only be the case for prepared statements :-(

Which doesn't quite help, in your situation, I guess...

And here's another one, still about the new features of mysqlnd, which talks about this for not only prepared statements : PHP: New network traffic, CPU and memory savings with mysqlnd.

Not sure this has been merged into the official mysqlnd driver, though -- best way would be to try ; but it'll still be PHP >= 5.3 only, anyway...

Another solution would be to have, on the PHP-side, some kind of a mapping-system (like an ORM) to convert results coming from the DB to PHP datatypes...

And yes, this is bad if you want to use operators like === and !==, which are type-sensitive...

PHP: Want to keep a persistent variable when submitting form and reloading self-processing form

It's a simple matter of ensuring the variable is set, if it is then increment it like any other variable.

if(isset($_SESSION['nameCount'])) {
    $_SESSION['nameCount']++;
}

Alternatively, given you've set the value of the session variable to a local variable you can increment this local variable and reassign that value to the session variable. I.e.

$nameCount++;
$_SESSION['nameCount'] = $nameCount;

Both will have the same result.

Best way to store values related to columns of a mysql table?

So if anyone stumbles upon this, I simply used a JSON file to store all the information I need about specific table columns, e.g. :

{
    "table1": {
        "id": {
            "type": "numeric",
            "hidden": false,
            "filtering": true,
            "editable": "never"
        },
        "name": {
            "type": "string",
            "hidden": false,
            "filtering": true,
            "editable": null
        },
    },
    "table2": {
        "phone": {
            "type": "string",
            "hidden": false,
            "filtering": true,
            "editable": null
        },
        "age": {
            "type": "numeric",
            "hidden": false,
            "filtering": true,
            "editable": null
        }
    }
}

I get this file in my server-side php and send everything to my frontend. Now I can access values via props in my React project.

    //use map function to create columns from column_fields in props
    let i = 0;
    let columns = props.column_fields.map(column => {
        i++;
        if (column.field != 'updated_at') {
            return ({
                title: column.field, field: column.field, type: props.column_fields[i - 1].type, editable: props.column_fields[i - 1].editable,
                filtering: props.column_fields[i - 1].filtering, hidden: props.column_fields[i - 1].hidden
            })
        }
    })

Hopefully this method works out long term.

Is there any need to ensure correct data type is submitted to MySQL database?

The values are being converted between string and integer. When inserting a row into MySQL both PHP and MySQL can convert a string of "1" into an integer 1. Try passing a string "notanumber" into an Integer field, it's not going to work because you can't convert that string value into a number. The reason MySQL returns strings in selecting is so everything is in one type of format, there may be another reason for it - but it's easier to know everything in your results is a string and not have to check if it's an integer, or a float, or whatever else. With PHP and implicit conversion this isn't a huge deal, but for a language like C# that is very strongly typed this can save a lot of time. You know it's a string, and convert to what you want if need be instead of checking for tons of different possibilities.

You definitely should be checking data before inserting, or at least handling the MySQL errors if you don't. You can check using isset($var) for null or empty values, is_numeric($var) for integers, is_float($var) for floats. I would recommend validating everything before putting it into the database.

How to add a possble value to a MySQL SET type in php, without know the current values

We can get the column definition with a query from information_schema.columns

Assuming the table is in the current database (and assuming we are cognizant of lower_case_table_names setting in choosing to use mixed case for table names)

 SELECT c.column_type 
   FROM information_schema.columns c
  WHERE c.table_schema = DATABASE()
  WHERE c.table_name   = 'TableName'
    AND c.column_name  = 'example'

Beware of the limit on the number of elements allowed in a SET definition.

Remove the closing paren from the end, and append ',newval').

---

Personally, I don't much care for the idea of running an ALTER TABLE as part of the application code. Doing that is going to do an implicit commit in a transaction, and also require an exclusive table / metadata lock while the operation is performed.

mySQL - PHP : how to prepare queries with variables number of variables

Your code suggests a very poor database structure. Having massive numbers of columns like this speaks to a very not normal database. That said, you're doing the best you can with the database you have; a couple of points that can compress the code significantly though.

You do not escape data for HTML display until you are in fact displaying it in HTML. Never store it in your database escaped, or you will be unhappy when someone wants data in a PDF or output to command line.

PDO does not require parameters to be bound; this is only necessary in obscure situations like when the data type is not being deduced correctly, or you need to get data back from stored procedures. Just pass the array of parameters to the execute function.

I've also condensed the code you use to build the query, and the parameter array is built from $_POST within that same loop.

<?php
public function saveInfos()
{
    $UM = new UserManager;
    if(isset($_SESSION['user_pseudo'])){
        $user_id = $UM->getUserId($_SESSION['user_pseudo']);
    }
    $DB = $this->dbConnect();
    $nbr_of_domain = $this->getNumberOfDomains();
    $fields = ["ST_G", "MT_G", "LT_G", "ST_T", "MT_T", "LT_T"];
    //BUILD QUERY AND PARAMETERS
    $params[':user_id'] = $user_id;
    for($i = 1; $i <= $nbr_of_domain; $i++) {
        foreach ($fields as $field) {
            $domain_assig[] = "DM{$i}_{$field} = :DM{$i}_{$field}";
            $params[":DM{$i}_{$field}"] = $_POST["DM{$i}_{$field}"];
            // if passing parameters to execute() truly is a problem,
            // you could delete the line above and then run this same
            // loop again to bind parameters, as in the comment below
        }
    }
    $req = sprintf(
        "UPDATE user_board_items SET %s WHERE user_id=:user_id",
        implode(",", $domain_assig)
    );
    //PREPARING REQUEST
    $saveRequest = $DB->prepare($req);
    // if passing parameters to execute() truly is a problem...
    /*
    $saveRequest->bindParam(":user_id", $user_id);
    for($i = 1; $i <= $nbr_of_domain; $i++) {
        foreach ($fields as $field) {
            $saveRequest->bindParam(":DM{$i}_{$field}", $_POST["DM{$i}_{$field}"]);
        }
    }
    */
    $saveRequest->execute($params);
    $saveRequest->closeCursor();
}

---

As regards database normalization, where your database structure currently looks like this:

+----+---------+----------+----------+----------+     +----------+
| id | user_id | DM1_ST_G | DM1_MT_G | DM1_LT_G | ... | DM8_LT_T |
+----+---------+----------+----------+----------+     +----------+
| 17 | 12345   | aaa      | aaa      | aaa      | ... | hhh      |
+----+---------+----------+----------+----------+     +----------+

It should look like this:

+----+---------+----+------+------+------+------+------+------+
| id | user_id | DM | ST_G | MT_G | LT_G | ST_T | MT_T | LT_T |
+----+---------+----+------+------+------+------+------+------+
| 11 | 12345   | 1  | aaa  | aaa  | aaa  | aaa  | aaa  | aaa  |
| 12 | 12345   | 2  | bbb  | bbb  | bbb  | bbb  | bbb  | bbb  |
...
| 18 | 12345   | 8  | hhh  | hhh  | hhh  | hhh  | hhh  | hhh  |
+----+---------+----+------+------+------+------+------+------+

Then you can select however many rows there are, based on the user ID. Imagine a situation where you have a few hundred thousand rows, and you decide you want to add another set of DM9_* columns. The way it is now, the entire table has to be rebuilt, your code has to be adjusted, and it's very messy.

---

Related Topics