0

I got one general and probably easy question. When saving an array of int numbers into a MySQL-database (numbers are seperated by a comma), what would be the correct datatype for the database to choose? When choosing INT as type, I guess I may not use commas, may I? So I used "text" as datatype since these arrays of numbers can get really long and I didnt want to set a limit. But is this the right way to go or is it bad to save only numbers with comma in a "text" field in my database?

Thanks for your help! phpheini

Chris
  • 6,093
  • 11
  • 42
  • 55

5 Answers5

2

The best datatype would be another table.

It is indeed very "bad to save only numbers with comma in a "text" field in my database"

This would be breaking First Normal Form

Doing this will cause you increase data contention on the field when modifying values.

For example lets suppose two users want to add a new value to the list of numbers associated with a record. When the table is in 1NF normally both users would get to insert a record. When not in 1NF its easy for one user to lose the addition because you're writing back several values to a single attribute.

It also makes it very difficult to do simple kinds of queries like (which records have a 3 in the list). Yes it can be done but it won't be able to use any indexes, because databases don't index what's inside a field (except for special cases like SQL Server's XML type)

Conrad Frix
  • 51,984
  • 12
  • 96
  • 155
  • Well the problem is, imagine hundreds of users adding eachone 1000 new int numbers. Wouldnt this in the end be slower to search in this huge table with hundreds of thousands of rows for all the rows which are from a user with the id 1 than to search in a database where each userid just has one entry? – Chris Dec 02 '11 at 18:28
  • No. It's slower as long as you have a index on the FK. Because what are you going to do with the comma list? You'll either retrieve it and then split it out (string parsing is expensive) or even worse you'll want to put a `WHERE` clause on it which as previously mentioned will be slow because it can't be SARGAble. But don't take my word for it try it your self. – Conrad Frix Dec 02 '11 at 18:46
  • Ok, because now I am also wondering how to insert the numbers into the database. Say a user wants to insert 1000 numbers, then the foreach loop would 1000 times call up the mysql INSERT algorithm while the other method would insert them all at once. – Chris Dec 02 '11 at 18:59
  • @phpheini take a look at [insert multiple rows via a php array into mysql](http://stackoverflow.com/questions/779986/insert-multiple-rows-via-a-php-array-into-mysql) – Conrad Frix Dec 02 '11 at 19:09
  • You got any idea how to do it with prepared statements? – Chris Dec 02 '11 at 19:54
  • No idea but you can always ask another question – Conrad Frix Dec 02 '11 at 19:55
1

The best thing to do is to NOT insert a comma separated list into a single column.

Sooner or later this will come back and bite you.

I suggest you read up on normalization.

Raj More
  • 47,048
  • 33
  • 131
  • 198
1

it would be batter to serialize the array and than store it to database and while accessing unrealize that, you can use as varchar/longtext as datatype depends how much data your array has. you will see the difference in below example. 

$var  = array('1'=>'a','2'=>'b');
$var2  = serialize($var);
echo $var2;
print_r(unserialize($var2));
Punit
  • 1,110
  • 1
  • 8
  • 14
0

You need to save them in separate rows. Saving coma-separated data in database breaks first normal form.

user996142
  • 2,753
  • 3
  • 29
  • 48
0

Text would be my suggestion, but it comes with the caveat that it's not easily searchable or indexable by a particular value in that list of values.

Without knowing what those values are and what they are for, I can't suggest anything else, but you might want to consider database normalization. It's far better in the long run.

Benjam
  • 5,285
  • 3
  • 26
  • 36