I've created a method what creates a list of items but not every value is filled. I've tried several ways but none of them fixed the issue. I've played with the if else statements but it didn't fixed the result. I still get less values then I expect. Can someone give me a hint or rewrite my method, so I can go back to my project.
Method:
public function getDataListItems(int $category, array $list)
{
global $dbh;
$query = 'SELECT data.value, data.uid, fields.name
FROM data
JOIN fields ON data.field_id = fields.id
WHERE fields.category_id = "' . trim($category) .
'" ORDER BY uid';
$sql = $dbh->prepare($query);
$sql->execute();
$values = $sql->fetchAll(PDO::FETCH_ASSOC);
$data = '';
$items = [];
foreach ($values as $value) {
foreach ($list as $key) {
if(!empty($data)){
if($data == $value['uid']) {
if($key == $value['name']) {
$item = [
$value['name'] => $value['value'],
'uid' => $value['uid'],
];
}else{
$item = [
$key => '',
'uid' => $value['uid'],
];
}
$items[$value['uid']] = array_merge($items[$value['uid']], $item);
}else{
if($key == $value['name']) {
$items[$value['uid']] = [
$value['name'] => $value['value'],
'uid' => $value['uid'],
];
}else{
$items[$value['uid']] = [
$key => '',
'uid' => $value['uid'],
];
}
}
}else{
if($key == $value['name']) {
$items[$value['uid']] = [
$value['name'] => $value['value'],
'uid' => $value['uid'],
];
}else{
$items[$value['uid']] = [
$key => '',
'uid' => $value['uid'],
];
}
}
$data = $value['uid'];
}
}
return $items;
}
In $list you'll find:
array(3) {
["Voornaam"]=> string(8) "Voornaam"
["Achternaam"]=> string(10) "Achternaam"
["Initialen"]=> string(9) "Initialen"
}
in $values you'll find:
array(7) {
[0]=>
array(3) {
["value"]=> string(7) "Bettina"
["uid"]=> string(15) "7d1f4f8e906245f"
["name"]=> string(8) "Voornaam"
}
[1]=>
array(3) {
["value"]=> string(3) "Les"
["uid"]=> string(15) "7d1f4f8e906245f"
["name"]=> string(10) "Achternaam"
}
[2]=>
array(3) {
["value"]=> string(6) "Simone"
["uid"]=> string(15) "7d1f4f8e906245g"
["name"]=> string(8) "Voornaam"
}
[3]=>
array(3) {
["value"]=> string(4) "Yül"
["uid"]=> string(15) "7d1f4f8e906245l"
["name"]=> string(10) "Achternaam"
}
[4]=>
array(3) {
["value"]=> string(6) "Joshua"
["uid"]=> string(15) "7d1f4f8e906245s"
["name"]=> string(8) "Voornaam"
}
[5]=>
array(3) {
["value"]=> string(3) "Mas"
["uid"]=> string(15) "7d1f4f8e906245s"
["name"]=> string(10) "Achternaam"
}
[6]=>
array(3) {
["value"]=> string(5) "Hello"
["uid"]=> string(15) "gGcYEJdRYJ1vqcn"
["name"]=> string(10) "Achternaam"
}
}
What I get in the return:
["7d1f4f8e906245f"]=>
array(4) {
["Voornaam"]=> string(0) ""
["uid"]=> string(15) "7d1f4f8e906245f"
["Achternaam"]=> string(3) "Les"
["Initialen"]=> string(0) ""
}
["7d1f4f8e906245g"]=>
array(4) {
["Voornaam"]=> string(6) "Simone"
["uid"]=> string(15) "7d1f4f8e906245g"
["Achternaam"]=> string(0) ""
["Initialen"]=> string(0) ""
}
["7d1f4f8e906245l"]=>
array(4) {
["Voornaam"]=> string(0) ""
["uid"]=> string(15) "7d1f4f8e906245l"
["Achternaam"]=> string(4) "Yül"
["Initialen"]=> string(0) ""
}
["7d1f4f8e906245s"]=>
array(4) {
["Voornaam"]=> string(0) ""
["uid"]=> string(15) "7d1f4f8e906245s"
["Achternaam"]=> string(3) "Mas"
["Initialen"]=> string(0) ""
}
["gGcYEJdRYJ1vqcn"]=>
array(4) {
["Voornaam"]=> string(0) ""
["uid"]=> string(15) "gGcYEJdRYJ1vqcn"
["Achternaam"]=> string(5) "Hello"
["Initialen"]=> string(0) ""
}
}
What I expect result array:
["7d1f4f8e906245f"]=>
array(4) {
["Voornaam"]=> string(0) "Bettina"
["uid"]=> string(15) "7d1f4f8e906245f"
["Achternaam"]=> string(3) "Les"
["Initialen"]=> string(0) ""
}
["7d1f4f8e906245g"]=>
array(4) {
["Voornaam"]=> string(6) "Simone"
["uid"]=> string(15) "7d1f4f8e906245g"
["Achternaam"]=> string(0) ""
["Initialen"]=> string(0) ""
}
["7d1f4f8e906245l"]=>
array(4) {
["Voornaam"]=> string(0) ""
["uid"]=> string(15) "7d1f4f8e906245l"
["Achternaam"]=> string(4) "Yül"
["Initialen"]=> string(0) ""
}
["7d1f4f8e906245s"]=>
array(4) {
["Voornaam"]=> string(0) "Joshua"
["uid"]=> string(15) "7d1f4f8e906245s"
["Achternaam"]=> string(3) "Mas"
["Initialen"]=> string(0) ""
}
["gGcYEJdRYJ1vqcn"]=>
array(4) {
["Voornaam"]=> string(0) ""
["uid"]=> string(15) "gGcYEJdRYJ1vqcn"
["Achternaam"]=> string(5) "Hello"
["Initialen"]=> string(0) ""
}
}
You see that I miss some values in my return list.
You want to group the results based on the uid values and set some default values -- this can be done without so many conditions. Use isset() to determine if the uid is being encountered for the first time. If so, set the defaults. Then overwrite the defaults with every subsequent encounter for that same uid.
Code: (Demo)
$values = [
['value' => 'Bettina', 'uid' => '7d1f4f8e906245f', 'name' => 'Voornaam'],
['value' => 'Les', 'uid' => '7d1f4f8e906245f', 'name' => 'Achternaam'],
['value' => 'Simone', 'uid' => '7d1f4f8e906245g', 'name' => 'Voornaam'],
['value' => 'Yül', 'uid' => '7d1f4f8e906245l', 'name' => 'Achternaam'],
['value' => 'Joshua', 'uid' => '7d1f4f8e906245s', 'name' => 'Voornaam'],
['value' => 'Mas', 'uid' => '7d1f4f8e906245s', 'name' => 'Achternaam'],
['value' => 'Hello', 'uid' => 'gGcYEJdRYJ1vqcn', 'name' => 'Achternaam'],
];
$list = ['Voornaam', 'Achternaam', 'Initialen'];
foreach ($values as $row) {
if (!isset($items[$row['uid']])) {
$items[$row['uid']] = array_fill_keys($list, ''); // if it needs to dynamically generated
$items[$row['uid']]['uid'] = $row['uid'];
}
$items[$row['uid']][$row['name']] = $row['value'];
}
var_export($items);
Output:
array (
'7d1f4f8e906245f' =>
array (
'Voornaam' => 'Bettina',
'Achternaam' => 'Les',
'Initialen' => '',
'uid' => '7d1f4f8e906245f',
),
'7d1f4f8e906245g' =>
array (
'Voornaam' => 'Simone',
'Achternaam' => '',
'Initialen' => '',
'uid' => '7d1f4f8e906245g',
),
'7d1f4f8e906245l' =>
array (
'Voornaam' => '',
'Achternaam' => 'Yül',
'Initialen' => '',
'uid' => '7d1f4f8e906245l',
),
'7d1f4f8e906245s' =>
array (
'Voornaam' => 'Joshua',
'Achternaam' => 'Mas',
'Initialen' => '',
'uid' => '7d1f4f8e906245s',
),
'gGcYEJdRYJ1vqcn' =>
array (
'Voornaam' => '',
'Achternaam' => 'Hello',
'Initialen' => '',
'uid' => 'gGcYEJdRYJ1vqcn',
),
)
Truth be told, if this was my application, I would be writing a pivot query and doing all of this in sql so that the fetchAll() value could be instantly returned.