CODEWITHSUNDEEP
jquery
tryer
tryer

Reputation: 81

Concatenating in JQuery from 2 table columns

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

Upvotes: 2

Views: 42

Answers (2)

Shamim Shaikh
Shamim Shaikh

Reputation: 827

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(',')); 
});

Upvotes: 0

Rory McCrossan
Rory McCrossan

Reputation: 337627

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" />

Upvotes: 1

Related Questions