Insert array in to MySQL with PHP

Question

I am trying to insert an array in to MySQL using PHP. I followed the excellent advice given on here to use the implode command and it worked great for one array, but this one seems to be dying. This array is slightly different than the other, but I don't know how to explain the difference.

Here is my code:

    $sql = array(); 
foreach( $ride_detail as $row ) {
    $sql[] = '('.$row['id'].', "'.mysql_real_escape_string($row['name']).'",
  "'.$row['version'].'")';
}
mysql_query('INSERT IGNORE INTO ride (ride_id, name, version) VALUES '.implode(',', $sql));

I'm getting this message over and over again.

Warning: Illegal string offset 'id' in ride_details.php on line 60

Warning: Illegal string offset 'name' in ride_details.php on line 60

Warning: Illegal string offset 'version' in ride_details.php on line 61

The content of my array (using print_r) is:

Array ( [id] => 21570117 [name] => Night ride home from work [start_date_local] => 1347302039 [elapsed_time] => 53:56 [moving_time] => 52:04 [distance] => 12.6 >>[average_speed] => 14.5 [elevation_gain] => 474 [location] => Englewood, CO [start_latlng] => Array ( [0] => 39.547792011872 [1] => -104.86300536431 ) [end_latlng] => Array ( [0] => 39.655485888943 [1] => -104.88656991161 ) [version] => 1355428869 [athlete] => Array ( >>[id] => 832001 [name] => Bob Kratchet [username] => bob_kratchet ) [bike] => Array ( [id] => 281303 [name] => Giant Allegre commuter ) [maximum_speed] => 29.3 [calories] => 372 >[average_power] => 107 [commute] => 1 )

I am a complete noob...

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial. — Kermit
– Kermit, Commented Jan 15, 2013 at 23:39
One other piece of advice - if you are ever going to want to use the information in the call in a query, have multiple rows in a different table - storing more than a single piece of data in any field is going to bite you in the backside sooner or later. You should read up on data normalisation — Basic
– Basic, Commented Jan 16, 2013 at 0:11
That's print_r($ride_detail). My plan is to have each piece of the array in a separate column, each separated out by rows. I didn't put my whole example here because the dataset was too huge. — user1981967
– user1981967, Commented Jan 16, 2013 at 16:09

Niet the Dark Absol · Accepted Answer · 2013-01-16 00:12:46Z

1

Since your $ride_detail is just one array, $row is 21570117 (integer), Night ride home from work (string), and so on, one by one. The code then attempts to get the id key of each element, then the name key, and so on, generating a [expletive]-ton of error messages as it goes.

It looks like you're intending to have $ride_detail be an array of arrays, or you don't actually want a foreach loop at all.

answered Jan 16, 2013 at 0:12

Niet the Dark Absol

326k86 gold badges480 silver badges604 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1981967 Over a year ago

Yes, correct. There are 23 values, but for my proof of concept I was only wanting to import 3 of the values in to a row. If I prepare the row do I have to import every value? How do I get around it trying to get an id for each element, because it's still an array right? I'm a complete noob.

echo_Me · Accepted Answer · 2013-01-16 00:34:40Z

0

try this

   foreach( $ride_detail as $row ) {
     $sql = array( $row['id'], mysql_real_escape_string($row['name']), $row['version'])  ;

         }
  mysql_query('INSERT IGNORE INTO ride (ride_id, name, version) VALUES  ($sql[0] ,$sql[1] , $sql[2] ) ');

answered Jan 16, 2013 at 0:34

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

1 Comment

user1981967 Over a year ago

Thank you, I was just reading last night that I shouldn't use mysql_ anymore. Unfortunately I was learning through w3schools and tizag where that is the method used. I will try to switch to PDO, do you have an example for that?

Collectives™ on Stack Overflow

Insert array in to MySQL with PHP

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related