Database Array Explode/Implode

[LaocheXe]

I've been wondering about this for years now, and have asked around but still I don't understand how this works.

I have a service records plugin, which stores data of users, some of the details stored are in an array (kind of like userclasses in the user database), It saves unique id's from other tables. which would end up looking like (1,4,15,22, etc...).

I want to show the details in the user profile, however I can't wrap my head around it. explode/implode - if I explode the array, how would I link the numbers(id's) to the other database to return their name, or other information?

Since it's similar to userclasses, I've been digging around the code and end up null on my end.

---- Alright, time for a better example. I think....

So I have a list of "Advanced Clone Training" <--- name doesn't really matter for this - With this is a list of "Trainings" that a unit would have created for their members to do, etc....

In the database is looks like this:

Now for the unit, there is a service records area, which allows administrators to edit a member's service record to keep it up to date, etc...

That is stored as:

What I'd want to do is display that service record information on the user profile page. However, I'm use to do it like Jimmi08 said, in one query (but I know it can't be done, this is why I asked).

[Deltik]

e107 is currently not normalizing data correctly, so I recommend against storing data like how e107 saves e107_user.user_class. According to the first normal form (1NF), database table cells may not store an array data type.

Suppose you have an Author and many Books. To model that a Book belongs to an Author, you add a column books.author_id with a foreign key relationship to authors.id like this:

^{_{Source: https://guides.rubyonrails.org/association_basics.html#the-belongs-to-association}}

Author then has many books:

^{_{Source: https://guides.rubyonrails.org/association_basics.html#the-has-many-association}}

But what if you wanted to connect two tables together so that they each have many of each other? A Physician has many Patients through Appointments, and a Patient also has many Physicians through Appointments. Your database schema should look like this, then:

^{_{Source: https://guides.rubyonrails.org/association_basics.html#the-has-many-through-association}}

To fix e107 userclasses, a third table, e107_user_userclasses, should be created, and there should be columns like:

• e107_user_userclasses.id – Primary key
• e107_user_userclasses.user_id – Foreign key to e107_user.user_id
• e107_user_userclasses.userclass_id – Foreign key to e107_userclass_classes.userclass_id

This model would mean that an e107_user has many e107_userclass_classes through e107_user_userclasses and that an e107_user_userclasses has many e107_users through e107_user_userclasses, and this model would comply with 1NF.

[Jimmi08]

@LaocheXe you are using userclasses as bad example - and it is the reason for the deltic answer.

In your case you have a field with multiple values and options for this field are saved in a separate table.

For displaying just use explode + foreach values get displayed the needed value and implode them back to one string.

For searching use FIND IN SET

You are trying to get everything in one query, which is too complicated.

[LaocheXe]

So I would use a simple query for the Service records db, then explode the field with multiple id's, then foreach with a FIND IN SET query from the other db table?

[Jimmi08]

No, FIND IN SET is used when you need all users with that value x and value is part of string.
https://www.w3schools.com/sql/func_mysql_find_in_set.asp

Real example:

$charlist - array of ids and values;
$characters - user value (string of 1,2,3...)
$charlinks - result;

$characters_array = explode(",", $characters);
$charlinks = array( );

foreach($characters_array as $c) {
        if(empty($charlist[$c]['name'])) continue;
        if(***)   $charlinks[] = "<a href='"***'>".$charlist[$c]['name']."</a>";
        else $charlinks[] = $charlist[$c]['name'];
 }
return implode(", ", $charlinks);

You don't need array and implode(), normal string is enough too.

[LaocheXe]

Question, what is the ** for the if(***)? Trying to follow whats there, and I might be going at it wrong.

`
$actlist = $sql->retrieve('act_progress_sys', '*'); // Returns array
$actuprogress = $sr['actp_id']; // Returns actp id's that the user has in service records
$actuprogress_array = explode(',', $actuprogress);
$actplink = array( );

foreach($actuprogress_array as $act)
{
if(empty($actlist[$act]['actp_name'])) continue;
if(***) // I take this is for creating a link for the example, so would this be if(!empty??
}

`

[Jimmi08]

it was an example, you can put anything in that foreach, in my case there was a test if $action is printing or not.