Storing Strings In Database Mimicing an Array

Question

What is the best way to store a ton of links into 1 db column but for only 1 id and making it like an array?, I cant quite get this to work as I need them to be categorized in a way.

DB:

=====================

ID | links

1 | -Links Here-

2 | -Completely Seperate Unrelated Links-

=====================

So, I need the 'links' to be seperated and categorized. I tried doing explode() which puts it into an array but you just cant organise it that way, I tried making seperators different e.g, ~1~, ~2~, ~3~ but I just couldnt get it to categorize properly. Do note that 100's of links will be going into this one column which probably sounds like a bad thing to do but id rather this way then 50-100 ish columns for simply storing links.

Tried Code:

if($rdl['links'] != '') { $s1dl = explode("~S1~", $links); }

substr(strstr($s3dl, 'http'), strlen('http'));

echo $s3dl[0];
echo $s3dl[1];
echo $s3dl[2];

UPDATE: Basically I need a way to have a user inputted field, You put the link in it, then send it to the DB in an array or something categorizable but an array would be best.

So I made a Season and Link field. I have got this:

$linkadd = $db2->prepare("INSERT INTO dl (link) VALUES (:link) WHERE imdbid LIKE :id");

Do note, If I use encode or Serialize how am I meant to "Append" the user entered link into that serialized array that is ready to be unserialized?

Where do I go from here?

Would something like this work?:

$currentlinks = unserialize($rdl['links']);
$currentlinks[] = $_POST['link'];
$newlinks = serialize($rdl['links']);

$linkadd->execute(array(':link' => $newlinks));

Answer 1

If you need to have them all into a single field, you could put them all into an array, encode it, and store the resulting string in the database. You could choose e.g. either of these:

$valueForDatabase = json_encode($my_links);
// or
$valueForDatabase = serialize($my_links);

Then, when you get the field from the database, you use the corresponding decoding function:

$my_links = json_decode($valueFromDatabase);
// or
$my_links = unserialize($my_links);

Answer 2

What exactly is the reason for putting it in one field? I ask because I myself have done it some times for pure lazyness to avoid a different table, and almost always regretted it a bit later (even a year or two).

The answer very much depends on what exactly you want to get. Normally just putting into a table linklist id, main_id, link and then

select group_concat(link) as thelinks from linklist where main_id = 3

or even

select main_id, group_concat(link) as thelinks 
from linklist 
where main_id in (4,5,6) 
group by main_id

would be the simplest and most clear solution.

If this is not the case for your request you should make clear why this would not solve your special problem.

UPDATE1:

You still should use normalization, that means creating another table. After you updated question it sounds like you have a link_type (bitly, adfly, others).

So you have your table dl with id and some other fields, and you make a table dl_links which consists of

 create table dl_links (
   id int(11) not null primary key auto_increment,
   dl_id int(11) not null,
   link_type varchar(255), # bitly, adfly, others
   link varchar(2000)
 }

When a new link comes in, you don't store it in table dl, but in dl_links:

insert into dl_links set dl_id= :id, link_type = :type, link = :link

or if you like

insert into dl_links (dl_id, link_type, link) values (:id, :type, :link);

Then you can read the links of a dl in the way

select dl.id, dl_link.link_type, group_concat(dl_link.link) as linklist
  from dl 
  inner join dl_links on dl_links.dl_id = dl.id
  group by dl.id, dl_link.link_type
;