Need help on insert comma separated data in mysql database using PHP

Question

Demo data which already have in the database table:

INSERT INTO `csvtbl` (`ID`, `SKU`, `Product_Name`, `Model`, `Make`, `Year_From`, `Year_To`) VALUES
(1, 'C2AZ-3B584-AR', 'Power Steering Pump Seal Kit (Eaton Pump)', 'Galaxie', 'Ford', '1960', '1965'),
(2, 'C2AZ-3B584-AR', 'Power Steering Pump Seal Kit (Eaton Pump)', 'Thunderbird ', 'Fordtrest', '1960', '1965');

I have using below code and inserted years with comma(,) separated in table:

INSERT INTO `diff_yearstbl` (`ID`, `SKU`, `Product_Name`, `Model`, `Make`, `Year`) VALUES
(1, 'C2AZ-3B584-AR', 'Power Steering Pump Seal Kit (Eaton Pump)', 'Galaxie', 'Ford', '1960,1961,1962,1963,1964,1965'),
(2, 'C2AZ-3B584-AR', 'Power Steering Pump Seal Kit (Eaton Pump)', 'Thunderbird ', 'Fordtrest', '1960,1961,1962,1963,1964,1965');

Years data are working good, but I want to insert data Make and Model like same as Years, But this time just insert the data with comma (,) separated in one row like below example using SKU field. So need to merge the above two record in one row like below record.

INSERT INTO `diff_yearstbl` (`ID`, `SKU`, `Product_Name`, `Model`, `Make`, `Year`) VALUES
(1, 'C2AZ-3B584-AR', 'Power Steering Pump Seal Kit (Eaton Pump)', 'Galaxie, Thunderbird', 'Ford, Fordtrest' '1960,1961,1962,1963,1964,1965');

Below are the code what I have done:

    $query = "select Year_TO - Year_From as diff_years, ID, SKU,Product_Name,Model,Make,Year_From,Year_To from csvtbl";
$result = mysql_query($query, $connect );

//$result = $db->query($select_test)->fetchAll();

if (count($result) > 0) 
{

    while($QryRow = mysql_fetch_assoc($result)) 
    {
        $diff_years = $QryRow['diff_years'];
        $Year_From = $QryRow['Year_From'];
        $SKU = $QryRow['SKU'];
        $Product_Name = $QryRow['Product_Name'];
        $Model = $QryRow['Model'];
        $Make = $QryRow['Make'];

        $years= array();
        for ($x = $QryRow['Year_From']; $x <= $QryRow['Year_To']; $x++) 
        {           
            $years[] = $x;    
        }

        $query_insert = "INSERT INTO diff_yearstbl(SKU,Product_Name,Model,Make,Year) VALUES('".$SKU."','".$Product_Name."','".$Model."','".$Make."','".implode('|',$years)."')";
        $s_insert = mysql_query($query_insert, $connect ); 
    }
} 
else 
{
    echo "<p>Nothing matched your query.</p>";
}

    ?>

Please help about the same.

Switch simple quotes and double quotes in your $query_insert — Gwendal
– Gwendal, Commented Feb 19, 2016 at 5:57
Yes i want insert the all years in the years column with come (,) separated. — Nishant Patel
– Nishant Patel, Commented Feb 19, 2016 at 5:59

Kickstart · Accepted Answer · 2016-02-19 15:50:24Z

2

In a single SQL statement, coping with up to 100 years:-

INSERT INTO diff_yearstbl(SKU, Product_Name, Model, Make, Year) 
SELECT   SKU,
        Product_Name,
        Model,
        Make,
        GROUP_CONCAT(Year_From + tens.acnt * 10 + units.acnt) AS ayear
