Why is mysqli_fetch_fields() giving me inaccurate results?

Question

The length result from mysqli_fetch_fields() is giving me inaccurate results.

MySQL > DESCRIBE Notices;

Field     Type               Null     Key     Default     Extra
Id        int(10) unsigned   NO       PRI     (NULL)      auto_increment
UserId    int(10) unsigned   NO       MUL     (NULL)
Title     char(255)          NO               (NULL)
Name      char(255)          NO               (NULL)
Summary   text               NO               (NULL)

However,

class Db {
    public function exec($sql) {
        if (!$this->link) {
            $this->open();
        }
        if (($this->result = mysqli_query($this->link, $sql)) === false) {
            throw new Exception('<b>' . __METHOD__ . '</b>: ' . $sql . ': ' . mysqli_error($this->link));
        }
        return true;
    }
    public function field_info($table) {
        $sql = 'SELECT * FROM `' . $table . '` LIMIT 1';
        $this->exec($sql);
        return $this->field_info = $this->result->fetch_fields();
    }
}

$a  = $db->field_info('Notices');
print_r($a);

Produces the following output:

// Note: "max_length" is the maximum existing length in the result set,
// while "length" is the set maximum length in the table definition.

Array
(
    [0] => stdClass Object
        (
            [name] => Id
            [orgname] => Id
            [table] => Notices
            [orgtable] => Notices
            [def] =>
            [max_length] => 1
            [length] => 10
            [charsetnr] => 63
            [flags] => 49699
            [type] => 3
            [decimals] => 0
        )

    [1] => stdClass Object
        (
            [name] => UserId
            [orgname] => UserId
            [table] => Notices
            [orgtable] => Notices
            [def] =>
            [max_length] => 1
            [length] => 10
            [charsetnr] => 63
            [flags] => 53289
            [type] => 3
            [decimals] => 0
        )

    [2] => stdClass Object
        (
            [name] => Title
            [orgname] => Title
            [table] => Notices
            [orgtable] => Notices
            [def] =>
            [max_length] => 27
            [length] => 765
            [charsetnr] => 33
            [flags] => 4097
            [type] => 254
            [decimals] => 0
        )

    [3] => stdClass Object
        (
            [name] => Name
            [orgname] => Name
            [table] => Notices
            [orgtable] => Notices
            [def] =>
            [max_length] => 25
            [length] => 765
            [charsetnr] => 33
            [flags] => 4097
            [type] => 254
            [decimals] => 0
        )

    [4] => stdClass Object
        (
            [name] => Summary
            [orgname] => Summary
            [table] => Notices
            [orgtable] => Notices
            [def] =>
            [max_length] => 26
            [length] => 196605
            [charsetnr] => 33
            [flags] => 4113
            [type] => 252
            [decimals] => 0
        )
)

If you look, you'll see that the reported lengths of the CHAR fields do not match. DESCRIBE is giving me 255, while fetch_fields() is giving me 765. Why?

(The idea, in case you're wondering, is to produce a maxlength attribute for <input> tags.)

https://chat.stackexchange.com/transcript/message/53261260#53261260 — Evan Carroll, Jan 17 '20 at 15:01

score 1 · Accepted Answer · edited Dec 08 '14 at 13:29

1

Your field length is 255 characters, but the reported length is in bytes.

Since MySQL is using 3 bytes per character for UTF-8 collations, you get 255*3 = 765 as your result.

edited Dec 08 '14 at 13:29

Sebastian Zartner

18,808
10
90
132

answered May 03 '12 at 11:39

Konerak

39,272
12
98
118

Where does it say that the length is in bytes? The [documentation for `mysqli_fetch_fields`](http://php.net/manual/en/mysqli-result.fetch-fields.php#refsect1-mysqli-result.fetch-fields-returnvalues) only says "The width of the field, as specified in the table definition.". Also you only get the incorrect result on Windows. When you run the same script on Linux you get the expected result, i.e. `255`. – Sebastian Zartner Dec 08 '14 at 14:01
@SebastianZartner. Nope. I was seeing trouble where both PHP and MySQL were running on Linux. (With both on Ubuntu; and again with both on CentOS.) – TRiG Dec 09 '14 at 11:04
Right, the comment above was my perception from yesterday. Though as you can see in my answer below it is actually unrelated to the OS but rather defined through the default client character set. – Sebastian Zartner Dec 09 '14 at 11:40

score 1 · Answer 2 · edited May 23 '17 at 12:23

The length as well as the charsetnr values in the mysqli_result::fetch_fields() / mysqli_fetch_fields() output obviously depend on the default character set defined by the client.

E.g. a charsetnr of 33 stands for UTF-8. To be precise it actually stands for the collation 'utf8_general_ci' of the character set 'utf8'. That info can be retrieved by executing this query:

SELECT * FROM information_schema.collations;

A list of available character sets and their character length can be retrieved via this query:

SELECT * FROM information_schema.character_sets;

The default client charset can be changed by PHP calling mysqli::set_charset() / mysqli_set_charset().

Example:

$dbHandle->set_charset('latin1');

The PHP documentation for mysqli_fetch_fields() currently doesn't include that info, so I requested to add it in bug 68573.

The default character set can also be defined within the server configuration as described in another thread.

As this behavior of fetch_fields() is quite confusing, I requested to change it in bug 68574.

score 0 · Answer 3 · edited Nov 26 '14 at 18:12

0

I divide the result:

$result = mysqli_query($con,"SELECT * from tabla");
$campoTD = mysqli_fetch_fields($result);
  foreach ($campoTD as $campo){
    echo '<input maxlength='.number_format($campo[$i]->length / 3).' >';
    $i++;
  }

edited Nov 26 '14 at 18:12

TRiG

10,148
7
57
107

answered Nov 26 '14 at 18:08

Vladimir Salguero

5,609
3
42
47

score -1 · Answer 4 · answered Apr 16 '13 at 22:10

-1

I had the same problem and found out you need to use: $val->length;

not max_length, max_length is the max length of the values in that particular column not the table def.

If you want the table definition use 'length'.

$q = $mysqli->query("select * from table");

$finfo = $q->fetch_fields();

foreach ($finfo as $val) {

    echo $val->length;

}

ref: http://php.undmedlibrary.org/manual/de/function.mysqli-fetch-fields.php

answered Apr 16 '13 at 22:10

stevesrs

57
1
7

Yes. See comment in code in my question, which already makes that point. – TRiG Apr 16 '13 at 22:24

Why is mysqli_fetch_fields() giving me inaccurate results?

4 Answers4