CODEWITHSUNDEEP
javascriptphphtmlmysql
Evan Lalo
Evan Lalo

Reputation: 1273

Pass mysql table values to Javascript via PHP

I am trying to take all of the rows MYSQL table and ultimately have them end up in an interactive HTML table. I currently have no problem inserting values into my table using a $.post function, I just cannot return them, at least, not all of them.

Here is my JS code:

function load() {   

            $.post(
            "Returnsmedb.php",
            function (response) {
                var firstname = response.fname;
                var lastname = response.lname;
                var username = response.uname;
                var email = response.email;
                var password = response.password;

                console.log(response);
            }, 'JSON'
        );  
    }

PHP:

<?php
header('Content-type: application/json');

$servername = "localhost";
$username = "SME";
$password = "mypass";
$db = "p3";

// Create connection
$conn =  new mysqli($servername, $username, $password, $db);

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


$sql = "SELECT * FROM pendingusers";
$result = $conn->query($sql);
$response = array();

if ($result->num_rows > 0) {

 while($row = $result->fetch_array()) {
    // $response[] = $row[];
    $response['fname'] = $row["fname"];
    $response['lname'] = $row["lname"];
    $response['uname'] = $row["uname"];
    $response['email'] = $row["email"];

}
    echo json_encode($response);
} else {
    echo "0 results";
}
$conn->close();
?>

I am thinking the easiest thing to do would be to store each row in an array, but I am not sure. It currently returns my $response array, but all of the values are null, except for 'email', which has the correct value, but it is from the last row of my DB table. Also, it only returns one row.

Any help / guidance would be much appreciated.

Thanks!

Upvotes: 1

Views: 1561

Answers (1)

BeetleJuice
BeetleJuice

Reputation: 40886

Your problem is that in this loop:

 while($row = $result->fetch_array()) {
    // $response[] = $row[];
    $response['fname'] = $row["fname"];
    $response['lname'] = $row["lname"];
    $response['uname'] = $row["uname"];
    $response['email'] = $row["email"];    
}

You keep overwriting the same keys in $response, so at the end of the loop, only the values from the last DB row will be found. you could fix this by doing

 while($row = $result->fetch_array()) $response[] = $row;

However if you'll be capturing all results, there is no need to loop through them. Just use fetch_all and you will get the full table right away.

 $response = $result->fetch_all(MYSQLI_ASSOC)

As a sidenote, I would be really careful about sending everything unfiltered from your DB to the browser like this. Consider at least adding a LIMIT clause to your query so that if your table has thousands of rows, everything doesn't get sent:

 $sql = "SELECT * FROM pendingusers LIMIT 50";

Finally, be explicit about the columns you want, so that you don't leak unwanted information if your DB gets new sensitive columns in the future

 $cols = 'fname, lname, uname, email';
 $sql = "SELECT $cols FROM pendingusers LIMIT 50";

Upvotes: 1

Related Questions