将字段留空时 PHP 表单错误

Question

我有一个 PHP 表单，当我将字段留空时会出现错误。

抱歉，在重复键更新 clientID='45'、clientName='Frank' 时将此客户端 sql=insert 添加到 db1.cleints (clientID, clientName) 值 (45, Frank) 时出错你的 SQL 语法；检查与您的 MySQL 服务器版本相对应的手册，了解在第 1 行“clientName”附近使用的正确语法

更新:我已经包含了代码是什么。请忽略我的上述只是一个修改后的示例这一事实。

 $sql = "insert into $DB.theater (dsc_id, dsc_lname, dsc_fname, distributor_id, distributor_name, distributor_theater_id, manager_id, manager_name, is_new_theater, name, phone_area, phone_exch, phone_local, phone_ext, street, city, state_prov, country, postal, web_site_url, manager_gender, theater_manager_name, email, location_desc, square_footage, year_founded, colors_techs, stylists, assistants, active_chairs, booth_renters, services, purchase_volume, products, other_lines, other_lines_txt, percent_women, demog_lt_20, demog_lt_35, demog_lt_55, demog_ge_55, avg_sale, avg_cut, avg_color, avg_hilite, rating, can_display, retail_sf, grade, dsc_notes) values ($dsc_id, '$dsc_lname', '$dsc_fname', $distributor_id, '$distributor_name', '$distributor_theater_id', $manager_id, '$manager_name', '$is_new_theater', '$name', '$phone_area', '$phone_exch', '$phone_local', '$phone_ext', '$street', '$city', '$state_prov','$country', '$postal', '$web_site_url', '$manager_gender', '$theater_manager_name', '$email', '$location_desc', '$square_footage', '$year_founded', '$colors_techs', '$stylists', '$assistants', '$active_chairs', '$booth_renters', '$services', '$purchase_volume', '$products', '$other_lines','$other_lines_txt', '$percent_women', '$demog_lt_20', '$demog_lt_35', '$demog_lt_55', '$demog_ge_55', '$avg_sale', '$avg_cut', '$avg_color', '$avg_hilite', '$rating', '$can_display', '$retail_sf', '$grade', '$dsc_notes') on duplicate key update dsc_id=$dsc_id, dsc_lname='$dsc_lname', dsc_fname='$dsc_fname', distributor_id=$distributor_id, distributor_name='$distributor_name', distributor_theater_id='$distributor_theater_id', manager_id=$manager_id, manager_name='$manager_name', is_new_theater='$is_new_theater', name='$name', phone_area='$phone_area', phone_exch='$phone_exch', phone_local='$phone_local', phone_ext='$phone_ext', street='$street', city='$city', state_prov='$state_prov', country='$country', postal='$postal', web_site_url='$web_site_url', manager_gender='$manager_gender', theater_manager_name='$theater_manager_name', email='$email', location_desc='$location_desc', square_footage='$square_footage', year_founded='$year_founded', colors_techs='$colors_techs', stylists='$stylists', assistants='$assistants', active_chairs='$active_chairs', booth_renters='$booth_renters', services='$services', purchase_volume='$purchase_volume', products='$products', other_lines='$other_lines', other_lines_txt='$other_lines_txt', percent_women='$percent_women', demog_lt_20='$demog_lt_20', demog_lt_35='$demog_lt_35', demog_lt_55='$demog_lt_55', demog_ge_55='$demog_ge_55', avg_sale='$avg_sale', avg_cut='$avg_cut', avg_color='$avg_color', avg_hilite='$avg_hilite', rating='$rating', can_display='$can_display', retail_sf='$retail_sf', grade='$grade', dsc_notes='$dsc_notes'";

Answer 1

insert into db1.cleints (clientID, clientName)
values (45, Frank)
on duplicate key update clientID='45', clientName='Frank' 

第一个 Frank 没有被引用。

此外，值得注意的是，重复键更新的全部目的是能够在记录不存在时插入记录，或者在记录已存在时更新记录。您正在尝试使用已有的相同值进行更新。也许你想要INSERT IGNORE ...

更新问题的答案:

我看来我们必须猜测。我有根据的猜测是您没有转义输入数据。相反，您会盲目地将其注入(inject)到 SQL 代码中。当数字字段留空时，您不会将其替换为适当的 NULL；而是将其替换为空值。它只是被转换为(空白)字符串:

echo "VALUES($one, $two, $three)";

...生成:

VALUES(1, , 3)

如果您在此类字段中输入字母，或者输入 O'Hara，它也可能会失败。