1

I need to store a comma delimited string (array of urls) inside mysql table.

for example:

"https://google.com,https://yahoo.com,https://something.com"

fetching this from the table inside php I'm using explode function to get an array.

$arr = explode(',', $string);

Problem is because some url can contain comma character inside itself and this can produce confusion.

In fact, url can contain any character, escaped or not - it will produce error in explode function.

Any help?

2 Answers2

1

You can use urlencode()

$stupidCommaUrl = 'http://why.com/is,there/a/comma';
echo urlencode($stupidCommaUrl);

// outputs http%3A%2F%2Fwhy.com%2Fis%2Cthere%2Fa%2Fcomma

You can use urldecode() to convert it back.

Your DB design sounds flawed. As Nigel said in the comments above, look into database normalisation.

delboy1978uk
  • 12,118
  • 2
  • 21
  • 39
0

  1. Try to use a set of characters that you will never find in a url like $&$ to separate your values, that will act as a comma, so when you retreive the values you will do this: $arr = explode('$&$', $string);

  2. You can also build an array and use json_encode and save the string, you will get rid of the explode function, example:

    $arrayOfUrls = [your array];
$urlsInJson = json_encode($arrayOfUrls);

    Then store $urlsInJson in database

    To retrieve your data you will use

    $arrayOfUrls = json_decode($yourQueryResult['URLS'])

Hope it helps.

Alberto Vidales
  • 54
  • 6
  • 6
    Still a bad idea.. separated values destroy the power of a SQL database... normalisation of the data is the best way to go. – Raymond Nijland Jul 17 '18 at 10:46
  • 1
    I updated some other approach using JSON, the first one is just to answer an alike solution like the one he´s trying, but I do think is a bad idea. – Alberto Vidales Jul 17 '18 at 10:50
  • this smells like a HACK – delboy1978uk Jul 17 '18 at 10:50
  • 1
    The answer is correct and specific to the question. The question was not "how to improve db design". Keep that in mind when downvoting.. – Zim84 Jul 17 '18 at 10:54
  • 1
    the downvotes system is also meant to indicate how usefull a answer is.. @Zim84 – Raymond Nijland Jul 17 '18 at 10:59
  • Then let the person who uses it decide that, it can be useful, not right but useful – Alberto Vidales Jul 17 '18 at 11:01
  • 1
    Well stackoverflow is a community if the communtity does not mark the "bad" answers more problems come back and give advice to suggest better ways to do it with comments.. The next questions with this bad table design could be `how to join with csv` or `how to count with csv`. Thats why the duplication https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad is connected to the question.. – Raymond Nijland Jul 17 '18 at 11:05
  • 1
    Well said, and hopefully the comments will help out the person into thinking about creating a relational table, I totally agree with the normalisation. – Alberto Vidales Jul 17 '18 at 11:07
  • The answer is not bad. The answer is good as it answers this specific question. The answer would be great, if it also pointed out that his db design is bad. But as said, this hint does not answer this specific question. The next guy that has (for what ever reason) the limitation on using a field to store csv will not be helped with the advice to change his db design (that he cannot, as he might use a third party service). – Zim84 Jul 17 '18 at 11:23