FROM csvtbl
CROSS JOIN (SELECT 0 AS acnt UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
CROSS JOIN (SELECT 0 AS acnt UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
WHERE (Year_From + tens.acnt * 10 + units.acnt AS ayear) <= Year_TO
GROUP BY SKU,
        Product_Name,
        Model,
        Make

answered Feb 19, 2016 at 15:50

Kickstart

21.5k2 gold badges26 silver badges33 bronze badges

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

Comments

roullie · Accepted Answer · 2016-02-19 06:04:44Z

1

You should loop from $QryRow['Year_From'] to $QryRow['Year_To'] and collect them all before inserting them.

$years= array();
for ($x = $QryRow['Year_From']; $x <= $QryRow['Year_To']; $x++) 
{           
    $years[] = $x;    
}

$query_insert = "INSERT INTO diff_yearstbl(SKU,Product_Name,Model,Make,Year) VALUES('".$SKU."','".$Product_Name."','".$Model."','".$Make."','".implode(',',$years)."')";
$s_insert = mysql_query($query_insert, $connect );

mysql_* is deprecated please use mysqli_* or PDO

answered Feb 19, 2016 at 6:04

roullie

2,82019 silver badges26 bronze badges

5 Comments

Nishant Patel Over a year ago

I got the solutions using the above code. Thank You!

roullie Over a year ago

@NishantPatel Glad i helped. Have a nice day. please also take note mysql_* is deprecated

Nishant Patel Over a year ago

If i want to insert the '".implode(',',$Make)."') and '".implode(',',$Model)."') using the SKU. Then is it possible to do this using above code. Now i want "(1, 'C2AZ-3B584-AR', 'Power Steering Pump Seal Kit (Eaton Pump)', 'Galaxie,Thunderbird', 'Ford, Fordtest' '1960,1961,1962,1963,1964,1965');" using the SKU field. Is it possible?

roullie Over a year ago

@NishantPatel i think it's possible. can you update your question? i don't quite understand your next question/problem.

Nishant Patel Over a year ago

I have update my question. Please see and give me reply.. Thanks

Nehal · Accepted Answer · 2016-02-19 06:16:07Z

1

Move out your insert code out of for loop

while($QryRow = mysql_fetch_assoc($result)) 
    {
        $diff_years = $QryRow['diff_years'];
        $Year_From = $QryRow['Year_From'];
        $SKU = $QryRow['SKU'];
        $Product_Name = $QryRow['Product_Name'];
        $Model = $QryRow['Model'];
        $Make = $QryRow['Make'];
        $YearCountList = array();
        for ($x = 0; $x <= $Year_From; $x++) {
            $YearCountList[] = $Year_From + $x;        
        } 

        $years=implode(',', $YearCountList); // insert comma separated values

        $query_insert = "INSERT INTO diff_yearstbl(SKU,Product_Name,Model,Make,Year) VALUES('".$SKU."','".$Product_Name."','".$Model."','".$Make."','".$years."')";
        $s_insert = mysql_query($query_insert, $connect );    
    }

Check This

edited Feb 19, 2016 at 6:16

Nehal

1,5134 gold badges17 silver badges30 bronze badges

answered Feb 19, 2016 at 6:00

Pallavi

6741 gold badge10 silver badges26 bronze badges

1 Comment

Nishant Patel Over a year ago

@Nehal If i want to insert the '".implode(',',$Make)."') and '".implode(',',$Model)."') using the SKU. Then is it possible to do this using above code. Now i want insert data like "(1, 'C2AZ-3B584-AR', 'Power Steering Pump Seal Kit (Eaton Pump)', 'Galaxie,Thunderbird', 'Ford, Fordtest' '1960,1961,1962,1963,1964,1965');" using the SKU field. Is it possible?

Kartik Dholariya · Accepted Answer · 2016-02-22 13:20:55Z

1

Move out your insert code out of for loop

answered Feb 22, 2016 at 13:20

Kartik Dholariya

511 silver badge5 bronze badges

Collectives™ on Stack Overflow

Need help on insert comma separated data in mysql database using PHP

4 Answers 4

Comments

5 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

5 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related