JQuery Fetch data from MySQL and store into Variable

Question

I am continuing to work on my multi stage bootstrap form, and I have hit a roadblock trying to pull info from my DB.

The main page is PHP and is named quote_tool.php

I have the following functional requirements:

The data must come from the MySQL database.
The user should only receive data that they requested (i.e. a row from the db with info about user license should only be grabbed if the user checked a radio button to include user licenses on the form).
The information needs to be called from the DB without refreshing/reloading the page.

Currently I have a table in my DB with the following columns:

There are 3 different products in that table right now. The user can select a radio to say they want to include endpoints, and then there are 3 check boxes to allow the user to input a quantity for which endpoint(s) they want to include.

The input field looks like this:

<label for="device-9102" class="form-partner-label"><input type="checkbox" class="quote-chkbox" id="9102-chk"> 9102 IP Phone</label>
<input type="text" name="9102-quantity" class="form-endpoint-qty form-control" id="form-partner-9102" readonly value="0">

When the user checks the box and input a value this value is dynamically updated on the summary page as well in the following field:

<input type="text" readonly name="sum-9102-qty" class="summary-field sum-qty" id="sum-9102-qty">

There is also 2 other fields on the summary page regarding this product.

MSRP
Part Number

MSRP is a hidden field that will be used for additional calculations, but Part Number is visible on the summary page.

When the user inputs the value for the endpoint quantity I need to call the DB and pull the MSRP and Part Number from the refEndpoints table.

I am currently building a function to call the DB when the user hits the "Next" button on the form, and that looks like this:

//Call DB to fetch part number and msrp of 9102
    $('#form-partner-9102').change(function()){ 
        var quantity_9102 = $('#form-partner-9102').val();
        if(quantity_9102 !== 0) {

        }
    });

This is the point that I am stuck at. I am not sure how to call the DB and place the values of the part number and the MSRP in the correct input fields on the summary page.

The technology you're looking for is called AJAX. At a high level, you would create a server-side "page" (PHP file which emits JSON data instead of an HTML page) which accepts the inputs it needs, fetches data from the DB, and writes the output back. Your client-side JavaScript code would make an AJAX request to that "page's" URL, supplying the values as POST or GET parameters as you see fit, and receive the response. The client-side code would then do whatever it needs to do on the page with that response. JavaScript won't talk to the DB, PHP would broker that interaction. — David, Jun 28 '17 at 18:09

score 0 · Answer 1 · edited Jun 28 '17 at 20:48

0

jQuery runs on the client side so it cannot connect to MySQL directly, however your question is tagged php, which runs on the server side and thus can connect to your database. First you will need to setup a PHP file that can respond to HTTP POST requests and return JSON. Here is a great answer that shows you how to do this: Returning JSON from PHP to JavaScript?

Once set up (and you will need to workout what parameters this PHP file takes in and how it converts this into a query so that it can respond) you can now setup some simple JavaScript to call this PHP file (lets call it query.php). Code that does this might look like this:

$.post('/query.php', {quantity: $('#form-partner-9102').val()}, function(resp) {
  $('#PartNumber').html(resp.PartNumber);
});

Some important things to keep in mind are to always be sure to use prepared statements when taking user input and turning it into a query (don't just build a SELECT statement by joining strings). Also be sure to look at your event binding, you can probably write one generic handler for your inputs that takes the partner ID as a data-* attribute making your code smaller and easier to maintain.

edited Jun 28 '17 at 20:48

Sᴀᴍ Onᴇᴌᴀ

8,218
8
36
58

answered Jun 28 '17 at 18:14

Jason Sperske

29,816
8
73
124

Is this piece of code you provided me with designed to grab just the part number of the item in the DB? (assuming all of the other work you mentioned above is completed). – K. Carskadon Jun 28 '17 at 18:17
It's just to show how you can `POST` to a `php` without refreshing a page and even use the servers response to update your page. – Jason Sperske Jun 28 '17 at 18:19
This has very quickly gone way over my head. – K. Carskadon Jun 28 '17 at 18:22
Then lets take a few steps back, can you make a PHP page that connects to your database, reads a parameter from [`$_POST`](http://php.net/manual/en/reserved.variables.post.php), builds a query, and prints some JSON from the record set? – Jason Sperske Jun 28 '17 at 18:26
My registration page connects to the DB to put a new user's info into it, and my login page connects to it to verify the user and create a session. I have not, however, worked with JSON. – K. Carskadon Jun 28 '17 at 18:30
That's a great starting point, I might start by copying that and changing the query/output, look at the linked answer about returning JSON. Once you have the PHP code then you can work on calling it from jQuery, here is the documentation on the `$.post()` function https://api.jquery.com/jquery.post/ – Jason Sperske Jun 28 '17 at 18:33
JSON is just a output format that is really easy to work with in JavaScript (it's also to write by hand making it ideal for debugging). PHP includes a simple helper function that can convert a PHP variable (like an Associative Array) into JSON for you – Jason Sperske Jun 28 '17 at 18:34

JQuery Fetch data from MySQL and store into Variable

1 Answers1