Fetch dynamically selected data from database using ajax php

Question

My view.php page comes from another page by getting a unique id which is serial. Now in my view.php page I want to show that specified serial no data from charts.php. I have done my code by myself. It is fetching the data. But not that selected serial no. How can I solve this

view.php

<?php 
if(isset($_GET['serial'])){
        $serial = $_GET['serial'];
?>
<html>
<div class="container" id="output"></div>
</html>
<script>
    $(document).ready(function(){
        function getData(){
            $.ajax({
                type: 'POST',
                url: 'charts.php',
                success: function(data){
                    $('#output').html(data);
                }
            });
        }
        getData();
        setInterval(function () {
            getData(); 
        }, 1000);  // it will refresh your data every 1 sec

    });
</script>

charts.php

<?php 
   $sql = mysqli_query($con,"SELECT * FROM criminal WHERE rand = '$serial'");
    while($row = mysqli_fetch_assoc($sql)){
 ?>

Please help.

That's nice @IRGeekSauce, but the OP doesn't have form. He is using a query string variable. — Jay Blanchard, Jul 25 '18 at 21:31
DOH! I saw 'POST' and thought form. My bad. Didn't notice the query string at the top. — DevOpsSauce, Jul 25 '18 at 21:32
@MehediHasanSiam Check my answer and see if that works. I tested it on my own database and it worked. Hopefully I didn't miss anything you needed. — DevOpsSauce, Jul 25 '18 at 21:50

score 2 · Answer 1 · edited Jul 25 '18 at 22:29

2

First of all, close your first if ( you forgot } )
In ajax, after url line, send serial parameter. ( data: {serial: "echo with php the variable"}, )
In charts.php query, get the post value. ( $_POST['serial'] ).

edited Jul 25 '18 at 22:29

cacti5

2,006
2
25
33

answered Jul 25 '18 at 21:31

oalexandru

19
5

Like this: data: ({serial: ""}), – Mehedi Hasan Siam Jul 25 '18 at 21:46
after url: 'charts.php', on the next line data: {serial: ""}, – oalexandru Jul 25 '18 at 21:48
IRGeekSauce`s version, using get to send the serial is good too. – oalexandru Jul 25 '18 at 21:50
@Jay Blanchard there is nothing about a form in what i said. – oalexandru Jul 26 '18 at 18:47
No, but you said "send the serial". My bad, I misunderstood your meaning – Jay Blanchard Jul 26 '18 at 18:52

DevOpsSauce · Accepted Answer · 2018-07-26T13:35:56.130

You want to place your $serial variable within your url. This is known as a query string.

$(function() {
    function getData(){
            $.ajax({
                type: 'POST',
                dataType: 'JSON',
                url: 'charts.php?serial=<?= $serial?>', //<-- RIGHT HERE
                success: function(data){
                    $('#output').html(data);
                }
            });
        }
})

Then you'll GET the data you just sent from ajax. It'll look like your first GET variable. You'll use that variable in your query.

In your php:

<?php 
   $your_variable = $_GET['serial'];
   $sql = mysqli_query($con,"SELECT * FROM criminal WHERE rand = '$your_variable'");
    while($row = mysqli_fetch_assoc($sql)){
        $variable_to_send = $row['serial']; //<--- Whatever your column name is
    }
    echo json_encode($variable_to_send);
 ?>

PDO VERSION As suggested by @JayBlanchard it's highly advisable you do some research on PDO. It is much safer.

I'll give you a PDO example:

$serial = $_GET['serial']; //The variable you're sending over from view.php

$hostname = 'your_hostname';
$username = 'your_username';
$password = 'your_passwd';
$dbname = 'your_db_name';

$pdo = new PDO("mysql:host=$hostname;$dbname=$dbname", $username, $password); //Create a new PDO object
$stmt = $pdo->prepare("SELECT * FROM criminal WHERE rand = :rand"); //prepare the query for execution
$stmt->bindValue(':rand', $serial); //bind your variable to your query
$stmt->execute(); //Run it
$result = $stmt->fetchColumn(); //Get a single column. No while loop.

echo json_encode($result); //Echo it back to your ajax function

I tested this against my own database and it displayed results on my screen with no errors (using my own values of course).

[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)***. Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Jul 26 '18 at 11:17
Yeah. We gotta remember Bobby Tables. He causes a lot of trouble. I should've written my answer in that context. I just wasn't thinking about it. Thanks for mentioning that. — DevOpsSauce, Jul 26 '18 at 12:57

Fetch dynamically selected data from database using ajax php

2 Answers2