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;");
    }
}

Answer 2

I guess that you should Update rater than Insert in second loop. GL!

Answer 3

When you are using array variables inside "" try putting them in {}. {$_slotcolumns[$j]}

Answer 4

You might have a problem with quotes:

mysql_query("INSERT INTO $tableName (".$_slotColumns[$j].") VALUES ('".slotValues[$j]."') WHERE Bay = $i ");

Or you an also do:

mysql_query("INSERT INTO $tableName ({$_slotColumns[$j]}) VALUES ('{slotValues[$j]}') WHERE Bay = $i ");

You have to notice that a normal query needs quotes like this:

mysql_query("INSERT INTO $tableName (columnName) VALUES ('value') WHERE Bay = $i ");

Answer 5

You have two separate insertions, so you're getting two separate rows and the first one will only contain the Bay.

It looks to me like you wanted one query:

$query = "INSERT INTO $tableName (Bay, " . implode(',', $_slotColumns) . ") ";
$query .= "VALUES(" . implode(',', $slotValues). ")";
mysql_query($query);

I have no idea what you're trying to do with the WHERE; that makes no sense for INSERT.