I am trying to create dynamic table views. I have two arrays. I want to create a string if there is a match.
Here are the arrays:
(
[0] => 20
[optionid] => 20
)
Array
(
[0] => Array
(
[1] => GROUP_CONCAT(if(optionid = 1, value_name, NULL)) AS 'Color'
)
[1] => Array
(
[22] => GROUP_CONCAT(if(optionid = 22, value_name, NULL)) AS 'Crystal Color'
)
[2] => Array
(
[25] => GROUP_CONCAT(if(optionid = 25, value_name, NULL)) AS 'Gauge'
)
[3] => Array
(
[35] => GROUP_CONCAT(if(optionid = 35, value_name, NULL)) AS 'Height'
)
[4] => Array
(
[18] => GROUP_CONCAT(if(optionid = 18, value_name, NULL)) AS 'Length'
)
[5] => Array
(
[33] => GROUP_CONCAT(if(optionid = 33, value_name, NULL)) AS 'Pieces in Pack'
)
[6] => Array
(
[26] => GROUP_CONCAT(if(optionid = 26, value_name, NULL)) AS 'Pincher Size'
)
[7] => Array
(
[24] => GROUP_CONCAT(if(optionid = 24, value_name, NULL)) AS 'Rack'
)
[8] => Array
(
[20] => GROUP_CONCAT(if(optionid = 20, value_name, NULL)) AS 'Ring Size'
)
[9] => Array
(
[2] => GROUP_CONCAT(if(optionid = 2, value_name, NULL)) AS 'Size'
)
[10] => Array
(
[34] => GROUP_CONCAT(if(optionid = 34, value_name, NULL)) AS 'Size in Pack'
)
)
For example, value 20 on the first array matches key 20 on the second so I want the value added to a string. If they are more than two matches I want the string to be appended.
the end result should look like
$string = "GROUP_CONCAT(if(optionid = 20, value_name, NULL)) AS 'Ring Size'"
if more than one match.
$string = "GROUP_CONCAT(if(optionid = 20, value_name, NULL)) AS 'Ring Size', NEXT MATCHED VALUE"
Flatten the array out if you can, so instead of $array[0][20], its simply accessed by $array[20].
Then you can just loop over the option id's you need, pull out the string and append it to a master sql string, along with appending a ', '. After the loop you can then just rtrim($string, ", ") to remove the final comma
Just looking at it, I don't think you need to do it the way you're doing it (with the full statement in the array). There's only two things that change in the statement, so put the id and alias into an array and find by that:
<?php
function getConcat($id) {
$group = "GROUP_CONCAT(if(optionid = %d, value_name, NULL)) AS `%s`";
$groups = array(
1 => 'Color',
2 => 'Size',
18 => 'Length',
20 => 'Ring Size',
22 => 'Crystal Color',
24 => 'Rack',
25 => 'Gauge',
26 => 'Pincher Size',
33 => 'Pieces in Pack',
34 => 'Size in Pack',
35 => 'Height'
);
if ((!is_string($id) && !is_numeric($id)) || !isset($groups[$id])) {
return '';
}
return sprintf($group, $id, $groups[$id]);
}
echo getConcat(22)."
";
echo getConcat(50)."
";
echo getConcat(35)."
";
echo getConcat(18)."
";
echo getConcat(1)."
";
?>
The above will give you the query statement. You could setup a function to also recursively concatenate a list of statements as well:
<?php
function getConcatList($arrList=array(), $strList=NULL) {
$group = "GROUP_CONCAT(if(optionid = %d, value_name, NULL)) AS `%s`";
if (!is_string($arrList) && !is_numeric($arrList) && !is_array($arrList)) {
return '';
} else if (is_string($arrList) || is_numeric($arrList) && !empty($arrList)) {
$arrList = array($arrList);
}
$groups = array(
1 => 'Color',
2 => 'Size',
18 => 'Length',
20 => 'Ring Size',
22 => 'Crystal Color',
24 => 'Rack',
25 => 'Gauge',
26 => 'Pincher Size',
33 => 'Pieces in Pack',
34 => 'Size in Pack',
35 => 'Height'
);
$c_list = array_shift($arrList);
if ($groups[$c_list]) {
$c_strList = sprintf($group, $c_list, $groups[$c_list]);
if (strlen($strList)) {
$c_strList = "$strList, $c_strList";
}
} else {
$c_strList = $strList;
}
if (!count($arrList)) {
return $c_strList;
}
return getConcatList($arrList, $c_strList);
}
echo getConcatList(22)."
";
echo getConcatList(50)."
";
echo getConcatList(array(35, 33))."
";
echo getConcatList(array(1, 18, 35, 33))."
";
echo getConcatList(array(1, 18, 70, 35, 33))."
";
?>