Build an array from a MySQL query?

Question

Could someone please let me know how I can create an array from the MySQL query?

The array should look like this:

<?php
$testLocs = array(
    '1' => array( 'info' => '1. New Random info and new position', 'lat' => 45345345, 'lng' => 44.9634 ),
    '2' => array( 'ino'  => '1. New Random info and new position', 'lat' => 686788787, 'lng' => 188.9634),
    '3' => array( 'info' => '1. New Random info and new position', 'lat' => 88888, 'lng' => 144.9634),
    '4' => array( 'info' => '1. New Random info and new position', 'lat' => 666666, 'lng' => 144.964 )
);
echo json_encode($testLocs);
exit();
?>

Where the text for info and numbers for lat and lng are MySQL $rows.

Any help would be appreciated.

Kapil Sharma · Accepted Answer · 2015-08-04 14:18:14Z

1

Read the manual

Example taken from PHP manual

$sth = $dbh->prepare("SELECT name, colour FROM fruit");
$sth->execute();
$result = $sth->fetch(PDO::FETCH_ASSOC);

answered Aug 4, 2015 at 14:18

Kapil Sharma

10.5k8 gold badges40 silver badges70 bronze badges

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

Comments

Jan.J · Accepted Answer · 2015-08-04 14:20:05Z

1

If I undestand it correctly you need to map mysql array results to some kind of normalized array. Function array_map would be perfect for it:

$array = array_map(function ($row) {
    return array(
        'info'  => $row['info'],
        'lat'   => $row['lat'],
        'lng'   => $row['lng'],
    );
}, $rows);

If you need indexes starting from 1 you should also add this line:

$array = array_combine(range(1, count($array)), $array);

answered Aug 4, 2015 at 14:20

Jan.J

3,0801 gold badge26 silver badges34 bronze badges

4 Comments

H.HISTORY Over a year ago

could you please let me know how you would actually use this in a sql query?

Jan.J Over a year ago

I thought you already have results of mysql query. How do you connect to db from php?

H.HISTORY Over a year ago

I actually do have the results of mysql query. but when I place your code in my while loop in MYSQL query, I only get [] printed on my php page. that is why i asked you to show me an example if possible so I know how array_combine works in relation to while loop etc.

Jan.J Over a year ago

If you mean that your loop is over mysql results than it is not needed here. Function array_map will act like a loop and iterate over each row of results.

Matt Jameson · Accepted Answer · 2015-08-04 14:47:37Z

1

   $testLocs = array();

   $sql = <<<SQL
            SELECT id, info, position, lat, lng
            FROM tbl_info
SQL;

    $q = mysql_query($sql);    

    while ($r = mysql_fetch_assoc($q)) {
        $testLocs[$r['id']] = $r;
    }

    print_r($testLocs);

Obviously replace the query with the correct one.

PS. This is the mysql way, not the mysqli way. It might be worth looking at both.

edited Aug 4, 2015 at 14:47

answered Aug 4, 2015 at 14:33

Matt Jameson

5823 silver badges16 bronze badges

7 Comments

H.HISTORY Over a year ago

aren't you suppose to declare $testLocs somewhere?

Matt Jameson Over a year ago

Yes by rights you are. this will still work but will throw warnings. I will edit

Absalon Valdes Over a year ago

mysql_* functions are deprecated. please consider using mysqli_* or PDO

H.HISTORY Over a year ago

This will print out something like this: {"1":{"id":"1","name":"Rooz","lat":"51.5033630","lng":"-0.1276250"}} but I need it to be like this: {"1":{"info":"Rooz","lat":51.503363,"lng":-0.127625}}

Matt Jameson Over a year ago

Yep, change query accordingly to achieve desired array, mine was a only an example

|

Collectives™ on Stack Overflow

Build an array from a MySQL query?

3 Answers 3

Comments

4 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related