experience40 Posted September 6, 2023 Share Posted September 6, 2023 (edited) Hi I have a Map DB Table which maps CSV column numbers and i'm trying to import the CSV data into the stock table based on the CSVColumn number. However when i insert the data into the stock table, it imports the different CSV column data onto seperate rows rather than the same row for the same product The column names in the stock table are named after the attribute_code on the Map table Wondering the best way to tackle this, possibly merge the first SELECT query into a single row result? Map Table | feed_id | user_id|attribute_code | csvcolumn | |1|1|1|3| |1|1|10|1| |1|1|6|7| Stock Table |feed_id|user_id|0|1|2|3|4|5|6|7|8|9|10| I want to import the data as such: Stock Table |feed_id|user_id|0|1|2|3|4|5|6|7|8|9|10| |1|1||CSVData||CSVData||||CSVData|||| However it imports as seperate rows Stock Table |feed_id|user_id|0|1|2|3|4|5|6|7|8|9|10| |1|1||||CSVData|||||||| |1|1||CSVData|||||||||| |1|1||||||||CSVData|||| Abrievated summary of the code i'm trying below: // RETRIEVE MAP COLUMN NUMBERS $feed_db = $conn->prepare("SELECT m.attribute_id, m.csvcolumn, f.filename, f.header_row FROM map m INNER JOIN ftp f WHERE f.id = m.feed_id AND user_id = ? AND feed_id = ?"); $feed_db->bind_param("ii", $userid, $feed_id); $feed_db->execute(); $feedresult=$feed_db->get_result(); while($dbrow=$feedresult->fetch_assoc()) { $A_ID = $dbrow['attribute_id']; $CSV_COL = $dbrow['csvcolumn']; // OPEN CSV $file = fopen($local_file_path, "r"); fgetcsv($file); while (($row = fgetcsv($file, 0, $delimiter)) !== FALSE) { // INSERT CSV DATA $insertstock = $conn->prepare("INSERT INTO stock (userid, feed_id, `$A_ID`) VALUES (?, ?, ?)"); $insertstock->bind_param("iis", $userid, $feed_id, $row[$CSV_COL]); $insertstock->execute(); } } Edited September 6, 2023 by experience40 Quote Link to comment Share on other sites More sharing options...
ginerjm Posted September 6, 2023 Share Posted September 6, 2023 Since I don't understand what you are trying to show us (and ask us about), I have to ask this. Are you really trying to insert an array into a table row? Or are you trying to insert the contents of an array into individual fields of your table that exist on a single record? Quote Link to comment Share on other sites More sharing options...
Barand Posted September 6, 2023 Share Posted September 6, 2023 Your insert query needs to look like this INSERT INTO stock (userid, feed_id, c0, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) Your values to be inserted would be NULL (if not required) or the desired csv value. Also, do not prepare and bind i every iteration of the loop. Do those before the loop. Inside the loop you should just update the variables and execute. Quote Link to comment Share on other sites More sharing options...
experience40 Posted September 6, 2023 Author Share Posted September 6, 2023 (edited) thanks for the replies Basically i have price stock CSV Excel files (all different) which contain columns such as price, part_code, manufacturer, name, description etc.. and i want to import the data into the correct database column in the stock table hence a map table which maps the CSV column number to the appropriate database stock table column Map Table | feed_id | user_id | attribute_code | csvcolumn | When the CSV data is imported into the DB, if price is mapped as CSVcolumn '2' then column '2' data will import into stock table column name '2' etc Each CSV is different and each user has different attributes, where one user may have weight, colour etc.. another user may have size, thickness so the attributes on the insert command varie and cannot be hard code. One user may have attributes 1-5, another user attributes 1-50. The idea is that the user can add as many attributes as they want depending on their needs The code below half works, however its creating seperate rows $insertstock = $conn->prepare("INSERT INTO stock (userid, feed_id, `$A_ID`) VALUES (?, ?, ?)"); $insertstock->bind_param("iis", $userid, $feed_id, $row[$CSV_COL]); The 2 variables in the map table $A_ID = $dbrow['attribute_id']; // THIS IS THE ATTRIBUTE ID $CSV_COL = $dbrow['csvcolumn']; // THIS IS THE CSV COLUMN NUMBER Edited September 6, 2023 by experience40 Quote Link to comment Share on other sites More sharing options...
experience40 Posted September 6, 2023 Author Share Posted September 6, 2023 (edited) 18 minutes ago, Barand said: INSERT INTO stock (userid, feed_id, c0, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) Unfortunately this wont work as i dont know how many columns there are as it varies for each user, the column is a variable i.e. user 1 may have 3 attributes (price, quantity, name) user 2 may have 5 attributes (name, colour, size, weight, part_code)... Edited September 6, 2023 by experience40 Quote Link to comment Share on other sites More sharing options...
experience40 Posted September 6, 2023 Author Share Posted September 6, 2023 (edited) this is how the original code imports into the stock table with seperate rows for the same item This is how i want it to import on the same row Edited September 6, 2023 by experience40 Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted September 6, 2023 Share Posted September 6, 2023 to normalize this data, you need an attribute table, with - id (autoincrement primary index) and name columns. as new attributes are defined, they would be inserted into this table. this table establishes attribute ids. the map table would map the csv column number to these attribute ids. you would JOIN with this table when displaying information to get the meaningful names for each attribute id. the stock table would not be laid out as a spreadsheet. you would instead insert only the unique one-time information for each item into the stock table. this would establish a stock id for each item for each user/feed. you would then have a stock attribute table with - id (autoincrement primary index), stock id, attribute id, and value columns. you would insert a separate row into the the stock attribute table for each csv column value for each stock id. when you are inserting the data, you would query the map table to get the set of rows for the user/feed and fetch these into an array. when you read each row of data from the csv file, you would then loop over this array of map data, use the csv column number to get the data value from the row of csv data, then use this data value and the attribute id for executing the stock attribute insert query. Quote Link to comment Share on other sites More sharing options...
Barand Posted September 6, 2023 Share Posted September 6, 2023 Don't worry about my suggestion not working - you shouldn't ever have a table designed like that. It should look like this, one value per row... userid | feed_id | attr_id | attr_value What does the CSV data look like? Quote Link to comment Share on other sites More sharing options...
experience40 Posted September 6, 2023 Author Share Posted September 6, 2023 57 minutes ago, mac_gyver said: to normalize this data, you need an attribute table, with - id (autoincrement primary index) and name columns. as new attributes are defined, they would be inserted into this table. this table establishes attribute ids. the map table would map the csv column number to these attribute ids. you would JOIN with this table when displaying information to get the meaningful names for each attribute id. thanks, i have an attribute table setup as below: 57 minutes ago, mac_gyver said: the stock table would not be laid out as a spreadsheet. you would instead insert only the unique one-time information for each item into the stock table. this would establish a stock id for each item for each user/feed. you would then have a stock attribute table with - id (autoincrement primary index), stock id, attribute id, and value columns. you would insert a separate row into the the stock attribute table for each csv column value for each stock id. Slightly struggling to understand this, are you able to give an example? Stock Table id user_id feed_id 1 1 1 Stock Attribute Table id stock_id attribute_id data 1 1 4 99.99 Quote Link to comment Share on other sites More sharing options...
experience40 Posted September 6, 2023 Author Share Posted September 6, 2023 1 hour ago, Barand said: What does the CSV data look like? Every CSV differs, but the below gives a good idea of the data Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.