/    Sign up×
Community /Pin to ProfileBookmark

Get Array In Single Column

Copy linkTweet thisAlerts:
Sep 07.2021

I’m doing a sum within a SQL query.

That sum is coming from a column in a joined table. This table also has an ID field relating to another table.

I’m getting the SUM perfectly. But I also need to know what all the ID’s are.

I’ve got two options.

a) SELECT and order by this ID. When I loop through the results I accumulate the total as I go and put the ID into an array. At the end I have a grand total and array of ID’s I need.

b) is there a way to place these into a single column as an array?

to post a comment
PHP

3 Comments(s) ↴

Copy linkTweet thisAlerts:
@NogDogSep 07.2021 — I'd probably use the MySQL [json_arrayagg() function](https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_json-arrayagg), then you can use PHP's json_parse() if you want to get it into a PHP array. Not having a clear idea of the relationships involved here, it's hard to suggest a sample query, though. But basically, the same as (presumably) you do a sum(table_x.some_field) in the query's select clause, you'd also add a json_arrayagg(table_y.some_id) to get that collection of IDs.
Copy linkTweet thisAlerts:
@ginerjmSep 14.2021 — Assuming that you simply want an array with each id's total value:
<i>
</i>$q = "select sum(value) as total, id from tablename order by id group by id";
$qrslts = $pdo-&gt;query($q);
$vals_ar = array();
if ($qrslts)
{
while($row = $qrslts-&gt;fetch())
$vals_ar[$row['id']] = $row['total'];
}
Copy linkTweet thisAlerts:
@sarthakseconnectSep 16.2021 — <?php

// Array representing a possible record set returned from a database

$records = array(

array(

'id' => 2135,

'first_name' => 'John',

'last_name' => 'Doe',

),

array(

'id' => 3245,

'first_name' => 'Sally',

'last_name' => 'Smith',

),

array(

'id' => 5342,

'first_name' => 'Jane',

'last_name' => 'Jones',

),

array(

'id' => 5623,

'first_name' => 'Peter',

'last_name' => 'Doe',

)

);

$first_names = array_column($records, 'first_name');

print_r($first_names);

?>

The above example will output:

Array

(

[0] => John

[1] => Sally

[2] => Jane

[3] => Peter

)

**Links removed by Site Administrator so it doesn't look like you're spamming us. Please don't post them again.**
×

Success!

Help @kiwis spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 4.24,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,

tipper: @Samric24,
tipped: article
amount: 1000 SATS,
)...