如何从数据库中获取数据并在应用程序中使用AJAX显示它

Question

I have not touched PHP or AJAX that much in about 8 years, so my memory on this is pretty low at the moment.

What I'm doing is fetching data from my database that works great. Then I want to use AJAX to get the data from the PHP file.

My PHP file on my server is connecting to the database and fetching the table "Form". This data is then going to be retrieved by another app trough AJAX.

I have a working PHP file, but how I should order this for the AJAX to fetch it nicely is a big question for me.

The things I have at the moment is:

PHP FILE:

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}


$sql = "SELECT * FROM form";
$result = $conn->query($sql);

if($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $name       = $row['name'];
        $country    = $row['country'];
        $email      = $row['email'];
        $need       = $row['need'];
        $available  = $row['available'];

        echo "name: " . $name . "<br> Country: " . $country . " <br> Email: " . $email . "<br> Need: " . $need . "<br> Available: " . $available;

        $form_data = array();
        array_push($form_data)
    }
} else {
    echo "Null results";
}

$conn->close();


?>

AJAX FILE:

$.ajax({
    url: 'url',
    data: "",

    dataType: 'json',
    success: function(data) {

    }
});

The AJAX file is not complete since I'm still wondering about these PHP results. Right now they are just stored in variables. What is the best way to store these results to get an fetch for AJAX here?

Should I put the results in an array and then push that array into another array? There can be many lines in forms, and I want 1 person with data: name, country etc to be in one array. Or is it stupid to have it in an array?

I hope someone can give a little clue and help me on my way here. Long time since I have been doing PHP and a little bit unclear about the best approach here.

Been searching for a while, but nothing made so much sense to me, so I'm coming here hoping someone can guide the way.

Happy Easter.

Answer 1

Create an array and save all your data, then json_encode to send the data to JavaScript.

<?php

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "db_julekgwa";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}


$sql = "SELECT * FROM form";
$result = $conn->query($sql);

$rows = array();

if($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $name       = $row['name'];
        $country    = $row['country'];
        $email      = $row['email'];
        $need       = $row['need'];
        $available  = $row['available'];

        $rows[] = "{ name: " . $name . ", Country: " . $country . ", Email: " . $email . ", Need: " . $need . ", Available: " . $available . "}";

    }
    echo json_encode($rows);
} else {
    echo json_encode([ "error" => "Null results"]);
}

$conn->close();


?>

In your ajax do the following

$.ajax({
    url: 'file.php',
    type: "GET",
    dataType: 'json',
    success: function(data) {
      data.forEach(item => {
        console.log(item);
      });
    },
    error: function(data) {
      console.log(data);

    }
});