how to implode two arrays into one single string for inserting mysql values?

Question

i have two arrays being gathered from form inputs both text and images

$final = array($info, $paths);
print_r($final);

this prints

Array ( [0] => Array ( [0] => '1995', [1] => 'chevrolet', [2] => 'impala', [3] => '5.7', [4] => 'bose', [5] => '165465', [6] => 'sadfasdf', [7] => '', [8] => '', [9] => 'asdfasdasdf', [10] => '', [11] => '', [12] => 'sdafasdfasd', [13] => '', [14] => '', [15] => '', [16] => '', [17] => 'asdfasdf', ) [1] => Array ( [0] => 'images/picture22677.png', [1] => 'images/picture22678.png', [2] => 'images/picture22679.jpg', ) )

PERFECT! but how do i now convert this $final array into a single string like this

'$value', '$value', '$value', '$value',

but with out the array key number to better understand me i need a string i can finnaly insert into mysql like so

mysql_query("INSERT INTO 
    auto(year, make, model, engine, sound_system, mileage, att1, att2,
    att3, att4, att5, att6, att7, att8, att9, att10, att11, att12, att13, att14, att15,    picture1,
    picture2, picture3, picture4, picture5, picture6, picture7, picture8, picture9,
    picture10, picture11, picture12)
    VALUES 
    ($finalpaths));

see for VALUES ($finalpaths)); here is where i need to have the array list into a string that would work in this case

i hope you understood what i need, thanks for any input:)

What is the purpose of having the 2-level array, and how does that convert to your expected output? — jcsanyi, Jun 26 '13 at 00:49

jeroen · Accepted Answer · 2013-06-26T00:39:52.450

1

What you should do, is use a prepared statement in PDO or mysqli. It protects you from sql injection and the mysql_* functions are deprecated.

Then your sql would look like (I normally prefer named variables like :year, etc. but this would work as well):

$sql = "INSERT INTO 
    auto(year, make, model, engine, sound_system, mileage, att1, att2,
    att3, att4, att5, att6, att7, att8, att9, att10, att11, att12, att13, att14, att15,    picture1,
    picture2, picture3, picture4, picture5, picture6, picture7, picture8, picture9,
    picture10, picture11, picture12)
    VALUES 
    (?, ?, ?, ..., ?, ?, ?)";    // as many question marks as variables

and in PDO you would execute (see the manual for complete examples) it like:

// assuming that $dbh contains your PDO database connection
$sth = $dbh->prepare($sql);
$sth->execute($final[0]);

edited Jun 26 '13 at 00:39

answered Jun 26 '13 at 00:29

jeroen

91,079
21
114
132

wow thats complicated i havent digged in pdo much yet, any other way to do it in normal php? – sdf asdfad Jun 26 '13 at 00:32
@sdfasdfad It's not that much different from your way and you avoid the whole escaping part. And it is the current *normal php* way :) – jeroen Jun 26 '13 at 00:33
@sdfasdfad PDO is normal php and is easier then mysql_* not to mention safer ! – Prix Jun 26 '13 at 00:33
Note that [prepared statements](http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php/60496#60496) are not specific to PDO. The mysqli functions support prepared statements as well. That being said, there ARE options to do this without prepared statements that are just as safe. – jcsanyi Jun 26 '13 at 00:46
@sdfasdfad Just post back here or in a new question if you run into any specific problems, you'll have it working in no time at all. – jeroen Jun 26 '13 at 00:56
@sdfasdfad What exactly is the problem? – jeroen Jun 26 '13 at 13:43

score 1 · Answer 2 · edited May 23 '17 at 12:14

While prepared statements are a better way to handle this, it is also possible to do something like this (assuming you're using the mysqli_* functions).

$items = array_merge($final[0], $final[1]);
$safeItems = array_map($items, 'mysqli_real_escape_string'); 
$sqlString = "'" . join("', '", $safeItems) . "'";

We're using array_merge() to join the two parts of $final into a single array. Alternatively, we could build from the original values like this: $items = array_merge($info, $paths).

We're using array_map() to call mysqli_real_escape_string() in order to properly escape each value to prevent SQL injection

We're using join() to combine everything together into the final output.

That being said, it looks like you're actually using the mysql_* functions. There's very good reasons why these functions should not be used. See here for more details: Why shouldn't I use mysql_* functions in PHP?

how to implode two arrays into one single string for inserting mysql values?

2 Answers2