列数与第1行的值计数不匹配 - 但我有27列和27个值？ PHP MySQL错误

Question

Here is my array:

$person = array(
    "fullname" => $fn,
    "skin_shade" => $_POST['skin_shade'],
    "acne" => $_POST['acne'],
    "dry_skin" => $_POST['dry_skin'],
    "oily_skin" => $_POST['oily_skin'],
    "wrinkles_aging" => $_POST['wrinkles_aging'],
    "sensative_skin" => $_POST['sensative_skin'],
    "darkspots" => $_POST['darkspots'],
    "hair_type" => $_POST['hair_type'],
    "parabens" => $_POST['parabens'],
    "sulfates" => $_POST['sulfates'],
    "mineral_oil" => $_POST['mineral_oil'],
    "silicones" => $_POST['silicones'],
    "relaxed" => $_POST['relaxed'],
    "colortreated" => $_POST['colortreated'],
    "thinning" => $_POST['thinning'],
    "growth" => $_POST['growth'],
    "braidout" => $_POST['braidout'],
    "roller" => $_POST['roller'],
    "wng" => $_POST['wng'],
    "heat" => $_POST['heat'],
    "wig" => $_POST['wig'],
    "braid" => $_POST['braid'],
    "dreadlocks" => $_POST['dreadlocks'],
    "henna" => $_POST['henna'],
    "hair_color" => $_POST['hair_color'],
    "hair_style" => $_POST['hair_style'],
);

And here is where I try to insert it and get the error:

$columns = implode(", ",array_keys($person));
$escaped_values = array_map('mysql_real_escape_string', array_values($person));
$values = implode(", ", $escaped_values);
$sql = "INSERT INTO people ($columns) VALUES ('$values')";
mysql_query($sql) or die (mysql_error());

I also used print_r on the columns and values to make sure they are the same size:

print_r($columns); echo"</br></br>";
print_r($values);

Here is the output I get for that:

fullname, skin_shade, acne, dry_skin, oily_skin, wrinkles_aging, sensative_skin, darkspots, hair_type, parabens, sulfates, mineral_oil, silicones, relaxed, colortreated, thinning, growth, braidout, roller, wng, heat, wig, braid, dreadlocks, henna, hair_color, hair_style

Chris Runo, 2, No, Yes, No, Yes, Yes, No, Straight, No, No, No, No, No, No, No, Yes, No, No, No, No, No, No, No, No, dark_brown, classic

I also checked my MySQL table and there are 27 columns.

Answer 1

$sql = "INSERT INTO people ($columns) VALUES ('$values')";

This is going to put one string literal into the VALUES clause, a single quoted string containing a comma-separated list of values:

INSERT INTO people (...columns...) VALUES ('Chris Runo, 2, No, Yes, No, Yes, Yes, No, Straight, No, No, No, No, No, No, No, Yes, No, No, No, No, No, No, No, No, dark_brown, classic')

To solve this, you could write your own quoting/escaping function and use it in array_map():

function myquote($val)
{
  return "'" . mysql_real_escape_string($val) . "'";
}

$escaped_values = array_map('myquote', array_values($person));

$values = implode(", ", $escaped_values);
$sql = "INSERT INTO people ($columns) VALUES ($values)";

Or else you could abandon the deprecated mysql_* function, and use PDO, which makes it much easier to write queries that are safe from SQL injection:

$columns = implode(", ",array_keys($person));
$params = implode(",", array_fill(0, count($person), "?"));

$sql = "INSERT INTO people ($columns) VALUES ($params)";
$stmt = $pdo->prepare($sql) or die(print_r($pdo->errorInfo(), true));
$stmt->execute(array_values($people)) or die(print_r($stmt->errorInfo(), true));