<div>
<blockquote>
attached the array data in a file
</blockquote>
Erm?
</div>


<div>
-Thanks for spotting my mistake.<br />
<a href="https://filedb.experts-exchange.com/incoming/2014/03_w10/838270/arraydata.txt" target="_blank" class="file-inline" title="Showing the data in the array. (6 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>arraydata.txt</a>
</div>


arraydata.txt

<div>
When you get the data posted here, I'll be glad to take a look and see if I can show you what you might want to do with the PHP programming. &nbsp;While I am doing that, you might find some value in this article, if you're <a href="https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html" rel="ugc">new to PHP and MySQL programming</a>.
</div>


<div>
OK, got it. &nbsp;Going forward you may find that something like this will make it easier to read the output of print_r(). &nbsp;Preformatting will preserve the indentation and line breaks.<br />
<br />

<pre><code class="code-1 nolinks" id="code-20-39906241-1"><span>echo '&lt;pre&gt;';</span>
<span>print_r($thing);</span>
</code></pre>
Please post the CREATE TABLE statement for the data base table that you will use to import this information, thanks.
</div>


<div>
Here is one way to do it. It assumes <br />
a) field names in the database are the same as in the array<br />
b) array values are stored in variable $array<br />

<pre><code class="code-10 nolinks" id="code-20-39906267-1"><span>// string to hold fieldnames</span>
<span>$fields = ''; </span>
<span>// string to hold values</span>
<span>$values = &quot; VALUES &quot;;</span>
<span>foreach($array as $item) {</span>
<span>  $val = '';</span>
<span>  foreach ($item as $f =&gt; $v) {</span>
<span>     $fields = $fields . &quot;`$f`,&quot;;</span>
<span>     $val = $val . &quot;'$v',&quot;</span>
<span>  }</span>
<span>  $values = $values . &quot;($val),&quot;</span>
<span>}</span>
<span>$fields = trim($fields, ',');</span>
<span>$values = trim($values, ',');</span>
<span>$query = trim($query, ',');</span>
<span>// Build query here</span>
<span>$query = &quot;INSERT INTO table ($fields) $values&quot;;</span>
</code></pre>
</div>


<div>
I've attached the sql used to create the table. Nearly all the fields are included in the table. Some of the image fields near the end of the file are excluded.<br />
<br />
In answer to julianH: does your code require the fields in the database to have the same name as the array items, does there need to be the same number of fields to array items.<br />
<br />
Also in terms of overwriting duplicates what how will the code handle that?<br />
<br />
Thanks!<br />
<a href="https://filedb.experts-exchange.com/incoming/2014/03_w10/838272/sqltable.txt" target="_blank" class="file-inline" title="table file (1 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>sqltable.txt</a>
</div>


sqltable.txt

<div>
I have not checked it in detail, but it <i>looks</i> like the column names in the CREATE TABLE statement map 1:1 to the array keys. &nbsp;Is that so and is it a dependency you're willing to live with? &nbsp;If so, the mapping from the array to the query will be very easy. &nbsp;If not, some sort of adapter object can be set up to create the mapping.<br />
<br />
Regarding &quot;overwriting duplicates,&quot; please tell us what constitutes a duplicate? &nbsp;Is it one column or a set of columns? &nbsp;Any clue you can give us will be helpful! &nbsp;You can avoid getting duplicates in a lot of different ways; the exact way we choose will depend a little on how we define duplication.
</div>


<div>
They do map 1:1 but towards the end some are not required (MEDIA_IMAGE_05 - 60) and a few others. &nbsp;<br />
<br />
Duplicates have the same AGENT_REF so if the AGENT_REF exists in the database and in the imported array then the import would overwrite what is in the database.<br />
<br />
Imports are on a daily basis and unlikely to be more than 100 items.<br />
<br />
Thanks
</div>


<div>
You can mark the AGENT_REF &quot;Unique&quot; and MySQL will throw error number 1062 when there is an attempt to insert a duplicate. &nbsp;The script can trap this event and use UPDATE instead of INSERT. &nbsp;We might also be able to do something with REPLACE INTO. &nbsp;I'll see if I can set up some test cases to demonstrate the concepts. &nbsp;Have some errands to run, so it will probably be this afternoon before I have a tested code set.
</div>


<div>
Thanks &nbsp;Chris I'm away from my desk for the next couple of hours will test on return.
</div>


<div>
There are a number of ways around duplicates.<br />
<br />
One way that might work here is to do a batch import into a duplicate of your production table and then do an<br />
<br />
First create a duplicate of your data table call it import_table for the purposes of this exercise.<br />
<br />
Then as part of the import porcess clear it<br />

<pre><code class="code-1 nolinks" id="code-20-39906475-1"><span>TRUNCATE import_table;</span>
</code></pre>
<br />
And then add your rows here using the code in my earlier post.<br />
<br />
And finally<br />

<pre><code class="code-1 nolinks" id="code-20-39906475-2"><span>INSERT INOT main_table (FIELDS, ...) </span>
<span>   SELECT VALUES FROM import_table </span>
<span>   WHERE AGENT_REF NOT IN (SELECT AGENT_REF FROM main_table)</span>
</code></pre>
<br />
Other approaches involve first checking if a record exists before importing - and doing this row by row. <br />
Another alternative is to use the MySQL<br />
<br />

