MySQL insert Problem

[nlacayo13]

Hello All,

I have read the guidelines and will try to follow them. Hopefully someone can help me with my question.,

 

my problem is that I am trying to get a textarea on a form to input each line in the text area to a different row in MySQL database. (THIS IS WORKING)

However my MySQL table has several columns. Text area will be used to submit a value to column "keysnumber". Each line in the text area will be a different row in MySQL DB. However,  Also on the same form as the text area, there will be hidden fields which have the keystatus, keypurchasedate, keyaddeddate. These need to be inserted as well into each new row.

 

Table Structure:

CREATE TABLE IF NOT EXISTS `keys_inventory` (
  `keysid` int(11) NOT NULL AUTO_INCREMENT,
  `keysnumber` tinytext COLLATE utf8_unicode_ci NOT NULL,
  `keyssubcategory` int(11) NOT NULL,
  `keystatus` tinytext COLLATE utf8_unicode_ci,
  `keypurchasedbystudentid` int(11) NOT NULL,
  `keypurchasedate` tinytext COLLATE utf8_unicode_ci,
  `paymentransactionid` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `dateassigned` tinytext COLLATE utf8_unicode_ci,
  `paymentstatus` tinytext COLLATE utf8_unicode_ci,
  `keyaddeddate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `addedby` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`keysid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=147 ;

 

MYSQL STATEMENT:

INSERT INTO keys_inventory(keysnumber) VALUES($line)

 

$LINE VALUE:

$values = $_POST['area'];
$array = split("\r\n", $values);
foreach($array as $line){

 

MySQL Version 5.1.56

[fenway]

Huh?

[ManiacDan]

If you have all the other variables from the form, then include them in your statement.  Get $keystatus and all the others into their variables, then include them in your loop.

 

Also, repeating the data like this is a violation of normal forms, take a look at that link for database design tips.

 

-Dan

[nlacayo13]

Thanks for your reply. I will definately read the normal forms description.

What is the recommeded way of doing this? Basically, the website will sell unique "keys" for the purpose of taking tests.

The admin needs to be able to bulk import 10-100 keys at a time, assign a category to them and stamp them with other information upon bulk upload.

[ManiacDan]

Make one table with all the "other" fields from this one, and another table that stores the Key and the ID of the first table.

 

When the page is submitted:

- take all the "other" data and make an entry in the metadata table

- retrieve the ID you just inserted

- insert all the keys in a loop (or in one multi-line insert statement) specifying the ID of the table to link to.