Concatenating in JQuery from 2 table columns

Question

So I have pieced together the below and I'm not getting the expected outcome I am looking for

    /* Build SQL string */ 
$(".ConvertSQL").click(function () {

    $(".TableQuery").children("tbody").children("tr").each(function (i) {

        $("#SQLstring").val("")

        var $this = $(this);
        var my_td = $this.children("td");
        var first_col = my_td.eq(0);
        var second_col = my_td.eq(1);
        var query = "SELECT "
        var text = $("#SQLstring").val()

        query = query + "[" + first_col.html() + "].[" + second_col.html() + "],";
        first_col = ""
        second_col = ""


        $("#SQLstring").val(text + query) 

    }); 

    /* Remove some text from the start of the string thats not needed */
    $('#SQLstring').contains('SELECT [undefined].[undefined],').remove();

});

There are no errors I'm receiving as such.

The code loops through the first 2 columns in my table and build an SQL query

The current outcome is:

SELECT [Test Table].[Test Field],

It should be basically concatenating eat row together to show something like:

SELECT [Test Table].[Test Field], [Test Table].[Test Field],

Rory McCrossan · Accepted Answer · 2020-03-09 11:41:58Z

1

The issue is because you define query within each iteration of the loop. You need to define it outside then add to it as the loop progresses.

Similarly you need to only update the '#SQLstring field after the loop completes. The setting of #SQLstring to empty can be removed along with the code to retrieve and concatenate it's value (which is redundant as the first thing the loop does is empty the value, so it's always empty). Try this:

$(".ConvertSQL").click(function() {
  var query = "SELECT ";

  $(".TableQuery").children("tbody").children("tr").each(function(i) {
    var $td = $(this).children("td");
    query += "[" + $td.eq(0).html() + "].[" + $td.eq(1).html() + "],";
  });

  $("#SQLstring").val(query.slice(0, -1)); // slice is used to remove trailing comma
});

input { 
  width: 300px;
}

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>

<table class="TableQuery">
  <tbody>
    <tr>
      <td>Foo</td>
      <td>Bar</td>
    </tr>
    <tr>
      <td>Lorem</td>
      <td>Ipsum</td>
    </tr>
  </tbody>
</table>
<button class="ConvertSQL">Convert SQL</button>
<input type="text" id="SQLstring" />

That being said a better approach would be to use map() to build an array from the values which you can then join() back together, like this:

$(".ConvertSQL").click(function() {
  var query = $('.TableQuery > tbody > tr').map(function() {
    var $td = $(this).children('td');
    return `[${$td.eq(0).html()}].[${$td.eq(1).html()}]`;
  }).get().join(', ');
  
  $("#SQLstring").val(`SELECT ${query}`);
});

input { 
  width: 300px;
}

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>

<table class="TableQuery">
  <tbody>
    <tr>
      <td>Foo</td>
      <td>Bar</td>
    </tr>
    <tr>
      <td>Lorem</td>
      <td>Ipsum</td>
    </tr>
  </tbody>
</table>
<button class="ConvertSQL">Convert SQL</button>
<input type="text" id="SQLstring" />

edited Mar 9, 2020 at 11:41

answered Mar 9, 2020 at 11:22

Rory McCrossan

338k41 gold badges322 silver badges353 bronze badges

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

4 Comments

tryer Over a year ago

Thank you, that makes sense. I can also see you added the + to my =, Ive just looked this up and think I understand the syntax for that. Spent a while trying this. Doh!

Rory McCrossan Over a year ago

No problem, glad to help. Note that I just edited the answer to include a better approach using map()

tryer Over a year ago

Thanks again for the help. Ive just moved it into the .map() method. Seems far better, ill need to do more work on constructing this myself as I had not heard of it before. One other question, my text outcome always starts with SELECT [undefined].[undefined]. I guess this is because its trying to read row 0 first?

Rory McCrossan Over a year ago

That sounds like the likely cause. Without seeing the relevant HTML I can't really show you how to avoid it, though.

Shamim Shaikh · Accepted Answer · 2020-03-11 06:22:51Z

0

Your code would be like

$(".ConvertSQL").click(function () {
    var sqlQuery = [];
    $(".TableQuery").children("tbody").children("tr").each(function (i) {

        $("#SQLstring").val("");
        var $this = $(this);
        var my_td = $this.children("td");
        var first_col = my_td.eq(0);
        var second_col = my_td.eq(1);

        var text = $("#SQLstring").val()

        var query = `SELECT [${first_col.html()}].[${second_col.html()}]`;
        if(first_col.html() != undefined && second_col.html() != undefined ){
            sqlQuery.push(query);
        }
    }); 

  $("#SQLstring").val(sqlQuery.join(',')); 
});

edited Mar 11, 2020 at 6:22

answered Mar 9, 2020 at 11:36

Shamim Shaikh

8479 silver badges15 bronze badges

1 Comment

Rory McCrossan Over a year ago

You can remove var query = "SELECT "

Collectives™ on Stack Overflow

Concatenating in JQuery from 2 table columns

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related