0

So one of my columns in my table has values like this

a:2:{i:0;s:6:"Subbed";i:1;s:6:"Dubbed";}

Right now it's stored as longtext and I've heard that varchar is faster than longtext if you're going to query it often.

So I have two questions, can values like this be stored as varchar and how do I retrieve these values as an array in php like ["Subbed, "Dubbed"].

How do I insert values like this if I have something like ["Subbed", "Dubbed"] and I want a:2:{i:0;s:6:"Subbed";i:1;s:6:"Dubbed";} .

Finally, is this something like a stringified array?

user1952811
  • 2,398
  • 6
  • 29
  • 49
  • 1
    you should look at db normalisation, this is not the ideal storage strategy –  Jul 01 '14 at 20:28
  • 2
    Is there a particular reason you aren't doing this in a [normalized](http://en.wikipedia.org/wiki/Database_normalization) way? You are violating [1NF](http://en.wikipedia.org/wiki/First_normal_form) with this structure which typically is not good. – Ethan Jul 01 '14 at 20:28
  • How does your title match your question? – GolezTrol Jul 01 '14 at 20:28
  • 1
    Your first problem is that your data is not held correctly. You should not have multiple values saved in a single column. They should each have their own. As far as the difference between LONGTEXT and VARCHAR, yeah, LONGTEXT is a really bad idea. – durbnpoisn Jul 01 '14 at 20:29
  • Also, in this stage, I wouldn't worry about the field type. I don't think the field type shouldn't matter *that* much. Also note that varchar has serious limits. The total record size in MySQL can be 65535 bytes at most, which is about 21000 characters if you have a UTF-8 VARCHAR field. This is total per record, so all fields combined. LONGTEXT is stored separately and doesn't have this limit. – GolezTrol Jul 01 '14 at 20:29
  • Guys this is how wordpress stores all its `meta_values`, as `longtext`. But I have thousands of entries stored like this. So sure i'll go ahead and normalize it. But another question is for every checkbox I have I'll have to create a `pivot table` and a separate table for the checkbox options. Seems like a hassle. – user1952811 Jul 01 '14 at 20:32
  • well you could of saved us both some time by tagging it with wordpress :( –  Jul 01 '14 at 20:35
  • @dagon I am exporting this out of wordpress and will be creating a new structure with post types have their own tables and no post meta table. What I am wondering is if it's actually a good idea to go about the whole normalization for each and every checkbox I have for a post type. – user1952811 Jul 01 '14 at 20:38
  • sigh, can i have my 5 minute back please –  Jul 01 '14 at 20:42
  • @Dagon I don't see why wordpress changes anything. My questions are not affected, they have no correlation with the fact that the DB is on WordPress. I am moving away from WordPress and I am exporting all of the data and remodeling it. – user1952811 Jul 01 '14 at 20:44

1 Answers1

1

  1. See this question: MySQL: Large VARCHAR vs. TEXT?

  2. It's the format used by serialization functions in php! You get to learn stuff every day. Check the PHP documentation.

Community
  • 1
  • 1
didierc
  • 14,572
  • 3
  • 32
  • 52
  • 1
    write a parser !?!? lol `serialize` was used so um use `unserialize` http://www.php.net/manual/en/function.unserialize.php –  Jul 01 '14 at 20:54
  • I wasn't familiar with the serialization function of PHP, thank you for the link, and glad I could make you laugh... – didierc Jul 01 '14 at 21:02
  • 7k rep and you have never encountered one of the basic php functions come on dude :-) –  Jul 01 '14 at 21:04
  • I never had to use them. And as you may see, my rep isn't solely based on php. Hopefully I'll get a badge for it some day though, I'm getting there with everyone's help. – didierc Jul 01 '14 at 21:06
  • Thanks! I must admit though that this looked slightly familiar :) – didierc Jul 01 '14 at 21:08
  • So serialize will return `string` in which case I should be able to use varchar. Main question is if it's worth normalizing. – user1952811 Jul 01 '14 at 21:11
  • I don't understand that question: it seemed that you had your db set that way, so how would you keep it working with your cms (wordpress?) If you change the db structure? – didierc Jul 01 '14 at 21:16
  • "Main question is if it's worth normalizing. " - almost always YES –  Jul 01 '14 at 21:17
  • 1
    @didierc I am not going to be using wordpress any more. I am taking the data and remodeling it. – user1952811 Jul 01 '14 at 21:30
  • Might need to filter data by choices. – user1952811 Jul 01 '14 at 21:32
  • So you want to export that data, and feed it to another CMS? – didierc Jul 01 '14 at 21:37
  • Removed all the comment material. I think you should extract that data and save it as csv for instance. Then try to design a db which works with what you have. If the format is fixed, you certainly can model it into a normalized db. – didierc Jul 01 '14 at 21:52