Making MySQL queries using javascript function.

Question

I'm trying to create PHP function that I will be able to use in javascript code.

<!DOCTYPE html>
<?php
include 'pdo_connect.php';
function pleaseWork($query) {
    return dataQuery($query)->fetchAll();
}
function makeQuery($query)
{
    $work = pleaseWork($query);
    $json = json_encode($work);
}
?>
<html>

<head>
<script src="//ajax.googleapis.com/ajax/libs/jquery/2.0.0/jquery.min.js"></script>
<title>Admin check</title>
<meta charset="UTF-8">
</head>

<body>
<script type='text/javascript'> 
<?php makeQuery("SELECT * FROM `grupy`");?>

var jArray = <?= $json ?>;
console.log(jArray);
</script>

Of course it doesn't work because code in JS block doesn't know anything about variables from beginning of file. How do I do that? I never used AJAX or anything so I don't know what to do.

I think you're mixing server-side and client-side code. You don't want to have any queries or connection info in your client side code (like you have here). If you want to get data via Javascript, then yes, you're right, you should use some sort of AJAX call to a web service. That web service would then house your database call. I definitely recommend doing some research on AJAX and web services. — the-nick-wilson, Feb 16 '16 at 18:25
I arrived here looking for something like an ajax-call inside an ajax-call, which lead me to find this question https://stackoverflow.com/a/10089580/10470002 . Just in case anybody in the future arrives here looking if that can be done. — anna, Feb 03 '19 at 19:27

score 3 · Accepted Answer · answered Feb 16 '16 at 21:23

You need to learn about OOP (Object Oriented Programming) in more detail. Perhaps search for Dependency Injection, or Exceptions. Also, you need to create a PHP script that accepts your ajax requests and calls the necessary functions or methods which should all be separated into their own files and in classes. This will separate the different data layers from each other (i.e. presentation, business logic, data). You should also invest some time in learning what MVC(Model View Controller) is about. This will help you understand the importance of the separation.

For now, I will show you a quick solution to your problem. Lets imagine your file structure is all in the same directory as such:

|--- ajax_requests.php
|--- Database.php
|--- Functions.php
|--- index.php

index.php is where your HTML /jQuery is located:

<!DOCTYPE html>
<html>

<head>
    <script src="//ajax.googleapis.com/ajax/libs/jquery/2.0.0/jquery.min.js"></script>
    <title>Admin check</title>
    <meta charset="UTF-8">
</head>
<body>
<script type='text/javascript'>
    $.ajax({
        type: "post",
        url: "ajax_requests.php",
        data: {request: "get_grupy_json"},
        success: function(result){
            console.log(result);
        }
    });
</script>

Notice how we are using jQuery to make our Ajax request. We are making a post request to the file ajax_requests.php sending the get_grupy_json as our request parameter. No SQL queries should be present on your front-end views.

ajax_requests.php receives the request, gets the Database object and sends it to the Functions object, then it checks that the request exists as a method of the Functions class, if it does, it runs the method and turns the result as json (remember to add error checking on your own):

<?php
if (!empty($_POST)) {

    $method = $_POST['request'];

    include 'Database.php';
    include "Functions.php";

    $db = new Database();
    $functions = new Functions($db);

    if (method_exists($functions, $method)) {
        $data = $functions->$method();
        header('Content-Type: application/json');
        echo json_encode($data);
    }
}

Functions.php

class Functions
{
    private $db;

    public function __construct(Database $db)
    {
        $this->db = $db;
    }

    public function get_grupy_json()
    {
        $query = "SELECT * FROM `grupy`";
        $result = $this->db->dataQuery($query);
        return $result->fetchAll();
    }
}

Database.php

class Database
{
    private $conn = null;

    public function __construct()
    {
        try {
            $username = "root";
            $password = "root";
            $servername = "localhost";
            $dbname = "test";
            $this->conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);

            $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        } catch (PDOException $e) {
            trigger_error("Error: " . $e->getMessage());
        }
    }

    public function dataQuery($query, $params = array())
    {
        try {
            $stmt = $this->conn->prepare($query);
            $stmt->execute($params);
            return $stmt;

        } catch (PDOException $e) {
            trigger_error("Error: " . $e->getMessage());
        };
    }


}

This is a rough mockup of what i would do. I hope you get the idea of how it is all separated so that you can easily add features to your application as it grows.

If I want to make the function that query comes from argument I should do ` public function get_grupy_json($query) { $result = $this->db->dataQuery($query); return $result->fetchAll(); }` but where do I call get_grupy_json($query)? — stevenhawkingsbiggestfan, Feb 17 '16 at 23:14
you make those calls in your JSON request, but you do not pass the query. That is not recommended as your app will be open to SQL injection. Build the queries needed statically in your PHP functions, then call those function through AJAX. — CodeGodie, Feb 18 '16 at 03:04
So if I want make for example 5 different queries then I have to write 5 functions? In file functions.php — stevenhawkingsbiggestfan, Feb 18 '16 at 12:45
Yes. SQL queries should never be placed in the presentation layer. — CodeGodie, Feb 18 '16 at 13:52
Unfortunally your code gives this error: `Fatal error: Class 'Database' not found in ajax_requests.php on line 9
` — stevenhawkingsbiggestfan, Feb 18 '16 at 15:20
And maybe it doesn't matter but when I saved Database.php it didn't change the color on php markup in Notepad++, maybe thats why, something was not properly saved — stevenhawkingsbiggestfan, Feb 18 '16 at 15:21
theres no way to do that through here. Its ok. Do you use Notepad++ to code? Why not use a better IDE like Netbeans? — CodeGodie, Feb 18 '16 at 16:25
And one more quick question that doesn't deserve new thread: Now I'm trying to convert response from object to array and it's going good but it returns array with whole object, not only with json respond. Is there any way to convert that? — stevenhawkingsbiggestfan, Feb 18 '16 at 17:19
im not exactly sure. Maybe provide an example so that I have a better idea what you mean. — CodeGodie, Feb 18 '16 at 19:02
I want this code: http://pastebin.com/B609wYz9 To give me array made of respond from database, not something like that: http://s29.postimg.org/entvwhoev/screenshot_1777.png — stevenhawkingsbiggestfan, Feb 18 '16 at 19:23
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/103891/discussion-between-codegodie-and-stevenhawkingsbiggestfan). — CodeGodie, Feb 18 '16 at 19:36
You need to create another question for this. This one is getting too long. Do that, and I will continue to help out. I'll be back later. — CodeGodie, Feb 21 '16 at 15:41

score 0 · Answer 2 · answered Feb 16 '16 at 18:31

AJAX does not work like this. Basically, product of PHP is a HTML page (with JS), that is rendered upon request in the browser. When user performs an action, that should result in displaying data retrieved by AJAX, the browser makes another request, possibly to different PHP script. Result of this request is not displayed to the user, as while performing the first request, rather it is passed to a function in JS of the currently displayed page (that is what makes it AJAX). This function can then process the data in any way it wants.

Have a look at this: http://www.w3schools.com/jquery/jquery_ajax_intro.asp.

Making MySQL queries using javascript function.

2 Answers2

Linked