MySql Loop through a column to form concatenated string

[shades]

Hi guys,

 

I have a requirement where the end user enters text in an HTML form, which I am storing in MySQL db. Later i query all the tables in the db which has the user data to obtain concatenated strings.

Right now i am able to concatenate only one string based on id(as shown below). I know i can use a loop on id, but the problem is the ids are not fixed.

Note: There can be "n" number of dimensions and levels.

For a clear understanding find the attached image

Here is an SQL Fiddle of the data i am trying to query : http://sqlfiddle.com/#!9/fa366/81

Edited July 22, 2017 by shades

[Jacques1]

SQL is not a replacement for all programming languages. Rendering data or processing it in special ways is still performed by the application, not the database system.

 

So select the groups/dimensions/… you need, turn the data into a convenient format (like an array of arrays), then build the word combinations with PHP (or whatever language you happen to prefer).

<?php

$texts = ['A', 'year old', 'is'];
$values = [
    [20, 30, 40],
    ['Man', 'Woman'],
    ['fresher', 'experienced'],
];

foreach (cartesian_product(...$values) as $combined_values)
{
    $line = '';
    foreach ($combined_values as $i => $value)
    {
        if ($i > 0)
        {
            $line .= ' ';
        }

        $line .= html_escape($texts[$i], 'UTF-8').' '.html_escape($value, 'UTF-8');
    }

    echo $line.'<br>';
}

<?php

// this is a naive recursive implementation; if you have a lot of data, you may need a more efficient solution
function cartesian_product($head, ...$tail)
{
    if (!$tail)
    {
        return array_map(function ($item) { return [$item]; }, $head);
    }
    else
    {
        $prod = [];
        $prod_of_tail = cartesian_product(...$tail);
        foreach ($head as $left_item)
        {
            foreach ($prod_of_tail as $right_items)
            {
                $prod[] = array_merge([$left_item], $right_items);
            }
        }

        return $prod;
    }
}

function html_escape($raw_text, $encoding)
{
    return htmlspecialchars($raw_text, ENT_QUOTES | ENT_SUBSTITUTE, $encoding);
}

[shades]

Thanks a lot That worked beautifully.

Edited July 26, 2017 by cyberRobot
removed quote for Jacques1's post

[shades]

Hi,

 

Sorry for reposting on this question. With reference to your solution, could you please explain me what exactly is happening in the below condition ?  I understood the following code but this, I am not able to understand.

if (!$tail)
    {
        return array_map(function ($item) { return [$item]; }, $head);
    }

Thanks

[requinix]

If you haven't already checked the documentation, do it now.

 

array_map() executes a callback function on each item in an array, and returns a new array using the returned values.

function array_map($callback, array $array) {
	$return = array();
	foreach ($array as $key => $value) {
		$return[$key] = $callback($value);
	}
	return $return;
}

The code you're looking at will turn each value in $head into its own sub-array:

[1, 2, 3] -> [[1], [2], [3]]