Loop through array and add parameters to sql query in javascript

Question

I want to assign an sql sting to a variable in Javascript. But I want to loop through an array to build par of the sql statement.

static sql:

var tag = [fun, beach, sun];

var sql = "SELECT * FROM myTable "
            +"WHERE id > 5" //...
            // loop through tag array and add to the sql
            function tagQuery(tag) {
                for (var i = 0; i < tag.length; i++) {
                var tagQuery = "AND WHERE tags like %'"+tag[i]+"'%";
                return tagQuery;
                }; 
            }

It is not clear to me how to get each result of the tagQuery function to be added to the sql sting?

EDIT: maybe a more clear way to ask the question is: How can I get each iteration of the tagQuery function to be joined with the sql variable?

I see you've already marked as answer... DID IT WORK? Your original question, using LIKE, will find values of tags where all your values occur IN THE SAME STRING: tags LIKE '%fun%%beach%%sun%'. Is that really what you want? Or are you actually trying to find situations with tags LIKE '%fun%' and tags LIKE '%beach%' and tags LIKE '%sun%'? — Doug_Ivison
– Doug_Ivison, Commented Dec 5, 2013 at 20:27
No, Rusty's answer did not work (at first I thought it did). I made a mistake, I am really after: tags LIKE '%fun%' OR tags LIKE '%beach%' OR ... — Bwyss
– Bwyss, Commented Dec 5, 2013 at 21:01
Then how about str += " OR tags like '%" + tag[i] + "%' ", within the function, then replace var i = 0 with var i = 1 (the beginning of the loop), then make the last two lines sql += " AND tags like '%" + tag[0] + "%' "; sql += tagQuery(tag); — Doug_Ivison
– Doug_Ivison, Commented Dec 5, 2013 at 21:06
Well, the last two lines also need to place parentheses around all the conditions for tags, for example: sql += " AND (tags like '%" + tag[0] + "%' "; sql += tagQuery(tag) + ")"; — Doug_Ivison
– Doug_Ivison, Commented Dec 5, 2013 at 21:14
I posted my suggestions as another answer, but I upvoted Rusty's answer, because he did the "heavy lifting" ;) — Doug_Ivison
– Doug_Ivison, Commented Dec 5, 2013 at 22:07

Rusty Fausak · Accepted Answer · 2013-12-05 22:55:24Z

2

var tag = [fun, beach, sun];

var sql = "SELECT * FROM myTable WHERE id > 5";

function tagQuery(tag) {
    var str = '';
    for (var i = 0; i < tag.length; i++) {
        str += " AND tags like '%" + tag[i] + "%' "
    }
    return str;
}

sql += tagQuery(tag);

Fiddle: http://jsfiddle.net/7P4us/2/

edited Dec 5, 2013 at 22:55

answered Dec 5, 2013 at 19:31

Rusty Fausak

7,5541 gold badge30 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Doug_Ivison Over a year ago

Don't you want IN (with parentheses and commas) instead of LIKE?

ProfessionalAmateur Over a year ago

@Doug_Ivison - The example above will produce three lines it will wind up looking like this AND tags like '%fun%' AND tags like '%beach%' AND tags like '%sun%' The loop will append a whole new line each iteration. It should probably have some ORs in there too. Example AND (tags like '%fun%' OR tags like '%beach%' OR tags like '%sun%')

Bwyss Over a year ago

This answer does not seem to work because when I do: sql += tagQuery(tag); it will only return the last iteration of the tagQuery loop, instead of all of the iterations like: " AND tags like '%"fun"%' AND tags like '%"beach"%' AND tags like '%"sun"%' "

Bwyss Over a year ago

The original question is still not clear, how can I get all of the iterations of the loop to be joined to the sql variable, not just the last iteration?

Bwyss Over a year ago

I really appreciate all the help and patience with this, and apologies for not being more clear. Yes, there was an issue with my implementation. I now have it working. Thanks

|

Doug_Ivison · Accepted Answer · 2013-12-05 23:36:55Z

0

EDIT:
I took out the tagQuery function altogether, after you commented:
sql += tagQuery(tag); ...will only return the last iteration of the tagQuery loop

var tag = ['fun', 'beach', 'sun'];
var sql = "SELECT * FROM myTable WHERE id > 5";

if (tag[0] != null) {
    sql += " AND (tags like '%" + tag[0] + "%' "; 
}

if (tag[1] != null) {
    for (var i = 1; i < tag.length; i++) {
        sql += " OR tags like '%" + tag[i] + "%' ";
    }
}

if (tag[0] != null) {
    sql += ")";
}

But note: the jsFiddle link that Rusty put in a comment, since, shows that the tagQuery function can return all iterations... so seeing only the last iteration was probably in your implementation.

edited Dec 5, 2013 at 23:36

answered Dec 5, 2013 at 21:57

Doug_Ivison

6487 silver badges19 bronze badges

Collectives™ on Stack Overflow

Loop through array and add parameters to sql query in javascript

2 Answers 2

11 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related