How to Store MySQL Table in a PHP Array

Question

I am trying to store all values in a table called locations from MySQL database which looks like

enter image description here

into a PHP Array called $locations = array(); .I need to store the data in a format(Associated array or Regular) which I can convert them to a JSON file by using the php's json_encode() function. The final output must looks like

{
    "markers": [{
        "id": 1,
        "type": "shelter",
        "lat": 55.6295639,
        "long": 12.6392556,
        "latlong": "55.6295639,12.6392556"
    }, {
        "id": 2,
        "type": "shelter",
        "lat": 49.6125639,
        "long": 12.6392556,
        "latlong": "55.6295639,12.6392556"
    }, {
        "id": 3,
        "type": "shelter",
        "lat": 56.6786339,
        "long": 11.6392556,
        "latlong": "55.6295639,12.6392556"
    }, {
        "id": 4,
        "type": "shelter",
        "lat": 51.6295639,
        "long": 13.6392556,
        "latlong": "55.6295639,12.6392556"
    }, ]
}

I already tried this code but not sure I am doing right? or how to export it to json?

$result = mysql_query('select * from locations');

$locations = array();
while($r = mysql_fetch_array($result) {
    $row = array();
    foreach($r as $k=>$v) {
         $row[$k] = $v;
    }
    array_push($locations,$row);
    unset($row);
}

1. var_dump($locations) 2. json_encode 3. you don't need unset there 4. You don't need a loop - just $locations[] = $r; — zerkms
– zerkms, Commented Dec 15, 2013 at 22:44
Please no longer use mysql_* as it is deprecated and will be removed from PHP soon. Please switch over to using the mysqli set of functions/methods — Zarathuztra
– Zarathuztra, Commented Dec 15, 2013 at 22:45
@Zarazthuztra: what if there is 10Mb of legacy code that uses it already and that will never be upgraded to a never php version ever (unless it's rewritten)? — zerkms
– zerkms, Commented Dec 15, 2013 at 22:52
@Zarazthuztra: right. So your advice should contain an explicit "for new projects" otherwise it looks not helpful. — zerkms
– zerkms, Commented Dec 15, 2013 at 22:53

Floris · Accepted Answer · 2013-12-15 22:45:56Z

2

I suppose you could just:

$result = mysql_query('select * from locations');
$locations = array();

while($row = mysql_fetch_array($result) {
    array_push($locations, $row);
} 

//echo json_encode($locations)

answered Dec 15, 2013 at 22:45

Floris

6534 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Behseini Over a year ago

Hi Floris, Thanks for your hint, is there any way I can store the json file on the server? I mean by ecohing the data I am not able to grab them by Ajax ,so how I can save the result on a file?

Zarathuztra Over a year ago

yup file_put_contents('locations.js',json_encode($locations));

Floris Over a year ago

PHP has file_put_contents(), a really easy way of doing this.

Zarathuztra · Accepted Answer · 2013-12-15 23:14:41Z

2

When you call mysql_fetch_array(), the row returned is already an associated array. What you can then do is store that row in another array (effectively now a two dimensional array), but you'll also want that array to be associative. If you follow Floris' method, you'll achieve your desired result just fine, so I'm just going to provide an alternative whereby each row gets some sort of meaningful label for your JSON export

NOTE: mysql_* functions are deprecated. I am only using them for the purpose of example. Please comment or send a message and I will post the mysqli version.

$result = mysql_query('select * from locations');
$locations = array();
$i=0;
while($row = mysql_fetch_array($result)){
    $locations['location_'.$i] = $row;
    $i++;
}

echo json_encode($locations);

EDIT: The mysqli version using 1:1 function conversions and no security enhancements, or object oriented code:

$con = mysqli_connect($host,$username,$password,$db_name);//these can have defaults as per your php.ini file.

$result = mysqli_query('select * from locations');
$locations = array();
$i=0;
while($row = mysqli_fetch_assoc($result)) {
    $locations['location_'.$i] = $row;
    $i++;
}    
echo json_encode($locations);

edited Dec 15, 2013 at 23:14

answered Dec 15, 2013 at 22:54

Zarathuztra

3,2411 gold badge23 silver badges34 bronze badges

3 Comments

Behseini Over a year ago

Thanks Zarazthuztra, Can you please let me know how to use the mysqli insteard of my code?

Zarathuztra Over a year ago

Sure! It's really not much different than mysql itself, I'll edit my answer. Although first, make sure you've followed the following guide for setup: php.net/manual/en/mysqli.setup.php

Zarathuztra Over a year ago

@zerkms, thanks for catching that error! I was typing up a post for something and totally zoned on that one.

Ermi · Accepted Answer · 2015-11-26 23:25:53Z

1

This is how you can do it Behseini,

$result = mysql_query('select * from locations');

$locations = array();

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) 
{
    $loc["id"] = $row["id"];
    $loc["type"] = $row["type"];
    $loc["lat"] = $row["lat"];
    $loc["long"] = $row["long"];
    $loc["latlong"] = $row["latlong"];

    array_push($locations,$loc);
}

answered Nov 26, 2015 at 23:25

Ermi

2013 silver badges9 bronze badges

Collectives™ on Stack Overflow

How to Store MySQL Table in a PHP Array

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related