SQL Separated delimiter with secondary query

Question

I have a table with columns

name
triggers

The trigger column stores a comma delimited string such as (2,4,58,72) and I have been able to write the following query to separate the values for me into individual lines but now I am stuck.

My existing query is:

SELECT name, link, content, bg_img, SUBSTRING_INDEX(SUBSTRING_INDEX(t.triggers, ',', n.n), ',', -1) value
FROM ad_banners t CROSS JOIN 
(
    SELECT a.N + b.N * 10 + 1 n
    FROM 
    (
        SELECT 0 AS N
        UNION ALL SELECT 1
        UNION ALL SELECT 2
        UNION ALL SELECT 3
        UNION ALL SELECT 4 
        UNION ALL SELECT 5 
        UNION ALL SELECT 6 
        UNION ALL SELECT 7 
        UNION ALL SELECT 8 
        UNION ALL SELECT 9) a,
        (
            SELECT 0 AS N
            UNION ALL SELECT 1
            UNION ALL SELECT 2 
            UNION ALL SELECT 3 
            UNION ALL SELECT 4 
            UNION ALL SELECT 5 
            UNION ALL SELECT 6 
            UNION ALL SELECT 7 
            UNION ALL SELECT 8 
            UNION ALL SELECT 9) b
            ORDER BY n
        ) n
        WHERE n.n <= 1 + (LENGTH(t.triggers) - LENGTH(REPLACE(t.triggers, ',', ''))) 
        AND live = 'yes' 
        AND end_date >= CURDATE()
        ORDER BY value

The issue is that my second query is dependent on the individual variables separated for the "value" in this query but there is no column by this name to truly query.

How can I set up a secondary query or modify the above query to separate the delimited variables and then filter by a specific value of "trigger"

The end goal is that i want to search for all entries with trigger ## but the triggers need to be separated before this can occur.

Any ideas?

I am trying to build out a custom function so the series is repeatable as follows:

function logic_banners() {

$code = $_COOKIE['lgc_cntl'];
$query = ("SELECT name, link, content, bg_img, SUBSTRING_INDEX(SUBSTRING_INDEX(t.triggers, ',', n.n), ',', -1) value
           FROM ad_banners t CROSS JOIN 
           (
               SELECT a.N + b.N * 10 + 1 n
               FROM 
               (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a,
               (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
               ORDER BY n
           ) n
           WHERE n.n <= 1 + (LENGTH(t.triggers) - LENGTH(REPLACE(t.triggers, ',', '')))
           AND live = 'yes'
           AND end_date >= CURDATE()
           ORDER BY value");
$result = mysql_query($query);
$nums = mysql_num_rows($result);

for ($i=0; $i < $nums_1; $i++) 
{ 
    $row = mysql_fetch_assoc($result); 

    $qry_a = ("SELECT * FROM ad_banners where value = '".$code."';");
    $result_a = mysql_query($qry_a);
    $row_a = mysql_fetch_assoc($result_a);

    $bg_img = $row_a['bg_img'];
    $content = $row_a['content'];
    $link = $row_a['link'];

    echo $content;

    /*echo '<div class="logic_banner col-xs-6 col-md-12 col-lg-12">
                <figure><img src="'.$bg_img.'" width="471" height="469" alt="img"></figure>
                <div class="logic_banner_in">
                    <div class="mdl">
                        <div class="mdl_in">
                            <a href="'.$link.'" target="_blank">
                                <h6>'.$content.'</h6>
                            </a>
                        </div>
                    </div>
                </div>
            </div>';*/
    }
}

You should normalize your column/table; http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad. — chris85, Feb 02 '16 at 20:48
oh wow, you are looking for `FIND_IN_SET()` !!! http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_find-in-set — cmorrissey, Feb 02 '16 at 20:52
@cmorrissey - how would I use the FIND_IN_SET since my array is the piece I am wanting to review? — Jonathan Anspaugh, Feb 02 '16 at 22:56

score 0 · Accepted Answer · answered Feb 04 '16 at 17:20

0

@Chris85 was correct and normalizing my tables solved my issues.

answered Feb 04 '16 at 17:20

Jonathan Anspaugh

125
1
10

SQL Separated delimiter with secondary query

1 Answers1