PHP array with MySQL data

Question

I have to send the list of all the users through JSON to javascript. The problem is that I don't know the function to store all the data in one variables.

This is my code:

$sql = "SELECT * FROM ___ WHERE __ = ___ ";
$result = mysqli_query($conn, $sql);

while( /* from here I don't know which function should be inserted */)


$json = json_encode('something');

echo $json;

see this answer https://stackoverflow.com/a/60137289/12232340 — , Feb 09 '20 at 15:18
I am just gonna link this here, because I smell problems: https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — maio290, Feb 09 '20 at 16:16
What about reading the documentation or a tutorial? Read [ask], this was asked 1000000 times before! — Alon Eitan, Feb 09 '20 at 16:17

score 0 · Answer 1 · answered Feb 09 '20 at 16:14

0

Try this

$sql = "SELECT * FROM ___ WHERE __ = ___ ";
$result = mysqli_query($conn, $sql);

$data_array = array();
while($row = mysqli_fetch_assoc($result) ){
   $data_array[] = $row;
}

$json = json_encode($data_array);

echo $json;

answered Feb 09 '20 at 16:14

Dum

1,431
2
9
23

1

Could you please explain why? This is definitely not the recommended way of fetching results via mysqli, so you need to explain why you suggested this. – Dharman Feb 09 '20 at 17:25
I only focused on `send the list of all the users through JSON to javascript`. I thought that he knows how to prevent injections. – Dum Feb 10 '20 at 01:41

score 0 · Accepted Answer · answered Feb 09 '20 at 17:31

It looks like your code is vulnerable to SQL injection. I need to point it out that if you have variable input (which I assume you do in WHERE clause) you need to bind the input via prepared statements.

$sql = "SELECT * FROM yourTable WHERE someColumn = ? ";
$stmt = $conn->prepare($sql);
$stmt->bind_param('s', $someVariable);
$stmt->execute();
$result = $stmt->get_result();

// Now fetch it into an array
$arrayOfRows = $result->fetch_all(MYSQLI_ASSOC);

// echo out JSON
echo json_encode($arrayOfRows);

There is no need for any loop, especially not while loop. Just fetch all the records into a multidimensional array.

PHP array with MySQL data

2 Answers2