CODEWITHSUNDEEP
javascriptmysql
jardane
jardane

Reputation: 471

how to run a MySQL query using JavaScript

I want to run MySQL query's on command without reloading the page. I think JavaScript can do this but i am unsure how. What i want to do is have a form with an return id field and when you fill out the form once with the return id and come back later and use that return id and it fills in in a lot of the content for them to save time.

Upvotes: 8

Views: 75753

Answers (5)

s3c
s3c

Reputation: 1851

Everyone's all about ajax so here's an example on how to send data with vanilla JavaScript and fetch the response back. It will be a nonsense example and will use non-PDO MySQL connection. Backend is PHP, but script.js file is practically the same for NODE.js backend.

script.js


/* mode options are cors, no-cors (only send, no response) and same-origin */
fetch('not-ajax.php', {
  method: 'post',
  mode: 'same-origin',
  credentials: 'same-origin',
  headers: {
    'Content-Type': 'application/json',  // sent request
    'Accept':       'application/json'   // expected data sent back
  },
  body: JSON.stringify({
    /**
     * FYI: For this particular case you would actually prefer to have this snippet
     * just before ending </body> tag so it get's actual width of the window minus
     * the width of the scrollbar if there is one. If you use it in an external
     * script it's the same as using window.innerWidth and window.innerHeight.
     **/
    'screen_width': document.documentElement.clientWidth,
    'screen_height': document.documentElement.clientHeight
  })
})
/**
 * You need this part as well if you send json-encoded data back from backend.
 **/
.then(response => response.json())
.then(data => {
  console.log('Success:', data);
})
.catch((error) => {
  console.error('Error:', error);
});

not-ajax.php

<?php
  include 'connection.php';

  $contentType = isset($_SERVER["CONTENT_TYPE"]) ? trim($_SERVER["CONTENT_TYPE"]) : '';
  if ($contentType === "application/json") {

    // Receive the RAW post data.
    $content = trim(file_get_contents("php://input"));
    // $decoded can be used the same as you would use $_POST with ajax
    $decoded = json_decode($content, true);

    /**
     * Sure enough you can use a SELECT statement, get the data from the database,
     * manipulate it to your heart's content, and then send it back to fetch
     * function in JavaScript for further manipulation and usage.
     **/
    $sql =
      "INSERT INTO used_screen_dimensions (
      ) VALUES ( ?, ? )";
    $statement = $connection->prepare($sql);
    /**
     * first parameter is a string with characters
     *   'i' for integer,
     *   'd' for double,
     *   's' for string,
     *   'b' for blob
     * in the respective order to all other binded parameters
     **/
    $statement->bind_param('ii', $decoded['screen_width'], $decoded['screen_height']);
    $result = $statement->get_result();
    $statement->close();

    /**
     * You may only echo out one thing, but it doesn't have to be a boolean.
     * You can (and should) echo an object with more info including what error
     * it is.
     **/
    if(! is_array($decoded)) {
      echo 0; //If json_decode failed, the JSON is invalid.
      return;
    } else {
      // Send error back to fetch.
      if (!$result) {
        echo 0; // couldn't insert to database
        return;
      }
      echo 1; // Success
    }
  } else {
    echo 0; // Wrong content type
  }

Don't know why I went and typed this answer out by hand. There's bound to be errors.

Upvotes: 1

transparent
transparent

Reputation: 182

Using ajax will do the job. But you'll still need a server-side language for the ajax to call to. Using jquery with ajax will be even quicker!

$.ajax({
  type: "POST",
  url: "someserversidelangfile",
  data: "" //pass data through this variable
}).done(function( msg ) {
  //do so
});

Upvotes: 3

Sean Johnson
Sean Johnson

Reputation: 5607

You'll need to have a backend script do the query - JavaScript, being an entirely client-side language, has no say-so in what goes on with your MySQL server.

What you'll need to do is pass the parameters you want in your query to whatever server-side language you're using via AJAX, and have the script create and process the query as you wish.

DO NOT create the query in javascript and pass it to the server - this is VERY unsafe as it allows anyone to run whatever queries they want.

Upvotes: 3

Travis J
Travis J

Reputation: 82267

You can't query with pure javascript. It has to be done from a hook that is setup on a backend.

This tends to be done with ajax.

Moreover, if querying were available from client side, then everyone could see your connection string.

Upvotes: 7

Josh Mein
Josh Mein

Reputation: 28625

Javascript cannot run MySQL Queries itself; however, you can use ajax to make a call to the server to retrieve the data. I like to use jQuery's ajax() for my ajax needs.

Here is an example of how jquery's ajax() method works:

$.ajax({
  url: "pathToServerFile",
  type: "POST",
  data: yourParams,
  dataType: "json"
});

Upvotes: 8

Related Questions