MySQL Insert - Delimited String contents as separate records

Hello,

I have a string consisting of product IDs seprated by semi colons (;). I would like to insert the contents of this string as separate records in a MySQL database table.

I was wondering, instead of using ASP to loop through the string (one product ID at a time), and performing a separate Insert for each product until all products are added, is there a command I can integrate within the MySQL Insert code to do this instead?

I hope someone can help!

Thanks,

Mark.
funassetAsked:
Who is Participating?
 
Aleksandar BradarićSoftware DeveloperCommented:
Assuming you create these two tables (`downloads` and `download_products`), you would first enter the data for the `downloads` table with:
---
INSERT INTO funasset.downloads (ID, FullName, Email, Date)  VALUES (varID, varFullName, varEmail, varDate);
---

Inserting into the `download_products` table would be done with:
---
INSERT INTO funasset.download_products(DownloadID, ProductID) VALUES (varID, 34), (varID, 55), (varID, 23), (varID, 22);
---

Now, the only problem is to convert your input string ("34;55;23;22") into the insert string. You do it from ASP by replacing the ";" with "), (varID, ":
---
34;55;23;22
---

transforms to:
---
34), (varID, 55), (varID, 23), (varID, 22
---.

The final step is to just add the missing front/back parts of the string:
---
INSERT INTO funasset.download_products(DownloadID, ProductID) VALUES (varID, 34), (varID, 55), (varID, 23), (varID, 22)
---

This will insert the 4 (or what ever the number) products into the table and link it to the other table.
0
 
Aleksandar BradarićSoftware DeveloperCommented:
You have LOCATE and SUBSTRING commands is MySQL (see: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html). These two should allow you to do the INSERTs using MySQL only.
0
 
funassetAuthor Commented:
Hi Leannonn,

As usual, thanks for a quick response. I have taken a look at the LOCATE and SUBSTRING commands and cannot undertsand how I could use them to resolve my problem. i.e. how would they be used in conjuction with the Insert?

Could you please provide an example please?

Thanks.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Aleksandar BradarićSoftware DeveloperCommented:
OK, here's an example:
---
mysql> SELECT * FROM mySource;
+---------------+
| productIDs    |
+---------------+
| 1;11;111;1111 |
| 2;22;222;2222 |
| 3333;333;33;3 |
| 4             |
+---------------+
4 rows in set (0.01 sec)
---

In order to get the first items from each row, use this:
---
mysql> INSERT INTO
    ->   myTarget
    -> SELECT
    ->   IF(LOCATE(';', productIDs) > 0, SUBSTRING(productIDs, 1, LOCATE(';', productIDs) - 1), productIDs)
    -> FROM
    ->   mySource
    -> ;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM myTarget;
+-----------+
| productID |
+-----------+
| 1         |
| 2         |
| 3333      |
| 4         |
+-----------+
4 rows in set (0.00 sec)
---

For second items, use this:
---
mysql> INSERT INTO
    ->   myTarget
    -> SELECT
    ->   SUBSTRING(productIDs, LOCATE(';', productIDs) + 1, LOCATE(';', productIDs, LOCATE(';', productIDs) + 1) - LOCATE(';', productIDs) - 1)
    -> FROM
    ->   mySource;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM myTarget;
+-----------+
| productID |
+-----------+
| 1         |
| 2         |
| 3333      |
| 4         |
| 11        |
| 22        |
| 333       |
|           |
+-----------+
8 rows in set (0.00 sec)
---

You'll have to continue this until the 4th element in this case - not sure how many IDs you have in one row. You will maybe have to delete the empty rows if you have different number of IDs, as I do in this case.
0
 
funassetAuthor Commented:
Leannonn,

Thanks again for the response and the example. However, the string I am talking about that contains the product IDs (eg. 34;55;23;22) is a variable used on my web page and is not contained in another MySQL table.

There is just one string that I need to extract the product IDs from and Insert into seperate MySQL records.
0
 
Aleksandar BradarićSoftware DeveloperCommented:
Then you'll much better if you use ASP to simply transform the string into:
---
INSERT INTO yourTable(productID) VALUES (34), (55), (23), (22);
---
0
 
funassetAuthor Commented:
Mmmmm. Maybe I need to expand my question a bit more...

I have a web page where you select products you wish to download (by clicking a checkbox). Each product that is selected has its product ID added to a delimited string variable (called varProdIDs).

The website visitor is then passed to a page that asks for their name and email address. Once entered they are passed to another page where the actual download links for the products selected are displayed. On this page, I would like to insert what products the user selected for download together with their name and email address (that they entered on the previous page) in my MySQL database. Each database record needs to have Name, Email and a single product ID (and also the date). So, if the user selected 4 products for download, 4 records in the DB would be created (all of which contained their name, email, product ID and the date).

This is how far I have got with my MySQL Insert...

INSERT INTO funasset.downloads (FullName, Email, ProductID, Date)  VALUES (varFullName, varEmail, ?, varDate)

As you can see from the above (notice the "?"), it is the ProductIDs I do not know how to Insert from the list of product IDs contained within my varProdIDs variable string. Again, each product ID would need to be a separate record in my DB (and also contain the same name and email).

Sorry I did not explain things very well to start with.

Hope you can help.

Thanks.

Mark.
0
 
Aleksandar BradarićSoftware DeveloperCommented:
The simplest way is just to repeat the data:
---
INSERT INTO
  funasset.downloads (FullName, Email, ProductID, Date)  
VALUES
  (varFullName, varEmail, 34, varDate),  
  (varFullName, varEmail, 55, varDate),  
  (varFullName, varEmail, 23, varDate),  
  (varFullName, varEmail, 22, varDate)
---

Now, if I were designing that DB, I would store this data into two tables - one for `downloads` with `ID`, `FullName`, `Email` and `Date`; and the second one for `download_products` with `download_id` and `product_id`.
0
 
funassetAuthor Commented:
Leannonn,

Thanks again. I see what you mean about storing in two different tables. I think I will change my DB so this happens. :)

I am still confused however! I do not understand how I can insert the product IDs from my varProdID variable string, as separate records when the product IDs will be different depending upon what the user has selected for download (i.e. not always 34, 55, 23, 22). They could also select any number of products (not just 4 as in your example).

Sorry for being rather dumb when it comes to MySQL. I use it so little, it is always a struggle to do things I am not used to doing or have never done before. I also find the MySQL manual very unhelpful and find examples a better way of getting my head around the problem.

Any chance you could enlighten me on adding the varied quantity) product IDs as seprate records please?

Thanks

Mark.
0
 
funassetAuthor Commented:
Ok. Got you, well up until the last INSERT you show in your example...

INSERT INTO funasset.download_products(DownloadID, ProductID) VALUES (varID, 34), (varID, 55), (varID, 23), (varID, 22)

I understand the replacement part I need to do in ASP (and also tagging missing stuff on each end), but how do I reference the new varProdID contents in the INSERT command? Can I do this?...

INSERT INTO funasset.download_products(DownloadID, ProductID) VALUES varProdID

Where varProdID content would now (after the ASP replacement and addition) be...

(varID, 34), (varID, 55), (varID, 23), (varID, 22)... or whatever product IDs the user had selected.
0
 
Aleksandar BradarićSoftware DeveloperCommented:
Yes, I think you can. However, if you have problems with it, just join the two strings and use the joined string as your new INSERT statement.
0
 
funassetAuthor Commented:
Thanks Leannonn.

I will try it now.

Regards,

Mark.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.