PHP MYSQL Insert Array on Same Table Row

[experience40]

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

[ginerjm]

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?

[Barand]

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.

[experience40]

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

[experience40]

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

[experience40]

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

[mac_gyver]

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.

[Barand]

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?

[experience40]

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

[experience40]

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