1

I'm trying to use a foreach statement in PHP to insert data from an array into a SQL database. The array can vary from 40 to 80 lines of data. I'm using the following code:

foreach ($racelap as $lap){
     $sql = "insert into laps (RaceID,Lap,time,Temp,Humidity) ";
     $sql .= "values ($RaceID,'$lap[0]','$lap[1]',$lap[2],'$lap[3]')";
    mysql_query($sql); 
}

The code doesn't give me an error, but it only adds the last line of data, and ignores all other data.

The solution is probably simple, but I can't find it myself unfortunately.

Dave
  • 3,073
  • 7
  • 20
  • 33
kevibu
  • 15
  • 6
  • You shouldn't use any mysql_*-functions. They have been deprecated since php 5.5 and completely removed in php 7.0 – Manish Oct 18 '16 at 14:23
  • 2
    You should also _actually check for errors_. – Jonnix Oct 18 '16 at 14:24
  • With no idea of what is actually in the `$racelap` array, or how your table is defined and the fact that the code looks ok, how many guesses would you like – RiggsFolly Oct 18 '16 at 14:26
  • Start by Adding some [error reporting](http://stackoverflow.com/questions/845021/how-to-get-useful-error-messages-in-php/845025#845025) to the top of your file(s) _while testing_ right after your opening PHP tag for example ` – RiggsFolly Oct 18 '16 at 14:28
  • 1
    Then if that does not help, `echo $sql;` inside the foreach loop and see if that gives you any clues ___Debugging 101___ – RiggsFolly Oct 18 '16 at 14:29
  • Sorry for using old methods, it's a temporary solution for existing coding (need to re-do it all later). There are no errors that cause the problem, and the `code` echo $sql; `code` only returns the last row of the array. The racelap array contains all numeric data from a table (which varies from 40 to 80 laps of data), temperature, laptimes.. stuff like that. – kevibu Oct 18 '16 at 14:47
  • @RiggsFolly _a kitten is strangled..._ you are a genius :) – Lelio Faieta Oct 18 '16 at 15:08
  • Have you done some processing on the `$racelap` array before getting to this code? – RiggsFolly Oct 18 '16 at 15:35

2 Answers2

0

Try this, use your query with insert multiple rows using a single SQL INSERT statement something like this : 

INSERT INTO MyTable ( Column1, Column2 ) VALUES
( Value1, Value2 ), ( Value1, Value2 )

CODE : 

$sql = "insert into laps (RaceID,Lap,time,Temp,Humidity) values  ";
foreach ($racelap as $lap){
     $sql .= "($RaceID,'$lap[0]','$lap[1]',$lap[2],'$lap[3]') ,";
}
$sql =rtrim($sql,",");
mysql_query($sql);
Dave
  • 3,073
  • 7
  • 20
  • 33
0

Now, mysql is deprecated. batter you can use mysqli. Try use with mysql bellow code :

$sql = "insert into laps (RaceID,Lap,time,Temp,Humidity) values ";

foreach ($racelap as $lap){
    $sql .= "($RaceID,'$lap[0]','$lap[1]',$lap[2],'$lap[3]'), "; 
    if(!next($racelap)) {
        $sql .= "($RaceID,'$lap[0]','$lap[1]',$lap[2],'$lap[3]')"; 
    }
}
mysql_query($sql);
Razib Al Mamun
  • 2,663
  • 1
  • 16
  • 24