PHP에서 MySQL 정수 필드가 문자열로 반환됩니다

PHP에서 MySQL 정수 필드가 문자열로 반환됩니다

---

MySQL 데이터베이스에 테이블 필드가 있습니다.

userid INT(11)

따라서이 쿼리를 사용하여 내 페이지로 호출합니다.

"SELECT userid FROM DB WHERE name='john'"

그런 다음 결과를 처리하기 위해 다음을 수행하십시오.

$row=$result->fetch_assoc();

$id=$row['userid'];

이제 내가하면 :

echo gettype($id);

나는 문자열을 얻는다. 정수가 아니어야합니까?

---

PHP를 사용하여 MySQL 데이터베이스에서 데이터를 선택하면 데이터 유형이 항상 문자열로 변환됩니다. 다음 코드를 사용하여 정수로 다시 변환 할 수 있습니다.

$id = (int) $row['userid'];

또는 기능을 사용하여 intval():

$id = intval($row['userid']);

---

PHP에는 mysqlnd (네이티브 드라이버)를 사용하십시오.

Ubuntu를 사용하는 경우 :

sudo apt-get install php5-mysqlnd
sudo service apache2 restart

Centos를 사용하는 경우 :

sudo yum install php-mysqlnd
sudo service httpd restart

원시 드라이버는 정수 유형을 적절하게 리턴합니다.

편집하다:

@Jeroen이 지적 했듯이이 방법은 기본적으로 PDO에서만 작동합니다.
@LarsMoelleken이 지적했듯이 MYSQLI_OPT_INT_AND_FLOAT_NATIVE 옵션을 true로 설정하면이 메소드는 mysqli와 함께 작동합니다.

예:

$mysqli = mysqli_init();
$mysqli->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, TRUE);

---

내 솔루션은 쿼리 결과를 전달 $rs하고 캐스팅 된 데이터의 assoc 배열을 반환으로 얻는 것입니다.

function cast_query_results($rs) {
    $fields = mysqli_fetch_fields($rs);
    $data = array();
    $types = array();
    foreach($fields as $field) {
        switch($field->type) {
            case 3:
                $types[$field->name] = 'int';
                break;
            case 4:
                $types[$field->name] = 'float';
                break;
            default:
                $types[$field->name] = 'string';
                break;
        }
    }
    while($row=mysqli_fetch_assoc($rs)) array_push($data,$row);
    for($i=0;$i<count($data);$i++) {
        foreach($types as $name => $type) {
            settype($data[$i][$name], $type);
        }
    }
    return $data;
}

사용법 예 :

$dbconn = mysqli_connect('localhost','user','passwd','tablename');
$rs = mysqli_query($dbconn, "SELECT * FROM Matches");
$matches = cast_query_results($rs);
// $matches is now a assoc array of rows properly casted to ints/floats/strings

---

내가 찾은 가장 쉬운 해결책 :

json_encode가 숫자처럼 보이는 값에 실제 숫자를 사용하도록 할 수 있습니다.

json_encode($data, JSON_NUMERIC_CHECK) 

(PHP 5.3.3부터)

또는 ID를 int로 캐스팅 할 수 있습니다.

$row = $result->fetch_assoc();
$id = (int) $row['userid'];

---

아니요. 테이블에 정의 된 데이터 유형에 관계없이 PHP의 MySQL 드라이버는 항상 행 값을 문자열로 제공합니다.

ID를 int로 캐스팅해야합니다.

$row = $result->fetch_assoc();
$id = (int) $row['userid'];

---

특히 쿼리 결과가 브라우저에서 자바 스크립트로 전달 될 때 Chad의 답변이 마음에 듭니다. Javascript는 숫자와 같은 엔티티를 숫자로 깔끔하게 처리하지만 숫자와 같은 엔티티를 문자열로 처리하려면 추가 작업이 필요합니다. 즉, parseInt 또는 parseFloat를 사용해야합니다.

Building on Chad's solution I use this and it is often exactly what I need and creates structures that can be JSON encoded for easy dealing with in javascript.

