Jump to content

PHP MYSQL Insert Array on Same Table Row


experience40

Recommended Posts

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 by experience40
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by experience40
Link to comment
Share on other sites

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 by experience40
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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:
1.png

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.