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?

You should check out the basics for SQL syntax. `INSERT` adds a new row to the database and doesnt have a `WHERE`. What you need is `UPDATE`. Although it would be better to create the entire query at once. Best thing to do is to write out the full query manually and then check what parts you need to alter. Also dont use `mysql_query` as its deprecated. Check out `mysqli` or `PDO` — Hugo Delsing, Mar 07 '13 at 09:57
Which is the value of `slotColumns` and `slotValues` in one of those loops? — Alvaro, Mar 07 '13 at 09:57
@Steve I pass the number 5 as slot count as iterate to get the slot columns and slot values are time1 time 2 ... — DesperateLearner, Mar 07 '13 at 11:21

score 1 · Answer 1 · answered Mar 07 '13 at 09:58

1

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

answered Mar 07 '13 at 09:58

www

4,365
1
23
24

Menno · Accepted Answer · 2013-03-07T10:10:08.763

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

mysql_query("UPDATE $tableName SET $_slotColumns[$j] = '$slotValues[$j]' WHERE Bay = '$i'"); This worked! update and not insert! thanks a lot for the help everyone — DesperateLearner, Mar 07 '13 at 11:27

score 0 · Answer 3 · answered Mar 07 '13 at 09:57

0

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

answered Mar 07 '13 at 09:57

tamilps2

46
1
5

Alvaro · Answer 4 · 2013-03-07T10:04:41.557

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

score 0 · Answer 5 · answered Mar 07 '13 at 10:08

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.

Insertion issue - MySQL via PHP

5 Answers5