Reputation: 361
I'm trying to insert diferent numebers (like "10 11 12") into a MySQL field (the numbers come from PHP), but when I do the query, the field only gets the first number.
For example, I do:
UPDATE profile SET subcategory = '10 11 12' WHERE userId = 1 LIMIT 1
And the DB just registers '10'.
Any way to do it?
Upvotes: 0
Views: 209
Reputation: 165
This happen because you're updating a number, probably an integer, so mysql do the job just for the first number.
If you do this:
UPDATE profile SET subcategory = 10 WHERE userId = 1 LIMIT 1
UPDATE profile SET subcategory = 11 WHERE userId = 1 LIMIT 1
UPDATE profile SET subcategory = 12 WHERE userId = 1 LIMIT 1
You'll just update the category with the third value (12).
I suggest you a user belonging to multiple subcategories so you'll have to create another table. Eg: a table called subcategories with at least two fields: userId and subcategoryId. And then you could do something like this:
DELETE FROM subcategories WHERE userId=1
INSERT INTO subcategories (userId, subcategory) VALUES (1,10)
INSERT INTO subcategories (userId, subcategory) VALUES (1,11)
INSERT INTO subcategories (userId, subcategory) VALUES (1,12)
The first line (delete) is used just to update the user's subcategories, first you delete all older subcategories for the user and then you insert the new ones. In PHP you could use a foreach() to automatize the insertion of multiple values.
You could also have a non unique userId in the table profiles with an entry per user subcategory but it will complicate things.
I hope it could help you
Upvotes: 3
Reputation: 1456
This very much depends on the problem you are trying to solve.
If you are just trying to store a small number of numbers then using the php join and split functions to take a list of numbers and convert to and from a string and store that in a VARCHAR.
A better way to solve the problem would be to understand the layout of your data. Try having a table that links profiles to subcategories. Two columns, one for the profile ID and one for the Subcategory ID. You might find having a search for database normalisation informative.
This presentation looks relatively informative: http://www.sqa.org.uk/e-learning/MDBS01CD/page_26.htm
Upvotes: 0
Reputation: 20286
From your problem I guess that the type of your subcategory is integer. What happens when you put string? It is converted. The converter convert it to first proper integer which is 10 space after 10 is considered as string.
What can solve your problem?
Upvotes: 0