Using MySQL results in javascript

Question

I have a web application where I need to get values from a MySQL database.

The series of event is as follows:

PHP code creates HTML page (works fine)
Click a button on the page, updating a cookie (works fine)
Use cookie in a MySQL query (This does not work)
Get a record from the above MySQL query result and pass to HTML page with jQuery

The problem with bullet 3 is that the MySQL query is only run when I load the page (of course). But I need a method to run a query, based on user input (stored as the cookie), without reloading the PHP script.

How can this be done?

My engineering c-coding brain has a really hard time wrapping this ajax thing. Here is the code so far, still not working:

The popup(HTML) I want to update with new strings when a button on the same page, is clicked:

<div id="popup" class="popup" data-popup="popup-1">
    <div class="popup-inner">
        <h2 id="popup-headline"></h2> //Headline, created from a cookie. Could be "Geography"

        <div id="dialog"></div> //From Will's suggestion

        <p id="question"></p> //String 1 from online MySQL DB goes here "A question in Geography"

        <p id="answer"></p> //String 2 from online MySQL DB goes here "The answer to the question"

        <p class="popup-small-button"><a data-popup-close="popup-1" href="#"><br>Close</a></p> // Hides the popup

        <a class="popup-close" data-popup-close="popup-1" href="#">x</a>

    </div>

</div>

Then i have my file with custom functions. It executes whenever the popup is shown:

<script>
jQuery(function() {

jQuery('[data-popup-open]').on('click', function(e)  {

        function myfunction(myparams) {
            // your logic here: testing myparams for valid submission, etc.
            alert("hey");
            jQuery.ajax({
                type: 'post',
                url: 'server.php',
                data: {
                    my_var1: 'question',
                    my_var2: 'answer'
                },
                success: function(data) {
                data = JSON.parse(data);
                jQuery('#question').html(data["question"]);
                jQuery('#answer').html(data["answer"]);
                },
                error: function(jqxhr, status, exception) {
                alert('Exception:', exception);
                }
                });

        }


    });
});

</script>

My server.php file contains now this:

<?php

    require("db.php");

    if(isset($_POST['my_var1']) && isset($_POST['my_var2'])) {
        myfunction($_POST['my_var1'], $_POST['my_var2']);
    }

?>

And my db.php contains this:

<?php

    function myfunction($var1, $var2) {
        $db = mysqli_connect('MyOnlineSQLPath','username','password','database1_db_dk');


    $stmt = $db->prepare("SELECT question, answer FROM t_da_questions WHERE category_id=?;");
    $stmt->bind_param("s", $_COOKIE('category'));
    $stmt->execute();

    $retval = false;

    if($result->num_rows > 0) {
        $row = $result->fetch_assoc();
        if(!is_null($row['question']) && !is_null($row['answer'])) {
            $retval = new stdClass();
            $retval->question = row['question'];
            $retval->answer = row['answer'];
        }
    }

    mysqli_close($db);
    return $retval;

}

?>

What I need, is the "question" and "answer" from the SELECT query.

TL;DR I need question and answer strings to go into <p id="question"></p> and <p id="answer"></p> in the HTML, both without refreshing the page. The getCookie('category') is a cookie stored locally - It contains the last chosen category for a question. The function getCookie('category') returns an integer.

Let me know if you need any more info on this.

