?
Solved

MySQL Insert - Delimited String contents as separate records

Posted on 2007-10-02
12
Medium Priority
?
252 Views
Last Modified: 2010-05-18
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.
0
Comment
Question by:funasset
  • 6
  • 6
12 Comments
 
LVL 17

Expert Comment

by:Aleksandar Bradarić
ID: 19997419
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
 

Author Comment

by:funasset
ID: 19997432
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
 
LVL 17

Expert Comment

by:Aleksandar Bradarić
ID: 19997759
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:funasset
ID: 19997843
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
 
LVL 17

Expert Comment

by:Aleksandar Bradarić
ID: 19997879
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
 

Author Comment

by:funasset
ID: 19998005
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
 
LVL 17

Expert Comment

by:Aleksandar Bradarić
ID: 19998076
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
 

Author Comment

by:funasset
ID: 19998161
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
 
LVL 17

Accepted Solution

by:
Aleksandar Bradarić earned 2000 total points
ID: 19998293
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
 

Author Comment

by:funasset
ID: 19998437
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
 
LVL 17

Expert Comment

by:Aleksandar Bradarić
ID: 19998523
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
 

Author Comment

by:funasset
ID: 19998554
Thanks Leannonn.

I will try it now.

Regards,

Mark.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question