Jump to content
Sign in to follow this  
shades

MySql Loop through a column to form concatenated string

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

Share this post


Link to post
Share on other sites

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);
}

Share this post


Link to post
Share on other sites

Thanks a lot :) That worked beautifully.

Edited by cyberRobot
removed quote for Jacques1's post

Share this post


Link to post
Share on other sites

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

Share this post


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]]

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

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.