Add or modify part of an array as a mysql string

Question

I currently have a column in mysql database that stores a string with delimiters so I can convert it into an array in php.

An example string looks like this:

23,1,1|72,2,0|16,3,1|...etc.

It essentially divides it into 3 major groups with |, and 3 smaller ones with , (if there's a cleaner way, let me know):

1st number is an ID number for an article from a different table
2nd is just a number for indenting purposes
3rd is for visible or not (0 or 1).

I will have an admin section where we'll be able to re-order the major groups (i.e., move group 3 to position 2) and modify specific numbers from the sub-groups (e.g. change 72,1,0 to 72,2,0) I'm not sure how I can accomplish this.

How do I loop through these modifications while keeping the order (or new order) when reinserting into the database?

I was thinking of adding a another number to my string that would determine the position of each major group? Something like this:

1[23,1,1]2[72,2,0]3[16,3,1]

But how do I loop through this and move things around?

Any help would be greatly appreciated.

Of course there's a cleaner way. As you are using a database, not a text file to store your data, you should be using mechanisms that a database offers. Thus instead of this home-brewed "database-in-a-cell" you should make it another table, and then use JOIN to retrieve the data. And please keep in mind then there is no other way. The sooner you realize than the more time and effort you'll save. — Your Common Sense
– Your Common Sense, Commented Oct 10, 2016 at 3:24
I wish I could use other tables with join instead, but what I am building will not work this way, unfortunately. I'm building a "user manual" with multiple versions (ongoing versions). One table will be for all the content of all versions, the other table will list all versions with one of the column as what I showed above. It will tell which content and in what order to display it for each version. — KeepCool
– KeepCool, Commented Oct 11, 2016 at 3:57

colonelclick · Accepted Answer · 2016-10-10 03:58:14Z

1

I agree with the comments about normalization, but if you insist on doing it this way, or are stuck with an existing schema you cannot alter, use the PHP serialize/unserialize functions if you can, rather than string parsing. This will at least allow you to retrieve the data into PHP and modify the array and then save it back.

http://php.net/manual/en/function.serialize.php

answered Oct 10, 2016 at 3:58

colonelclick

2,2432 gold badges26 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Paul T. Rawkeen · Accepted Answer · 2016-10-14 15:56:48Z

I'm joining all comments about the approach used to store data, but nevertheless.

This what can help you to move forward:

/** @var string $s Assumed packet */
$s = "23,3,1|72,1,0|16,2,1"; // Indent is not ordered

/** @var array $parsed Parsed packet */
$parsed = array_map(function ($segment) {
    list($artId, $indent, $visibility) = explode(',', $segment);
    return [
        'ArticleId' => (int)$artId,
        'Indent' => (int)$indent,
        'Visible' => (int)$visibility !== 0
    ];
}, explode('|', $s));

usort($parsed, function (array $a, array $b) {
    return ($a['Indent'] < $b['Indent']) ? -1 : 1;
});

You'll get following $parsed structure sorted by Indent key:

array(3) {
  [0] => array(3) {
    ["ArticleId"]=>  int(23)
    ["Indent"]=>     int(1)
    ["Visible"]=>    bool(true)
  }
  [1] => array(3) {
    ["ArticleId"]=>  int(72)
    ["Indent"]=>     int(2)
    ["Visible"]=>    bool(false)
  }
  [2] => array(3) {
    ["ArticleId"]=>  int(16)
    ["Indent"]=>     int(3)
    ["Visible"]=>    bool(true)
  }
}

Thus you can alter Indent as you want just applying usort() before/after parsing.

Regarding storing this structure in database the way you decided, you can use JSON format (json_encode(),json_decode()). Such "serialization" way faster than proposed serialize() method and way more faster than $parsed approach + more readable. If you worry about redundancy you can json_encode() array without array keys and add them on parsing or use directly [0], [1], [2] knowing the correspondence beforehand.

If you use json_*() functions you can omit structure parsing bec. it will be decoded right the same you've encoded it for saving. Order can be defined on save using same usort(). This can be considered as improvement by reducing excessive sorts bec. readings/decodings will occur more frequently than saves.

YVS1102 · Accepted Answer · 2016-10-10 04:06:58Z

0

I was interested with your question. So i create this. It may not like what you desired.

<?php
    $string = "23,1,1|72,2,0|16,3,1|";
    $explode = explode("|", $string);



    $a = array();
    for($x = 0;$x<count($explode)-1;$x++)
    {
        $a[] = $explode[$x];
        $b[] =explode(',',substr($explode[$x], strpos($explode[$x], ",") + 1));   
    }

    for($y=0;$y<count($b);$y++){
        echo $b[$y][0]. '=>'. $a[$y] . '<br>'; 
    }


    ?>

answered Oct 10, 2016 at 4:06

YVS1102

2,7407 gold badges38 silver badges65 bronze badges

1 Comment

KeepCool Over a year ago

Thank you. This does work, but how do I reorder the major groups of that array string. So move 16,3,1 to position "2" for example: 23,1,1|16,3,1|72,2,0 ?

Collectives™ on Stack Overflow

Add or modify part of an array as a mysql string

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related