Inserting data into table from exploded string

[papamole]

Hi

 

I am trying (without success) to add info to a temporary table from data retrieved from another table.

 

The code i am using is shown below. What I am trying to do is from one table, explode the field data and create array's, then add the multiple parts of the array to a new (temporary) table.

 

I know its not good to do insert to a table in a loop, but... 

 

When I run the script, it only adds the first of the exploded array to the table, but the output to the browser, shows all the data (as I would expect it to be added to the table)

 

In the case of    Aniseed China Star  it will only add  Bay  to the table, where I want to add all 10 oils.

 

Can anyone help me with any ideas where I am going wrong.

 

Thanks in advance

 

Paul

 

 

 

<?php

 

$query = "SELECT * FROM essential_oils ORDER BY oil_name"; // Select Everything From The Table

$result = mysql_query($query);

while($row = mysql_fetch_row($result)) // While there is something to do, Do It!

{

 $pieces = explode(",", $row[7]); // Split the String into each piece

 

foreach ($pieces as $piece) // For each array add it to another temp table

{

$q = "INSERT INTO oil_blend_with (id, name) VALUES ('$row[1]', '$piece')";

$r = mysql_query($q);

echo "&nbsp&nbsp&nbsp".$row[1]."&nbsp&nbsp".ucwords($piece)."<br />"; // display on the screen the loop output to test. remove later

}

 

}

 

 

?>

 

 

Example of output to browser :-

 

 

Get Next Row
   Anise  
Get Next Row
   Anise star  
Get Next Row
   Aniseed China Star  Bay
   Aniseed China Star   Cedarwood
   Aniseed China Star   Eucalyptus
   Aniseed China Star   Dill
   Aniseed China Star   Fennel
   Aniseed China Star   Ginger
   Aniseed China Star   Mandarin
   Aniseed China Star   Petitgrain
   Aniseed China Star   Peppermint
   Aniseed China Star   Rosemary.
Get Next Row
   Apricot  

 

etc..

 

Cheers 

 

Paul

[.josh]

is id in your temp table set to be unique/auto-increment?

 

also do this, does it output any errors?

 

$r = mysql_query($q) or die(mysql_error());

[Barand]

Define the primary key of the "oil_blend_with" table as

PRIMARY KEY (id, name)

And don't have it as a temporary table, make it permanent. That is how column 7 data should be stored.

 

edit:

Except you should be storing ids in the second table and not the names.

 

I meant to add some code to write data in a single insert:

$query = "SELECT * FROM essential_oils ORDER BY oil_name"; // Select Everything From The Table
$result = mysql_query($query);
while($row = mysql_fetch_row($result)) // While there is something to do, Do It!
{
    $pieces = explode(",", $row[7]); // Split the String into each piece
    
    $data = array(); 
    foreach ($pieces as $piece) // For each array add it to another temp table
    {
        $data = sprintf("('%s', '%s')", $row[1], $piece);
        // display on the screen the loop output to test. remove later
        echo "&nbsp&nbsp&nbsp".$row[1]."&nbsp&nbsp".ucwords($piece)."<br />";
    }
     
}
$q = "INSERT INTO oil_blend_with (id, name) VALUES " . join(',', $data);
$r = mysql_query($q);

And as you are only interested in two fields from essential oils table, those are the only ones you should select (not "select * ")

Edited January 30, 2014 by Barand

[papamole]

Josh & Barand

 

Thanks for your inputs. The temp table field id was set to unique so as soon as I changed it, the whole thing works ... such a stupid thing to miss.

 

I have also tried the code for a single insert.

 

Thanks again .. much appreciated

 

Paul 

[Barand]

I missed the "[ ]", it should have been

$data[] = sprintf("('%s', '%s')", $row[1], $piece);