Need help inserting comma data into mysql database using PHP

Question

Sample data that is already 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 the code below and insertable years with a comma (,) divided in the 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'); 

Data years work well, but I want to insert the Make and Model data as the same as Years, but this time just insert the data with a comma (,), divided into one line, as shown below, using the SKU field. Therefore, you need to combine the two above records into one line, as shown below.

 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 is the code I made:

  $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.

Answer 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 , use mysqli_ * or PDO

Answer 2

Extract the insert code from the 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 it out

Answer 3

In one SQL statement, cope 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 

Answer 4

Extract the insert code from the for loop