Jump to content

MySql Loop through a column to form concatenated string


Go to solution Solved by Jacques1,

Recommended Posts

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


post-204768-0-05785400-1500733343_thumb.png

Edited by shades
Link to post
Share on other sites
  • Solution

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);
}
Link to post
Share on other sites
  • 2 months later...

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

Link to post
Share on other sites

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]]
Link to post
Share on other sites
This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.