up vote 0 down vote favorite

This is a problem that I come across frequently when using PHP to query mysql data, and I would like to know if there is a more efficient solution. When I only need two columns of data, for instance the columns 'id' and 'price', I prefer this 'flat' format: 

array(
    id 1 => price 1,
    id 2 => price 2,
    id 3 => price 3,
    ...
);

or in json:

[{"id 1":"price 1"},{"id 2":"price 2"},{"id 3":"price 3"}, ...]

And my usual solution is to loop twice, like so: 

require_once('server/connection.php');
$info = mysql_query("SELECT id, price FROM table");  

$array1 = array();
while ($row = mysql_fetch_assoc($info)) {
    $array1[] = array(
        'id' => $row['id'],
        'price' => $row['price']
    );
}
mysql_close($con);

$array2 = array();
foreach ($array1 as $key => $value) {
    $array2[$key][$value['id']] = $value['price'];
}

print json_encode($array2);

which does work, but I think this code is too lengthy for its purpose, and there should be a better way -- so that I only have to loop one array. Any suggestions?

share|improve this question
Second loop: you are rather making a two dimensional array. – FatalError Aug 15 '12 at 15:05
feedback

3 Answers

up vote 0 down vote accepted

You can simplify your loop to this

while ($row = mysql_fetch_assoc($info)) {
    $array1[] = array(
        'id '.$row['id'] => 'price '.$row['price']
    );
}

print json_encode($array1);
share|improve this answer
Thank you, this was much simpler than I thought! – J. Mouse Aug 15 '12 at 15:48
feedback
up vote 0 down vote 
require_once('server/connection.php');
$info = mysql_query("SELECT id, price FROM table");  

$array1 = array();
while ($row = mysql_fetch_assoc($info))
    $array1[$row['id']] = $row['price'];
mysql_close($con);

print json_encode($array1);

NOTE: your $array2 is a two dimensional array. If it works for you, you need to change your javascript code to handle following flat format i.e. the above code produce

[{"id 1":"price 1"},{"id 2":"price 2"},{"id 3":"price 3"}, ...]
share|improve this answer
This works well too. Thank you for pointing out that it's a two dimensional array. – J. Mouse Aug 15 '12 at 15:49
feedback
up vote 0 down vote 
$result = array();
while ($row = mysql_fetch_assoc($info)) {
    $result[$row['id']] = $row['price'];
}
print_r($result);
share|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.