PHP检查数组动态变量有效值 过滤空地址避免入库SQL报错

问题现象 #

• 联系人创建功能支持选填多地址，规则为只要填写Name字段即可正常存储联系人，地址项所有字段为空时不存储该地址
• 空地址提交时触发SQL报错：

Invalid datetime format: 1366 Incorrect integer value: '' for column clientchief_db.addresses.state_id at row 1 (SQL: insert into addresses (contact_id, street1, state_id, country_id, updated_at, created_at) values (14, , , , 2022-06-14 19:26:49, 2022-06-14 19:26:49))

现有代码 #

前端Vue表单（create.vue） #

基础联系人字段代码：

<div class="form-group mb-3 ">
 <label class="form-label">Name</label>
 <div>
   <input type="text" class="form-control" placeholder="Enter Name" v-model="form.name" required>
 </div>
</div>
<div class="form-group mb-3 ">
 <label class="form-label">Contact Id</label>
 <div>
   <input type="text" class="form-control" placeholder="Enter Contact Id" v-model="form.contact_id" required>
 </div>
</div>   

动态地址模块代码：

<div class="form-group row" v-for="(item, k) in form.addresses" :key="k" >
  <label for="exampleFormControlInput1" class="form-label">Street 1:</label>
  <div class="form-group mb-3">
     <input class="form-control" type="text" v-model="item.street1" name="street1" placeholder="Enter Street 1">  
  </div>
  <div class="form-group mb-3">
     <label for="exampleFormControlInput1" class="form-label">State</label>
     <select class="form-control"  v-model="item.state_id" name="state_id">
        <option value="">Choose State</option>
        <option value="1">Manitoba</option>
        <option value="2">Winnipeg</option>
        <option value="3">Punjab</option>
        <option value="4">Other</option>
     </select>   
   </div>
   <div class="form-group mb-3">
     <label for="exampleFormControlInput1" class="form-label">Country</label>
     <select class="form-control"  v-model="item.country_id" name="country_id">
        <option value="">Choose Country</option>
        <option value="1">Canada</option>
        <option value="2">U.S</option>
        <option value="3">India</option>
        <option value="4">Other</option>
     </select>   
    </div>
    <div class="form-group mb-3">
      <button class="btn btn-sm btn-danger" type="button" @click="removeAddress(k)">Remove</button>  
    </div>
 </div>
 <div class="form-group row">
   <div class="col-12">
     <input class="btn btn-primary form-control" type="button" value="Add Another Address" @click="addAddress()"> 
   </div>
 </div>

前端提交的请求数据格式：
{"name":"y","contact_id":"y","addresses":"[{\"street1\":\"y\",\"state_id\":\"1\",\"country_id\":\"1\"}]"}

后端控制器原有逻辑 #

public function store(Request $request)
{
    //return $request->all();
    $contact = Contacts::create([
        'name' => $request->name,
        'company_id' => Auth::user()->company_id,
    ]);
    $addresses= json_decode($request->addresses, true);
    //return $addresses;
    //[{"street1":"","state_id":"","country_id":""}]
    if (! empty($addresses)) {
     foreach($addresses as $ad) {
            $address = new Address;
            $address->contact_id = $contact->id;
            $address->street1 = $ad['street1'];
            $address->state_id = $ad['state_id'];
            $address->country_id = $ad['country_id'];
            $address->save();
        } 
   }

报错原因 #

• 遍历地址列表时未做有效性校验，全空的地址项也会执行写入逻辑
• state_id、country_id字段在数据库中为整数类型，空表单提交的空字符串''无法写入整数字段，触发类型错误
• 原有逻辑漏存contact_id字段，不符合表单设计要求

修复后代码 #

直接替换控制器store方法逻辑即可：

public function store(Request $request)
{
    // 基础字段校验
    $request->validate([
        'name' => 'required',
        'contact_id' => 'required'
    ]);

    // 存储联系人主数据
    $contact = Contacts::create([
        'name' => $request->name,
        'contact_id' => $request->contact_id,
        'company_id' => Auth::user()->company_id,
    ]);

    $addresses = json_decode($request->addresses, true);
    if (!empty($addresses)) {
        foreach ($addresses as $ad) {
            // 三个字段全为空的无效地址直接跳过
            $street1 = trim($ad['street1'] ?? '');
            $stateId = $ad['state_id'] ?? '';
            $countryId = $ad['country_id'] ?? '';
            if (empty($street1) && empty($stateId) && empty($countryId)) {
                continue;
            }

            $address = new Address;
            $address->contact_id = $contact->id;
            $address->street1 = $street1 ?: null;
            // 整数字段空值转null，避免类型错误
            $address->state_id = $stateId ? intval($stateId) : null;
            $address->country_id = $countryId ? intval($countryId) : null;
            $address->save();
        }
    }

    // 自行补充后续返回逻辑，比如跳转、返回成功响应
    return response()->json(['status' => 'success', 'msg' => '联系人创建成功']);
}

修复点说明 #

• 增加基础字段必填校验，和前端表单的required规则对齐
• 补上原逻辑遗漏的contact_id字段存储
• 遍历地址时先判断是否为全空的无效地址，命中则跳过存储
• 对整数字段做类型转换，空值统一转为null适配数据库字段类型，避免空字符串写入报错
• 增加字段兜底取值逻辑，避免前端传值缺失时出现数组下标不存在的报错
• 对street1字段做首尾去空处理，避免存入全空格的无效内容

内容的提问来源于stack exchange，提问作者Coder