<pre><code class="code-1 nolinks" id="code-20-39906475-3"><span>INSERT IGNORE ... </span>
</code></pre>
To use this you need a unique key defined on your AGENT_REF field - any attempt to insert a duplicate record will be ignored if the key exists.<br />
<br />
More on this here <a href="http://dev.mysql.com/doc/refman/5.6/en/insert.html" rel="ugc">http://dev.mysql.com/doc/refman/5.6/en/insert.html</a>
</div>


<div>
Ok, so I tried the code provided by Chris Stanyon which runs to the end with no errors, but the database remains empty.<br />
<br />
I checked it is connecting to database ok. <br />
<br />
If there was an error I might be able to troubleshoot, but I'm stuck again.<br />
<br />
Thanks.
</div>


<div>
OK.<br />
<br />
Firstly, make sure you've turned on error reporting. At the very start of your script, add this:<br />
<br />

<pre><code class="code-1 nolinks" id="code-20-39907085-1"><span>error_reporting(E_ALL);</span>
<span>ini_set('display_errors', 1);</span>
</code></pre>
Then make liberal use of var_dump() so you can visualize your data:<br />
<br />

<pre><code class="code-10 nolinks" id="code-20-39907085-2"><span>$columns = array_keys($data[0]);</span>
<span>$columnList = join(',', $columns);</span>
<span>$paramList = join(',', array_map(function($column) { return &quot;:$column&quot;; }, $columns));</span>
<span></span>
<span>var_dump($columnList);</span>
<span>var_dump($paramList);</span>
<span></span>
<span>foreach ($data as $info):</span>
<span>    var_dump($info);</span>
<span>endforeach;</span>
</code></pre>
The $columnList should be a comma delimited list of all your columns (grabbed from the Keys of you data array). The $paramList should match this, but each column will be preceded by a colon.<br />
<br />
The $info should dump out each record in your array.<br />
<br />
Run that so you can at least see what data is being used.
</div>


<div>
Ok, I made the changes, no errors and the data is in the attached file. I still can't see what where is going wrong.<br />
<br />
Thanks/<br />
<a href="https://filedb.experts-exchange.com/incoming/2014/03_w10/838330/datashown.txt" target="_blank" class="file-inline" title="datashown (12 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>datashown.txt</a>
</div>


datashown.txt

<div>
Wow. That's hard work to read, but it all looks good (as far as I can tell). One thing I forgot about - telling PDO to fire exceptions!! You need to add this line straight after the <i>$dbh = new...</i> line:<br />
<br />
$dbh-&gt;setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );<br />
<br />
You could also extend the error handling to report on errors other than 1062. Update your catch block to this:<br />
<br />

<pre><code class="code-1 nolinks" id="code-20-39907195-1"><span>try {</span>
<span>	$sql-&gt;execute($info);</span>
<span>} catch(PDOException $e) {</span>
<span>	if ($e-&gt;errorInfo[1] == 1062) {</span>
<span>		echo &quot;We have a duplicate Record.&quot; . PHP_EOL;</span>
<span>	} else {</span>
<span>		echo &quot;We have some other error: &quot; . $e-&gt;errorInfo[2] . PHP_EOL;</span>
<span>	}</span>
<span>}</span>
</code></pre>
If you still don't get any joy, post up your full code.
</div>


<div>
Ok, so the error is <br />
<br />
&quot;We have some other error: Unknown column 'MEDIA_IMAGE_05' in 'field list' We have some other error: Unknown column 'MEDIA_IMAGE_05' in 'field list' We have some other error: Unknown column 'MEDIA_IMAGE_05' in 'field list' &quot;<br />
<br />
I'll recheck the database and retry.
</div>


<div>
OK. You have a key in your data array called MEDIA_IMAGE_05 but it doesn't exist in your table. You can either remove it from the array or add it to your table.
</div>


<div>
<div class="content wysiwyg-content">
Hi,<br />
I have an array of data from an external file which I want to import into my database. So far I can print the ARRAY to the screen. <br />
<br />
I have attached the array data in a file (this is how it looks when I print it to the screen) which shows an example with three entries. When I import it into my database I want it to overwrite any duplicates. <br />
<br />
I presume I need a foreach loop to read and insert the data?<br />
<br />
I'm using PHP / MySQL.<br />
<br />
Thanks,<br />
nl
</div>
</div>


Hi,
I have an array of data from an external file which I want to import into my database. So far I can print the ARRAY to the screen. 

I have attached the array data in a file (this is how it looks when I print it to the screen) which shows an example with three entries. When I import it into my database I want it to overwrite any duplicates. 

I presume I need a foreach loop to read and insert the data?

I'm using PHP / MySQL.

Thanks,
nl

Add Array data to database

PHP is a widely-used server-side scripting language especially suited for web development, powering tens of millions of sites from Facebook to personal WordPress blogs. PHP is often paired with the MySQL relational database, but includes support for most other mainstream databases. By utilizing different Server APIs, PHP can work on many different web servers as a server-side scripting language.

MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.