CODEWITHSUNDEEP
phpmysqlinteger
Paolo
Paolo

Reputation: 361

Insert numbers into a single field MySQL

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

Answers (3)

cbcram
cbcram

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

ModulusJoe
ModulusJoe

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

Robert
Robert

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?

  1. Chance db structure and depend on relations.
  2. (bad idea) change the type to varchar for example and then insert will be done(DONT DO IT)
  3. Do multiple updates(it really depends on db structure)

Upvotes: 0

Related Questions