Run a SQL query in JS

Question

I want to run a SQL query in a .JS file. I tried to add some PHP codes to it but it didn't work of course.

I want to run the SQL query: SELECT price FROM list WHERE q=1. I want to show the price in the chart. Does anyone know how I can run this SQL query in the .JS file so I can get the item price from the database?

I am using the following JavaScript code:

 /* Morris.js Charts */
  // Sales chart
  var area = new Morris.Area({
    element: 'revenue-chart',
    resize: true,
    data: [
      {y: '2016 Q1', item1: 5000, item2: 4460},
      {y: '2016 Q2', item1: 8432, item2: 5713}
    ],
    xkey: 'y',
    ykeys: ['item1', 'item2'],
    labels: ['Item 1', 'Item 2'],
    lineColors: ['#a0d0e0', '#3c8dbc'],
    hideHover: 'auto'
  });
  var line = new Morris.Line({
    element: 'line-chart',
    resize: true,
    data: [
      {y: '2015 Q4', item1: 0},
      {y: '2016 Q1', item1: 5000},
      {y: '2016 Q2', item1: 8432}
    ],
    xkey: 'y',
    ykeys: ['item1'],
    labels: ['Item 1'],
    lineColors: ['#efefef'],
    lineWidth: 2,
    hideHover: 'auto',
    gridTextColor: "#fff",
    gridStrokeWidth: 0.4,
    pointSize: 4,
    pointStrokeColors: ["#efefef"],
    gridLineColor: "#efefef",
    gridTextFamily: "Open Sans",
    gridTextSize: 10
  });

Answer 1

You can't directly connect to mysql with javascript but you can echo out your php variable in javascript like this..

 var area = new Morris.Area({
element: 'revenue-chart',
resize: true,
data: [
  {y: '2016 Q1', item1: <?php echo $price ?>, item2: <?php echo $price ?>},
  {y: '2016 Q2', item1: 8432, item2: 5713}
]

you can cover the whole javascript inside php script.

this will replace like this on executing.

{y: '2016 Q1', item1: 12054, item2: 65242},

Answer 2

You can't execute a query using javascript because javascript can't connect directly with your database, but you can use AJAX. With this technology you'll be able to send a request to a PHP (or other server side language) page where resides the code that can execute a query to your db and get back the result of this query.

You can find some information about client server here:

https://spin.atomicobject.com/2015/04/06/web-app-client-side-server-side/

http://www.codeconquest.com/website/client-side-vs-server-side/

And you can find a lot of useful information about ajax here:

https://developer.mozilla.org/en-US/docs/AJAX/Getting_Started

Take a look even to jQuery for manage Ajax call: http://api.jquery.com/jquery.ajax/

EDIT You can use server side javascript to access your database, here you can find a good article about MySql Cluster. In a nutshell:

The MySQL Cluster JavaScript Driver for Node.js is just what it sounds like it is – it’s a connector that can be called directly from your JavaScript code to read and write your data. As it accesses the data nodes directly, there is no extra latency from passing through a MySQL Server and need to convert from JavaScript code//objects into SQL operations. If for some reason, you’d prefer it to pass through a MySQL Server (for example if you’re storing tables in InnoDB) then that can be configured.

Answer 3

Execute your query out side the JS and pass the prices in the javascript .

Example :

$res_price = $conn -> query("SELECT price FROM list WHERE q=1");
$row_price = mysqli_fetch_assoc($res_price);

echo $row_price['price'];

Now in Javascript add Like,

<?php echo $row_price['price']; ?>

If you want add more than one prices then you can manage it through array also make array of price and pass it in the js.

Answer 4

You cannot connect to Mysql directly from client side JS, but only from server side JS, such as node. It can just like PHP connect using PDO or mysqli extensions, run your query and then pass the information to client side JS.