Insertion issue - MySQL via PHP

Question

I have a table with the following structure

     Bay | Slot1 | Slot 2 | Slot 3 | Slot 4 | Slot 5 
     -----------------------------------------------
      1  | time1 | time 2 | time 3 | time 4 | time 5

The following code is used for insertion:

for ($i =1; $i <= $bayCount; $i++) {
    mysql_query("INSERT INTO $tableName (Bay) VALUES ($i)");

    for ($j=0; $j<$slotCount ; $j++) {  
       echo $i;
       echo $_slotColumns[$j];
       mysql_query("INSERT INTO $tableName ($_slotColumns[$j]) VALUES (slotValues[$j]) WHERE Bay = $i ");
    }

  }

The bay is an integer of incremental kind and the values for slots are passed as arrays (slotValues[$j]) Slot columns are generated using a for loop to insert. The slot values are text kind. Can someone tell me what's happening? The bays values are inserted but not slotvalues. Am I doing anything wrong?

Answer 1

Remove the WHERE Bay = $i in your second INSERT statement, this will always be false, as it doesn't exist. You can't use a WHERE in the INSERT query in this case, since it will always return false.

You also forgot to put a $ sign in front of slotValues. When using an array in a string, you should always place {} around them. (e.g. {$_slotColumns[$j]}

for ($i =1; $i <= $bayCount; $i++) {
mysql_query("INSERT INTO $tableName (Bay) VALUES ($i)");

for ($j=0; $j<$slotCount ; $j++) {  
echo $i;
echo $_slotColumns[$j];
mysql_query("INSERT INTO $tableName ({$_slotColumns[$j]}) VALUES ({$slotValues[$j]})");
}
}

Or if you want to update the fields for the inserted bay instead of adding a new record for each column, you would use an update query as follows:

for ($i =1; $i <= $bayCount; $i++) {
    mysql_query("INSERT INTO $tableName (Bay) VALUES ($i)");

    for ($j=0; $j<$slotCount ; $j++) {  
    echo $i;
    echo $_slotColumns[$j];
    mysql_query("UPDATE $tableName SET {$_slotColumns[$j]} = {$slotValues[$j]} WHERE Bay = $i;");
    }
}