Inputting array into mysql table

#1

Hello,

I have been working on a local database script in php to parse a CSV file and insert it into a table. Everything in the script works up to the point of putting it into the table. My question is, how do I implode() the array of $pusher? When I try to do it with how it is currently coded, I get an array to string error.

This is my current code:

<?php


//Connecting to database
$servername = "localhost";
$username = "root";
$password = "GreenSun10";
$databasename = "studio";

$conn = mysqli_connect($servername, $username, $password, $databasename);

//Check Connection
//if (!$conn) {

//die("Connection failed: " . mysqli_connect_error());

//}

//echo "Connected Successfully";

//Scanning Directory
$dollar = glob("C:\Users\user\Desktop\CSV\DH\*");

//field Check
//if ($dollar != null){

//print_r("Data Collected");
//print_r($dollar);

//}

//else{
	//die("Data Not Collected!");

//}

foreach($dollar as $value) {

	//opening $dollar field in array
	$doc = fopen($value, "r");
	while (($data = fgetcsv($doc, 1002, "|")) !== FALSE){

		//remove first field
		$fruit = array_shift($data);
		print_r("Shifted");
		//print_r($data);

		//remove last string
		array_pop($data);
		print_r("Popped");
		//print_r($data);

		//remove last string again
		array_pop($data);
		print_r("Popped");
		//print_r($data);

		//remove last string a third time
		array_pop($data);
		print_r("Popped");
		//print_r($data);

		//split array into smaller arrays
		$chunker = array_chunk($data, 1);
		print_r("Chunked Out");
		//print_r($chunker);

		//splitting the thumbnails
		$thumb = implode(";" , $chunker[5]);
		//print_r($thumb);
		$thumper = explode(";", $thumb);
		//print_r($thumper);
		
		//making an array of keywords
		$keyword = implode(";" , $chunker[6]);
		print_r($keyword);
		$keyser = explode(";", $keyword);
		print_r($keyser);

		//popping last two off of $chunker
		array_pop($chunker);
		array_pop($chunker);
		//print_r($chunker);

		//merging all the arrays
		$pusher = array_merge($chunker, $thumper, $keyser);
		print_r($pusher);

		$s = implode($pusher);

		$query = "INSERT INTO Quarantine (Url, EmbedUrl, Title, Duration, DateAdded, Thumb1, Thumb2, Thumb3, Thumb4, Thumb5, Thumb6, Thumb7, Thumb8, Thumb9, Thumb10, Keyword1, Keyword2, Keyword3, Keyword4, Keyword5, Keyword6, Keyword7, Keyword8, Keyword9, Keyword10) VALUES$s";

		mysqli_query($conn, $query);
		//Field Check
		print_r("Data Entered Into Quarantine");
	}

	//closing dollar variable
	fclose($doc);

}

//closing connection
mysqli_close($conn);

?>

And so that everything is easier to see, here is what I get when the above code is ran:

ShiftedPoppedPoppedPoppedChunked Outkeyword one;keywordtwoArray ( [0] => keyword one [1] => keyword two ) Array ( [0] => Array ( [0] => url ) [1] => Array ( [0] => embedded ) [2] => Array ( [0] => Title) [3] => Array ( [0] => 0m11s ) [4] => Array ( [0] => 2007-05-26 ) [5] => thumb one [6]thumb two [7] thumb three [8] => thumb four [9] => thumb five [10] => thumb six [11] => thumb seven [12] => thumb eight [13] => thumb nine [14] => thumb ten [15] => keyword one[16] => keyword two )
( ! ) Notice: Array to string conversion in C:\wamp\www\XH.php on line 89
Call Stack

Time Memory Function Location

1 0.0130 386368 {main}( ) …\XH.php:0
2 0.0490 414496 implode ( ) …\XH.php:89

( ! ) Notice: Array to string conversion in C:\wamp\www\XH.php on line 89
Call Stack

Time Memory Function Location

1 0.0130 386368 {main}( ) …\XH.php:0
2 0.0490 414496 implode ( ) …\XH.php:89

( ! ) Notice: Array to string conversion in C:\wamp\www\XH.php on line 89
Call Stack

Time Memory Function Location

1 0.0130 386368 {main}( ) …\XH.php:0
2 0.0490 414496 implode ( ) …\XH.php:89

( ! ) Notice: Array to string conversion in C:\wamp\www\XH.php on line 89
Call Stack

Time Memory Function Location

1 0.0130 386368 {main}( ) …\XH.php:0
2 0.0490 414496 implode ( ) …\XH.php:89

( ! ) Notice: Array to string conversion in C:\wamp\www\XH.php on line 89
Call Stack

Time Memory Function Location

1 0.0130 386368 {main}( ) …\XH.php:0
2 0.0490 414496 implode ( ) …\XH.php:89
Data Entered Into Quarantine

#2

Allow me to introduce you to Mysql’s “LOAD DATA”. (Assumming you are using MySQL)

The LOAD DATA statement reads rows from a text file into a table at a very high speed.

Example: http://www.mysqltutorial.org/import-csv-file-mysql-table/

(And yes, it can be used with Php)

2 Likes
#3

Save us counting, which is line 89?

What does $query look like, before you run it? It looks like there’s a missing space to me.

In here,

$s = implode($pusher);

did you mean to specify no separator string? So you’ll just have a list of values all run into each other, rather than comma-separated as you would normally put in a query if you’re not using prepared statements?

mysqli_query($conn, $query);

It’d probably be a good idea to check whether the query has executed or not.

#4

100% agree with LOAD DATA while running locally to the database.

Line 89 is the implode line that droop identified;

Implode is not recursive; you’re applying it to a multidimensional array (an array_chunk’d array becomes a multidimensional array, even if the chunks are of size 1), so implode tries to convert each subarray to a string, generating the notice level event you see.

$chunker is your problem array here. There exists no reason for you to have chunked it at all; but if you’re intending on doing so, you’ll have to ‘unchunk’ it at the end in some manner. Something to the tune of $unchunker = array_map($chunker,function ($chunk) { return implode($chunk); });