Performing a MySql query in JavaScript

[Andrew_52]

I have a table on my web page that is currently updating with random Data, see below:

function changeContent(){
    var i = 1;
    var len = numberRows;
    var random1 = Math.floor( Math.random() * 90 ) + 10;
    var random2 = Math.floor( Math.random() * 90 ) + 10;
    var x=document.getElementById('myTable').rows;
    var y=x[0].cells;

  for (; i < len; i++) {
    var d = new Date(); // for now
    y=x[i].cells;
    y[0].innerHTML=eventNumber++;
    y[1].innerHTML=Math.floor( Math.random() * 90 ) + 10000
    y[2].innerHTML= (d.getHours()<10?'0':'') + d.getHours() + 
      ":" + (d.getMinutes()<10?'0':'') + d.getMinutes() + 
      ":" + (d.getSeconds()<10?'0':'') + d.getSeconds();
    y[3].innerHTML=Math.floor( Math.random() * 90 ) + 10; 
    y[4].innerHTML=Math.floor( Math.random() * 20 ) + 5;
    y[5].innerHTML=Math.floor( Math.random() * 20 ) + 5;
    y[6].innerHTML=Math.floor( Math.random() * 20 ) + 5;    
    y[7].innerHTML=Math.floor( Math.random() * 20 ) + 5;
    y[8].innerHTML=Math.floor( Math.random() * 20 ) + 5;        
  }
}

I would like replace the (Math.random() * 20) + 5 , with a query that is going to pull data from a MySQL database that I have created.

Any help would be greatly appreciated.

Best Regards.
Andy

[pty]

Essentially what you need is something that sits between the web page and the database. We'd often refer to this layer as an API, but just think of it as a simple program that receives HTTP requests and responds with some data.

Here's a really simple example written in Ruby with Sinatra.

require "sinatra"
require "json"

before do
  content_type :json
end

get "/" do
  [1,2,3,4,5].to_json
end

Now, when I run this program and make a request, we can see exactly what happens. By default, Sinatra listens on port 4567

$ http get localhost:4567

HTTP/1.1 200 OK
Connection: keep-alive
Content-Length: 11
Content-Type: application/json
Server: thin 1.5.1 codename Straight Razor
X-Content-Type-Options: nosniff

[
    1,
    2,
    3,
    4,
    5
]

Once you have this working you should be able to create a method in JavaScript that makes AJAX requests to your API. The easiest way to do this is to use the new standard Fetch API or something like Axios.

Here's me making the request using fetch in Chrome against the API I just wrote

Simple, eh? The next step would be to enhance this program (or a similar one written in the language of your choice) and connect it to a database. But, one step at a time.

[ryantroop]

You are going to need to learn about AJAX, some server side language to format your JSON to output to the AJAX request, and of course - SQL to write the query that the server side language will process/execute.

A very common free stack is called a LAMP stack, which stands for Linux (the server OS), Apache (the web server), MySQL (database), PHP (server side language / processor) stack. It is common/popular because it's free, and all you need is a host (which, if you look hard enough, you can get for free as well - but if this is sensitive information, or information that you want to keep for future use, you may want to look into a paid hosting solution).

There are other iterations of this: if you are a windows user, there is also a WAMP stack (windows instead of linux) but it tends to cost more.

Of course, there are also other free server side languages to pick from - Ruby on Rails, Python (Flask is common), ASP, NodeJS, etc... but each has their own strengths and weakenesses, as well as learning curves.

And other database options as well: Oracle, TSQL/MSSQL, MongoDB, .... (this list is crazy long, for the most part they are all the "same" but different). As well as the option to go with NoSQL but... meh.

Hope that helps,

Ryan

[Andrew_52]

Ah thanks guys - I am a novice with these things, I have done a similar thing before using PHP which has a good API for connecting to MySQL and sending it queries. I was hoping to by-pass the PHP and do everything in JavaScript.

I think I need to get a better understanding of AJAX.

I maybe back with a few more questions one I have done some reading.

[ryantroop]

As I noted, if you want to do "everything" in javascript, look at building out a nodeJS stack. While it's "all javascript" it just happens to replace the PHP part (you still need a server though)