Display SQL Query Result in HTML

Question

I need to use javascript and HTML5 to display the result of the following SQL query in my html page. The SQL query works in SQLite Browser, but I am unsure of how to write the function and corresponding HTML5 code to call the function to display the result of my query. The SQL Query is as follows:

SELECT SUM(Orders.productQty * Products.productPrice) AS grandTotal FROM Orders JOIN Products ON Products.productID = Orders.productID

This returns a numerical result from my SQLite database that is already created, but I do not get how to display the result of the select query on my webpage.

I've tried using the following function to execute the sql statement, but I do not know how to display it using HTML.

function calculateTotalDue() {
db.transaction(function (tx) {
    tx.executeSql('SELECT SUM(Orders.productQty * Products.productPrice) AS grandTotal FROM Orders JOIN Products ON Products.productID = Orders.productID', [], []);
});

}

Would someone please show me how to display the result of the query in my html page?

Yes. Display the result on my html page, but there should only be one result, the sum. — EzAnalyst
– EzAnalyst, Commented Jan 4, 2013 at 20:38

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

What you need is a function in the third parameter of the executeSql call. like this ( this is an example if you have mulitple results, but will work with your query too ):

Javascript

function calculateTotalDue() {
  db.transaction(function (tx) {
      tx.executeSql('SELECT SUM(Orders.productQty * Products.productPrice) AS grandTotal FROM Orders JOIN Products ON Products.productID = Orders.productID', [], 
      function(){
        // Get return rows
        var data = result.rows;

        // Initialize variable to store your html
        var html = '';

        // loop thru results
        for (var i = 0; i < dataset.length; i++) {
          var row = data.item(i);

          // Add to html variable
          html += row.grandTotal;

          // Append that html somewhere
          // How todo this will vary depening on if you are using framworks or not
          // If just javascript use:
          // document.getElementById('results').innerHTML += html;


        }

      }
    );
  });
}

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jan 4, 2013 at 20:40

jsweazy

1,6319 silver badges22 bronze badges

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

3 Comments

EzAnalyst Over a year ago

I see. Is it possible to use an id from my page like <p id="totalDue"></p> to call the stored variable in my function?

jsweazy Over a year ago

uncomment that last line then change the getElementById('results') to getElementById('totalDue')

EzAnalyst Over a year ago

Got it! Thank you. Appreciate the help.

Collectives™ on Stack Overflow

Display SQL Query Result in HTML

1 Answer 1

Javascript

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Javascript

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related