JSON Encode MySql Query Results

[Chalupabatman]

Hello - I am connecting to MySql and running a query.  If I use a foreach loop, I can iterate over the results and have them print to screen.  However, when I try the below everything is null!

 

How can I add the results of my MySql query to a php array?

 

	<?php
$con=mysqli_connect("site", "user", "psasswr=ord", "db");
	if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
	$sql="SELECT * FROM test LIMIT 10";
$result = mysqli_query($con,$sql);
	echo json_encode($result);
	mysqli_free_result($result);
	mysqli_close($con);
?>
	

 

but what this prints is:

{"current_field":null,"field_count":null,"lengths":null,"num_rows":null,"type":null}

[chhorn]

You have to read the data before encoding it.

[Barand]

You need

https://www.php.net/manual/en/mysqli-result.fetch-all.php

to fetch the data

EDIT: Note that fetch_all() is not available in all inplementations of mysqli so you may have to loop through the  resuts using fetch() and store the rows in an array (which is one of the reasons for using PDO instead of mysqli.)

Edited July 29, 2019 by Barand

[Chalupabatman]

@Barand - so if I am following the tutorial you linked to, I need to do 

	foreach($services as $service){
    echo $service; //work properly, cause it implements Iterator  
}
	

in my code.  Now my question is, how would I assign all of the returned results (fields and rows) to an array in this foreach loop?

[Barand]

That would depend on how $services is being created. You failed to show that bit. It isn't in your original post and appears out of nowhere in this one..

[Chalupabatman]

4 minutes ago, Barand said:

That would depend on how $services is being created. You failed to show that bit. It isn't in your original post and appears out of nowhere in this one..

Sorry that was a direct pull from the link you provided.  My variable is $result so it would be something like

	foreach($result as $res){    
	    echo $res;   
	}
	

[Barand]

The method I was suggesting with that link to the php manual was

$sql="SELECT * FROM test LIMIT 10";
$result = mysqli_query($con,$sql);
$data = $result->fetch_all(MYSQLI_ASSOC);
echo json_encode($data);

However you can traverse the $result object as you suggest

$sql="SELECT * FROM test LIMIT 10";
$result = mysqli_query($con,$sql);
$data = [];
foreach ($result as $row) {
    $data[] = $row;
}
echo json_encode($data);

 

Edited July 29, 2019 by Barand

[Chalupabatman]

@Barand - that worked perfectly.  Thank you so much!