由于错误的数组数据类型，无法插入到 mysql 表中 [英] unable to insert into mysql table due to wrong array datatype

问题描述

以下是从中检索数据并尝试插入到 mysql 表中的数组，但在插入多于 1 行时运行良好，仅插入 1 行时失败.

Following is the array from which the data are retrieved and tried to be inserted into mysql table but works well when more than 1 row to be inserted and fails when only 1 row to be inserted.

// The structure of the array is as follows
Array
(
    [Row] => Array
        (
            [0] => Array
                (
                    [PIN] => 1274
                    [DateTime] => 2018-04-07 09:28:16
                    [Verified] => 15
                    [Status] => 3
                    [WorkCode] => 0
                )

            [1] => Array
                (
                    [PIN] => 157
                    [DateTime] => 2018-04-07 10:22:56
                    [Verified] => 15
                    [Status] => 3
                    [WorkCode] => 0
                )
    // these are the raw punch data from biometric machine

以下是当前正在使用的代码.

Following is the piece of code which currently being used.

  if(isset($array_att_logs) && isset($array_att_logs['Row'])) {     
      foreach ($array_att_logs['Row'] as $value) {
          $emp_code =  $value['PIN'];       // line 92                                  $dateNTime =  $value['DateTime']; //line 93

$punch_query = "INSERT IGNORE INTO punching_data_table (emp_code, date_time,in_out_status) VALUES ('$emp_code', '$dateNTime','$in_out_status')";

 $punch_result = mysql_query($punch_query);
 echo mysql_error();
         }       
     } echo var_dump($array_att_logs);

请注意，$in_out_status 是在别处明确定义的，这似乎根本没有任何问题.我已经使用 var_dump() 检查了该数组是否存在数据就好了.问题是如果有一行要插入到表中，则会显示以下错误.

Please note that the $in_out_status is defined explicitly elsewhere which does not seem to have any problem at all.I have checked using var_dump() that the array exists with data just fine. The problem is if there is a single row to be inserted into the table then following error is shown.

如果要插入到表中的行超过 1 行，那么它就可以正常工作.可能是我错误地定义了 $emp_code 和 $dateNTime.

这是单行时的样子.

 echo " <pre>"; 
    print_r($array_att_logs); 
    echo "/<pre>"; 

Array
(
    [Row] => Array
        (
            [PIN] => 406
            [DateTime] => 2018-05-06 14:40:09
            [Verified] => 1
            [Status] => 3
            [WorkCode] => 0
        )

)
/

using var_dump it looks like below

array(1) { ["Row"]=> array(5) { ["PIN"]=> string(3) "406" ["DateTime"]=> string(19) "2018-05-06 14:40:09" ["Verified"]=> string(1) "1" ["Status"]=> string(1) "3" ["WorkCode"]=> string(1) "0" } }

推荐答案

你遇到问题的原因是$array_att_logs 的数组结构在只有 1 行时和不止一排.因此，您需要针对这种情况进行测试并更改遍历数组的方式.检查是否只有 1 行的最简单方法是查看 $array_att_logs['Row'] 的内容是否为数组数组.为此，我们使用 array_values($array_att_logs['Row']) 来确保我们有一个索引为 0 的条目，然后测试该值以查看它是否是一个数组.然后我们使用该结果来更改 foreach 迭代的值:

The reason that you are having the problem is that the array structure of $array_att_logs is different when there is only 1 row to when there are more than one row. Thus you need to test for that situation and change the way you iterate through the array. The easiest way to check if there is only 1 row is to see if the contents of $array_att_logs['Row'] is an array of arrays. To do this we use array_values($array_att_logs['Row']) to ensure that we have an entry with an index of 0, and then test that value to see if it is an array. Then we use that result to change the value being iterated by the foreach:

$row = array_values($array_att_logs['Row']);
$multirows = is_array($row[0]);
foreach (($multirows ? $array_att_logs['Row'] : $array_att_logs) as $value) {
    $emp_code =  $value['PIN'];
    ...

这篇关于由于错误的数组数据类型，无法插入到 mysql 表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！