This seems up to date enough (but you still have to understand, what you are doing): https://stackoverflow.com/questions/5298401/basic-php-and-ajax — Rauli Rajande, Jan 28 '18 at 19:40
Have you tried any AJAX? Your case can be done with quite simple AJAX application. Find a simple example, copy it and try, and then apply it to your application. — ild flue, Jan 28 '18 at 19:40
Have you installed a webserver on your local machine or are you using the `file://` schema (Instead of `http://`)? — Alon Eitan, Jan 28 '18 at 19:47
I have tried 20 different pieces of code, so it might be useless. So im open to suggestions as below :) — Emil Olsen, Jan 28 '18 at 19:57
@SergioTulentsev Did you read my elaboration below? Or did you just rapidly downvote me? — Emil Olsen, Jan 28 '18 at 20:22
@EmilOlsen: yes, it says the same thing. "I did something, but it didn't do what I want. I'm not telling you what I did, what I wanted or what the thing did instead." — Sergio Tulentsev, Jan 28 '18 at 20:24
I dont think it matters what i did, what i seek is options of what to do.. — Emil Olsen, Jan 28 '18 at 20:25
Oh it matters what you DID... so we know if what you did SHOULD have worked, but you have some other issue conflicting with it... or know that you didn't even DO the first thing you should. No one around here likes guessing or reading minds ;) And don't argue with others asking for clarity... is akin to just giving them the middle finger. — IncredibleHat, Jan 28 '18 at 20:27
@EmilOlsen: you at least don't interpolate the cookie correctly. I don't know php, but it should be something like this, I think: `category_id=${getCookie('category')}` — Sergio Tulentsev, Jan 29 '18 at 06:15
Hmm, i am not familiar with this notation on getting data from database, that might be an issue. I changed it to the php way of getting cookie. BTW, debugging seems really difficult compared to other languages. What setup are you using? I just run brackets and filezille, but theres no debugging options whatsoever... — Emil Olsen, Jan 29 '18 at 06:45
@EmilOlsen "this notation on getting data from database" - no, it's not for getting data from database, it's for building your query string. Which is still the wrong way to go about this (should have used prepared statements instead. But you'll get there) — Sergio Tulentsev, Jan 29 '18 at 10:06
@EmilOlsen: jfyi, if you want to make sure that someone sees your message, you `@mention` them. — Sergio Tulentsev, Jan 29 '18 at 10:07

Will Jobs · Accepted Answer · 2018-01-28T23:37:42.447

1

Here is some template AJAX that may help you out. I used this in another project. This won't require a page refresh. You will have to include the code to send your cookie's data in the 'data' section.

<!DOCTYPE html>
<html lang="en">
    <head>
        <script src="https://code.jquery.com/jquery-3.3.1.min.js" integrity="sha256-FgpCb/KJQlLNfOu91ta32o/NMZxltwRo8QtmkMRdAu8=" crossorigin="anonymous"></script>
        <meta charset="utf-8">
    </head>

    <body>
        // your HTML here

        <script>
            <div id="dialog"></div>

            function myfunction(myparams) {
                // your logic here: testing myparams for valid submission, etc.

                $.ajax({
                    type: 'post',
                    url: 'myphpfile.php',
                    data: {
                        my_var1: 'myval',
                        my_var2: 'myval2'
                    },
                    success: function(data) {
                        $("#dialog").html("<span>Success!</span>");
                        $("#dialog").fadeIn(400).delay(800).fadeOut(400);
                    }
                });
            }
        </script>
    </body>
</html>

Then in the file 'myphpfile.php', you'll have code like the following:

<?php
    require("../mycodebase.php");

    if(isset($_POST['my_var1']) && isset($_POST['my_var2'])) {
        myfunction($_POST['my_var1'], $_POST['my_var2']);
    }
?>

Finally, in mycodebase.php (which is stored in a place inaccessible to the public/world), you'll have a function that actually runs your query and returns your result:

function myfunction($var1, $var2) {
    $db = mysqli_connect('localhost','myuser','mypass','dbname');

    $stmt = $db->prepare("UPDATE mytbl SET col1=? WHERE col2=?;");
    $stmt->bind_param("ss", $var1, $var2);
    $stmt->execute();

    $result = (($db->affected_rows) > 0);
    mysqli_close($db);
    return $result;
}

UPDATE

That function above is to run an UPDATE query, so the result returned just indicates whether you successfully updated your data or not. If you want to return an actual result, you have to extract the result from the query as follows:

function myfunction($cat) {
    $db = mysqli_connect('localhost','myuser','mypass','dbname');

    $stmt = $db->prepare("SELECT question, answer FROM t_da_questions WHERE category_id=?;");
    $stmt->bind_param("s", $cat);
    $stmt->execute();

    $retval = false;

    if($result->num_rows > 0) {
        $row = $result->fetch_assoc();
        if(!is_null($row['question']) && !is_null($row['answer'])) {
            $retval = new stdClass();
            $retval->question = row['question'];
            $retval->answer = row['answer'];
        }
    }

    mysqli_close($db);
    return $retval;
}

Then your server.php file will look like:

<?php

    require("db.php");

    if(isset($_COOKIE['category'])) {
        json_encode(myfunction($_COOKIE['category']));
    }

?>

Here's the JS:

jQuery('[data-popup-open]').on('click', function(e)  {
    function myfunction(myparams) {
        // your logic here: testing myparams for valid submission, etc.
        alert("hey");
        jQuery.ajax({
            type: 'post',
            url: 'server.php',
            // data section not needed (I think), getting it from the cookie
            success: function(data) {
                data = JSON.parse(data);
                jQuery('#question').html(data["question"]);
                jQuery('#answer').html(data["answer"]);
            }
        });
    }
});

This is untested -- I may have gotten an argument wrong, but this is at least very close if it's not already there.

edited Jan 28 '18 at 23:37

answered Jan 28 '18 at 19:50

Will Jobs

362
1
11

1

Aren't cookies sent automatically? That's their whole raison d'être :) – Sergio Tulentsev Jan 28 '18 at 19:51
Cookies are stored in the user's browser, and the site has access to them when the page loads. However, it sounds like the user is talking about using the cookie data in their own database, which wouldn't happen automatically. – Will Jobs Jan 28 '18 at 19:53
I mean, the cookies will be sent with the ajax request without any effort from user's side (same as a "regular" page load). Then he's free to use (or ignore) them on server side. – Sergio Tulentsev Jan 28 '18 at 19:54
All of that works fine - The problem occurs when i want to use it in a PHP MySQL Database connection - Since the PHP is server side, i dont know the way around it... – Emil Olsen Jan 28 '18 at 20:00
Ah, I follow. I'll update my answer above and see if that helps. – Will Jobs Jan 28 '18 at 20:04
1

Also, if a cookie is created as httponly, you won't have access to it within javascript to use explicitly as data my_var1 etc ... (but it will send them in the ajax request headers and be available with `$_COOKIE['mycookie1']` on the php side). So passing them as POST args is not needed here (unless its a cross-domain situation, but thats beyond the question now lol). – IncredibleHat Jan 28 '18 at 20:18
@WillJobs I tried to incorporate your code. I know that the var names etc should be changed, but i kept them in the code, in hope that its easier for you to help me get what i need. Can you take a look at the original question? Its updated. – Emil Olsen Jan 28 '18 at 21:30
You're like 95% of the way there - great job! I'll update the code in my answer above. – Will Jobs Jan 28 '18 at 21:34
Thanks! It looks like something quite close to what i've tried before plus some more! In the ajax, is this correct? data: {my_var1: 'question',my_var2: 'answer'} ? – Emil Olsen Jan 28 '18 at 23:04
I've updated my question with your latest codes. There still are problems. The ajax part has some error, because if i put an alert("test"); before the ajax, it is not displayed. However, if i comment out the ajax, it is displayed (the "test" alert). – Emil Olsen Jan 28 '18 at 23:10
OK, I updated my code. You didn't need the my_var1 and my_var2, those were just examples of how you can explicitly send data in through PHP. As Sergio mentions above, the cookie data should be sent automatically, so I removed the data section from the ajax, and reference $_COOKIE in the PHP. Try it out and let me know if it works. If it doesn't, try putting an alert within the 'success' function (alert(data)) and let me know what that gives you. – Will Jobs Jan 28 '18 at 23:42
Thx, changes makes sense. However, it seems there is a bug in within the myfunction(myparams) in the js. Is it correct that you have defined two functions as "myfunction" ? – Emil Olsen Jan 29 '18 at 08:13
Okay, i found out that the url "server.php" was insufficient. It now is the direct web url and ajax does not throw me an error now. However, it does get an empty "data" . Are you sure the server.php file is correct up there? How can i see, what the ajax is taking from it? (Is it taking or getting?) – Emil Olsen Jan 29 '18 at 09:11
Yes, I probably should have named them something distinguishable, like "my_js_function" and "my_php_function". They are different functions. For server.php, is this a file on your server? If it's cross-domain (i.e., from another site) you might have issues. If it's on your server, you shouldn't need the full web url (https://....), only the relative path (e.g., "../server.php" if it's one folder up from the main HTML/PHP file. How are you setting the cookie value? Are you sure the success function is running? If not, put an alert in there to make sure. – Will Jobs Jan 29 '18 at 17:53
@WillJobs Its in the same server, same folder even. I just used the console in chrome and it searched for it in the wrong place. However, it seems to find it now. But "data" is still completely empty in the ajax script. :( – Emil Olsen Jan 29 '18 at 17:55
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/164117/discussion-between-will-jobs-and-emil-olsen). – Will Jobs Jan 29 '18 at 17:56

Using MySQL results in javascript

1 Answers1