Pulling info from MySQL using PHP, AJAX and jQuery and show in pop up

Question

I am currently working on a project that is a bit of a learning curve for me at the moment so I thought I'd turn to stack to see if anyone can help me make sense of a hole I appear to be digging myself...

I'm working on a Text browser game that has a very basic vector map (For now), see screenshot in link below to see how it current looks.

Preview of instance and what I'm looking for it to do

My idea is that when you click one of the SVG paths, a pop up shows information based on the id that is in <path id=""

Right now, the code looks like this because it's not dynamic.. propertyDetail and prop2 are the same div, just different content inside them, I want it to be one div, that loads the content based on a mysqli query, finding info from properties based on the id="" in path that's clicked.. So for this example, in the MySQL database the property id will be Path_27 or Path_57

<script>
$(document).ready(function () {
    $("#Path_27").click(function(){
        $(".propertyDetail").fadeToggle();
    });
    $("#closeDetail").click(function(){
        $(".propertyDetail").fadeToggle();
    });
    $("#Path_57").click(function(){
        $("#prop2").fadeToggle();
    });
    $("#closeDetail2").click(function(){
        $("#prop2").fadeToggle();
    });
});
</script>

The code for the PHP, well it's pretty simple right now too.. just a lookup based on the id that will be passed from the UI when clicked...

$query = $dbb->query("SELECT * FROM properties WHERE id = $id LIMIT 1");

Now I know this will be an AJAX request and not just as simple as the code above, but I don't currently know where to even start to achieve what I'm trying to do in a scalable way

UPDATE* I do not want to pull paths from the database.. as in using a while loop or something like that, the paths already are hard coded on the page, I just want to find info about that path that's clicked, and show it using a pop up window

Thank you kindly in advance if someone is willing to take a look. :)

**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/32391315) — Dharman, Sep 16 '22 at 11:53
What you're trying to achieve will never be scalable if your game keeps sending database queries on click events. Especially not if used often. If you need data to be transmitted between a server and one or more clients on a very regular basis, you're better of loading that data into the server's ram. One option would be to use a websocket instead of relying on ajax. — icecub, Sep 16 '22 at 11:57
Add a [jquery ajax](https://api.jquery.com/Jquery.ajax/) request to your `$("#Path_27").click(function(){` and/or `$("#Path_57").click(function(){` function block. Point it at your PHP file on the server handling the mysql query. In that file `echo` the query results and handle that back in your jquery click handlers. — bloodyKnuckles, Sep 16 '22 at 11:57
thank you for the feedback, super appreciated. I will focus on sanitation once I overcome a few of these hurdles that are putting me off the project, so I'm really keen to discover the best practices for this kind of web application, because I get ideas all the time and they are often destroyed when I get to steps like this, so very much looking to learn! — jake oliver, Sep 16 '22 at 12:08
If you really want to learn a bit, you can add me on Discord if you want: icecub#2071 Stack Overflow is not the place to properly learn things as writing half a book to answer a question is out of its scope and usually not something anyone here has the time to do for you. Note: I'm a busy man as well, so don't expect me to spend hours of my time with you, but at least I'm not bound by SO rules and regulations regarding answers on SO itself there. — icecub, Sep 16 '22 at 12:16
@icecub, I really appreciate that.. this is just a side project that I'm using to learn and grow.. helps me with my main line of work too. I'll add you on Discord shortly mate, genuinely nice of you to say it exactly how it is — jake oliver, Sep 16 '22 at 12:20

score 1 · Answer 1 · answered Sep 16 '22 at 13:05

You can do in this way. you can define a function to each path like in this way.

<path id="" data-id="27" onclick="make_request(this);">

In your javascript code do the folllowing.

<script>

function make_request(obj){

var path_id= $(obj).data('id');
  $.ajax({
    url: "test.php",//your path url
    type: "post",
    data: {id:path_id} ,
    success: function (response) {
       // return your response from php in json.the you can do as below
            var data=JSON.parse(response);
           //render your data with html template
            e.g var html="<div>name:"+data.name+"</div>";
           // then append this data into you poup
           $("#prop"+path_id).html(html);
           $("#prop"+path_id).show();


    },
    error: function(jqXHR, textStatus, errorThrown) {
       console.log(textStatus, errorThrown);
    }
});
        
}

bloodyKnuckles · Answer 2 · 2022-09-16T13:04:54.383

Add a jQuery ajax request to your $("#Path_27").click(function(){ and/or $("#Path_57").click(function(){ function block. Point it at your PHP file on the server handling the mysql query. In that file echo the query results and handle that back in your jquery click handlers.

In the jQuery ajax request, designating the return data type as JSON: dataType: "json", then...

In the PHP file, using json_encode() to send a string representation of the property information back to the browser.

And back in your Javascript file, the property information is provided as a JSON object, accessible like: property_info_obj.street_address.

$("#Path_27").click(function(){
    $(".propertyDetail").fadeToggle();

    $.ajax({
        type: "POST",

        url: "get_property_info.php", // PHP file on server
        data: {"path_id": this.id}, // provide path id here

        dataType: "json",

        // handle server response here
        success: function(property_info_obj) {
            // assuming "street_address" is one of the field names
            alert(property_info_obj.street_address); 
        },

        error: function() {
            $(".result").append("Error occured");
        },
    });
});

get_property_info.php

$sql = "SELECT * FROM properties WHERE id = ? LIMIT 1";
$stmt = $conn->prepare($sql); 

$stmt->bind_param("i", $_POST["path_id"]);

$stmt->execute();
$result = $stmt->get_result();

$property_info = array();

while ( $row = $result->fetch_assoc() ) {
    $property_info[] = $row;
}

echo json_encode($property_info);

Pulling info from MySQL using PHP, AJAX and jQuery and show in pop up

2 Answers2