sql多插入函数的参数错误无效

Question

Can't seem to figure out what the problem is with this. I have used this same method before, but not with the inner for each for each user. I am getting an Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: parameter was not defined', but from what I can see everything matches up correctly.

I have user_id and day setup as a unique key group in the db. $output has stored values (1 or 0) for every hour of the day for each day of the week. This entire week of values of each hour are inserted for each user on a per day basis (only the user_id and day values change for the inserts).

$user_set = array(1, 5, 8, 24) //user ids to insert for

$weekdays = array(1 => 'mon', 2 => 'tue', 3 => 'wed', 4 => 'thu', 5 => 'fri', 6 => 'sat', 7 => 'sun');

$hourDefs = array('12am', '1am', '2am', '3am', '4am', '5am', '6am', '7am', '8am', '9am', '10am', '11am', '12pm', '1pm', '2pm', '3pm', '4pm', '5pm', '6pm', '7pm', '8pm', '9pm', '10pm', '11pm');


//build query for multi insert
$sql = 'INSERT INTO weekly_sched (
        user_id,
        day,
        12a,
        1a,
        2a,
        3a,
        4a,
        5a,
        6a,
        7a,
        8a,
        9a,
        10a,
        11a,
        12p,
        1p,
        2p,
        3p,
        4p,
        5p,
        6p,
        7p,
        8p,
        9p,
        10p,
        11p
    ) VALUES ';

$sqlDupe = 'ON DUPLICATE KEY UPDATE
    12a = VALUES(12a),
    1a = VALUES(1a),
    2a = VALUES(2a),
    3a = VALUES(3a),
    4a = VALUES(4a),
    5a = VALUES(5a),
    6a = VALUES(6a),
    7a = VALUES(7a),
    8a = VALUES(8a),
    9a = VALUES(9a),
    10a = VALUES(10a),
    11a = VALUES(11a),
    12p = VALUES(12p),
    1p = VALUES(1p),
    2p = VALUES(2p),
    3p = VALUES(3p),
    4p = VALUES(4p),
    5p = VALUES(5p),
    6p = VALUES(6p),
    7p = VALUES(7p),
    8p = VALUES(8p),
    9p = VALUES(9p),
    10p = VALUES(10p),
    11p = VALUES(11p)
';

//set starting values
$insertQuery = array();
$insertData = array();
$n = $n2 = 0;

//build bindings for user for each day
foreach ($user_set as $key)
{       
    // loop through each weekday
    foreach($weekdays as $day=>$day_value)
    {
        //build day values for the user
        $insertQuery[] = '(
            :user_id'.$n.'-'.$n2.',
            :day'.$n.'-'.$n2.',
            :12a'.$n.'-'.$n2.',
            :1a'.$n.'-'.$n2.',
            :2a'.$n.'-'.$n2.',
            :3a'.$n.'-'.$n2.',
            :4a'.$n.'-'.$n2.',
            :5a'.$n.'-'.$n2.',
            :6a'.$n.'-'.$n2.',
            :7a'.$n.'-'.$n2.',
            :8a'.$n.'-'.$n2.',
            :9a'.$n.'-'.$n2.',
            :10a'.$n.'-'.$n2.',
            :11a'.$n.'-'.$n2.',
            :12p'.$n.'-'.$n2.',
            :1p'.$n.'-'.$n2.',
            :2p'.$n.'-'.$n2.',
            :3p'.$n.'-'.$n2.',
            :4p'.$n.'-'.$n2.',
            :5p'.$n.'-'.$n2.',
            :6p'.$n.'-'.$n2.',
            :7p'.$n.'-'.$n2.',
            :8p'.$n.'-'.$n2.',
            :9p'.$n.'-'.$n2.',
            :10p'.$n.'-'.$n2.',
            :11p'.$n.'-'.$n2.'
        )';
        $insertData['user_id'.$n.'-'.$n2] = $key;
        $insertData['day'.$n.'-'.$n2] = $day;
        $insertData['12a'.$n.'-'.$n2] = $output[$day_value]['12am'];
        $insertData['1a'.$n.'-'.$n2] = $output[$day_value]['1am'];
        $insertData['2a'.$n.'-'.$n2] = $output[$day_value]['2am'];
        $insertData['3a'.$n.'-'.$n2] = $output[$day_value]['3am'];
        $insertData['4a'.$n.'-'.$n2] = $output[$day_value]['4am'];
        $insertData['5a'.$n.'-'.$n2] = $output[$day_value]['5am'];
        $insertData['6a'.$n.'-'.$n2] = $output[$day_value]['6am'];
        $insertData['7a'.$n.'-'.$n2] = $output[$day_value]['7am'];
        $insertData['8a'.$n.'-'.$n2] = $output[$day_value]['8am'];
        $insertData['9a'.$n.'-'.$n2] = $output[$day_value]['9am'];
        $insertData['10a'.$n.'-'.$n2] = $output[$day_value]['10am'];
        $insertData['11a'.$n.'-'.$n2] = $output[$day_value]['11am'];
        $insertData['12p'.$n.'-'.$n2] = $output[$day_value]['12pm'];
        $insertData['1p'.$n.'-'.$n2] = $output[$day_value]['1pm'];
        $insertData['2p'.$n.'-'.$n2] = $output[$day_value]['2pm'];
        $insertData['3p'.$n.'-'.$n2] = $output[$day_value]['3pm'];
        $insertData['4p'.$n.'-'.$n2] = $output[$day_value]['4pm'];
        $insertData['5p'.$n.'-'.$n2] = $output[$day_value]['5pm'];
        $insertData['6p'.$n.'-'.$n2] = $output[$day_value]['6pm'];
        $insertData['7p'.$n.'-'.$n2] = $output[$day_value]['7pm'];
        $insertData['8p'.$n.'-'.$n2] = $output[$day_value]['8pm'];
        $insertData['9p'.$n.'-'.$n2] = $output[$day_value]['9pm'];
        $insertData['10p'.$n.'-'.$n2] = $output[$day_value]['10pm'];
        $insertData['11p'.$n.'-'.$n2] = $output[$day_value]['11pm'];

        // increase for next day
        $n2++;
    }
    // increase for next user
    $n++;
}

if (!empty($insertQuery)) {
    $sql .= implode(', ', $insertQuery);
    $sql .= ' '.$sqlDupe;
    $stmt = $db->prepare($sql);
    $stmt->execute($insertData);
        }

Answer 1

Change all the hyphens in the parameter names to underscores. For instance,

        :user_id'.$n.'-'.$n2.',

should be:

        :user_id'.$n.'_'.$n2.',

It's interpreting this as :user_id$n minus $n2.