while ($row = $result->fetch_assoc()) {
    // convert numeric looking things to numbers for javascript
    foreach ($row as &$val) {
        if (is_numeric($val))
            $val = $val + 0;
    }
}

Adding a numeric string to 0 produces a numeric type in PHP and correctly identifies the type so floating point numbers will not be truncated into integers.

---

You can do this with...

1. mysql_fetch_field()
2. mysqli_result::fetch_field_direct or
3. PDOStatement::getColumnMeta()

...depending on the extension you want to use. The first is not recommended because the mysql extension is deprecated. The third is still experimental.

The comments at these hyperlinks do a good job of explaining how to set your type from a plain old string to its original type in the database.

Some frameworks also abstract this (CodeIgniter provides $this->db->field_data()).

You could also do guesswork--like looping through your resulting rows and using is_numeric() on each. Something like:

foreach($result as &$row){
 foreach($row as &$value){
  if(is_numeric($value)){
   $value = (int) $value;
  }       
 }       
}

This would turn anything that looks like a number into one...definitely not perfect.

---

In my project I usually use an external function that "filters" data retrieved with mysql_fetch_assoc.

You can rename fields in your table so that is intuitive to understand which data type is stored.

For example, you can add a special suffix to each numeric field: if userid is an INT(11) you can rename it userid_i or if it is an UNSIGNED INT(11) you can rename userid_u. At this point, you can write a simple PHP function that receive as input the associative array (retrieved with mysql_fetch_assoc), and apply casting to the "value" stored with those special "keys".

---

This happens when PDO::ATTR_EMULATE_PREPARES is set to true on the connection.

---

If prepared statements are used, the type will be int where appropriate. This code returns an array of rows, where each row is an associative array. Like if fetch_assoc() was called for all rows, but with preserved type info.

function dbQuery($sql) {
    global $mysqli;

    $stmt = $mysqli->prepare($sql);
    $stmt->execute();
    $stmt->store_result();

    $meta = $stmt->result_metadata();
    $params = array();
    $row = array();

    while ($field = $meta->fetch_field()) {
      $params[] = &$row[$field->name];
    }

    call_user_func_array(array($stmt, 'bind_result'), $params);

    while ($stmt->fetch()) {
      $tmp = array();
      foreach ($row as $key => $val) {
        $tmp[$key] = $val;
      }
      $ret[] = $tmp;
    }

    $meta->free();
    $stmt->close();

    return $ret;
}

---

MySQL has drivers for many other languages, converting data to string "standardizes" data and leaves it up to the user to type-cast values to int or others

---

In my case mysqlnd.so extension had been installed. BUT i hadn't pdo_mysqlnd.so. So, the problem had been solved by replacing pdo_mysql.so with pdo_mysqlnd.so.

---

I like mastermind's technique, but the coding can be simpler:

function cast_query_results($result): array
{
    if ($result === false)
      return null;

    $data = array();
    $fields = $result->fetch_fields();
    while ($row = $result->fetch_assoc()) {
      foreach ($fields as $field) {
        $fieldName = $field->name;
        $fieldValue = $row[$fieldName];
        if (!is_null($fieldValue))
            switch ($field->type) {
              case 3:
                $row[$fieldName] = (int)$fieldValue;
                break;
              case 4:
                $row[$fieldName] = (float)$fieldValue;
                break;
              // Add other type conversions as desired.
              // Strings are already strings, so don't need to be touched.
            }
      }
      array_push($data, $row);
    }

    return $data;
}

I also added checking for query returning false rather than a result-set.
And checking for a row with a field that has a null value.
And if the desired type is a string, I don't waste any time on it - its already a string.

---

I don't bother using this in most php code; I just rely on php's automatic type conversion. But if querying a lot of data, to then perform arithmetic computations, it is sensible to cast to the optimal types up front.

참고URL : https://stackoverflow.com/questions/5323146/mysql-integer-field-is-returned-as-string-in-php