Jump to content

JSON Encode MySql Query Results


Chalupabatman

Recommended Posts

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}

Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

@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?

Link to comment
Share on other sites

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;   
	}
	

Link to comment
Share on other sites

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 by Barand
  • Like 1
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.