Adding a key to a multidimensional array from another multidimensional array

Question

I have seen lots on adding to multidimensional arrays but I can't figure out how they apply to my situation. I am trying to end up with one large multi-dimensional array.

I have a multidimensional associative array compiled from a mysql query of a membership database. All my arrays are compiled using while loops.

Array(
     [0] => Array ( [full_name] => Amy Smith [id] => 00111111 [member_ref] => 1 [type] => 1 ) 
     [1] => Array ( [full_name] => Bob Smith [id] => 00222222 [member_ref] => 2 [type] => 0 ) 
     [2] => Array ( [full_name] => Cam Smith [id] => 00333333 [member_ref] => 3 [type] => 2 ) 
     )

This was compiled from the mysql_query

SELECT full_name, id, member_ref, type 
FROM members 
ORDER BY full_name asc

I then have a list of [id] numbers in another table for people who have paid membership. This is provided by another organisation. This will become the [paid] key.

Array(
      [0] => Array ( [id] => 00111111 ) 
      [1] => Array ( [id] => 00333333 ) 
      [2] => Array ( [id] => 00444444 ) 
     )

From query:

SELECT * FROM paid

And finally a table that counts members attendances within a specified timeframe, to be the [log] key.

Array(
 [0] => Array ( [member_ref] => 1 [COUNT(member_ref)] => 17 ) 
 [1] => Array ( [member_ref] => 2 [COUNT(member_ref)] => 10 ) 
 [2] => Array ( [member_ref] => 3 [COUNT(member_ref)] => 1 )
     )

From

SELECT member_ref, COUNT(member_ref) 
FROM log 
WHERE timestamp >= STR_TO_DATE('$from') 
AND timestamp < STR_TO_DATE('$to') 
GROUP BY member_ref

What i want to end up with is: (Note that Bob Smith hasn't paid)

 Array(
     [0] => Array ( [full_name] => Amy Smith [id] => 00111111 [member_ref] => 1 [type] => 1 [paid] => 00111111 [log] => 17) 
     [1] => Array ( [full_name] => Bob Smith [id] => 00222222 [member_ref] => 2 [type] => 0 [paid] => 0 [log] => 10) 
     [2] => Array ( [full_name] => Cam Smith [id] => 00333333 [member_ref] => 3 [type] => 2 [paid] => 00333333 [log] => 1) 
      )

I can then output it as necessary, but crucuially I can sort the data by any of the keys.

I am open to doing this with a single mysql query with the sort key being a php $var, but I can't figure out the JOIN elements, it's just far too complex for me to understand.

Thanks

there's no need for str_to_date() if your date is already in yyyy-mm-dd format. MySQL can understand that directly. You only need str_to_date() if your date string is in a non-standard format, like Jul 31, 2014 or whatever. — Marc B
– Marc B, Commented Jul 31, 2014 at 16:05
so for paid, you either want there Id or zero? why not a 0 or 1 or true of false? — NSjonas
– NSjonas, Commented Jul 31, 2014 at 16:10

Arth · Accepted Answer · 2014-07-31 22:49:51Z

1

Working from the answer of NSjonas:

   SELECT m.full_name,
          m.id,
          m.member_ref,
          m.type, 
          (p.id IS NOT NULL) paid,
          COUNT(l.member_ref) log
     FROM members m
LEFT JOIN paid p
       ON m.id = p.id
LEFT JOIN log l
       ON m.member_ref = l.member_ref
      AND l.timestamp >= '$from' 
      AND l.timestamp < '$to'
 GROUP BY m.full_name, m.id, m.member_ref, m.type, p.id
 ORDER BY m.full_name ASC

edited Jul 31, 2014 at 22:49

answered Jul 31, 2014 at 16:16

Arth

13.1k5 gold badges43 silver badges72 bronze badges

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

8 Comments

NSjonas Over a year ago

yup yours is a bit cleaner and more correct, good job. I haven't been writing sql for almost a year now. a little rusty with the syntax

Arth Over a year ago

@NSjonas, thanks I tried editing your answer.. but I think it got lost somewhere.. as you did most of the work, if your answer gets corrected and accepted I'll delete this one.

NSjonas Over a year ago

Yea i made my own edit before i saw that so it got automaticly refused.

NSjonas Over a year ago

your use of (p.id IS NOT NULL) is better than my case statement, so i think its a valid (better) answer anyways. Is the group by necessary?

Arth Over a year ago

@NSjonas Ok, I don't know about the GROUP BY, I'd probably put it in just for readability, I'd have to test it to be sure.

|

NSjonas · Accepted Answer · 2014-07-31 16:18:53Z

1

My mySQL is a bit rusty but I think this is basically what your looking for. Definitely possible in 1 query

SELECT m.full_name, m.id, m.member_ref, m.type, COUNT(l.member_ref) log,
Case WHEN p.Id != null
 THEN true
 ELSE false paid
FROM members m
LEFT JOIN paid p 
ON m.id = p.id
LEFT JOIN log l
ON m.member_ref = l.member_ref
WHERE l.timestamp >= STR_TO_DATE('$from') 
AND l.timestamp < STR_TO_DATE('$to')
ORDER BY m.full_name asc

edited Jul 31, 2014 at 16:18

answered Jul 31, 2014 at 16:05

NSjonas

12.4k11 gold badges76 silver badges103 bronze badges

5 Comments

NSjonas Over a year ago

actually looks like I misunderstood the "paid key". I assumed you only wanted members who paid. Let me try again

SLV Over a year ago

If it helps the error generated is near 'INNER JOIN paid ON members.id = paid.id INNER JOIN log ON members.member_ref' at line 3

NSjonas Over a year ago

@SLV opps, I refactored based and missed that line

NSjonas Over a year ago

@SLV hey man, I think you need to refresh your page and look at either my updated answer or the answer by arth. My first attempt was not correct and you want to use LEFT join instead of INNER joins so that you always select all records from the Members table.

SLV Over a year ago

Thanks. I was refreshing I don't know what happened. Thanks for your help.

Collectives™ on Stack Overflow

Adding a key to a multidimensional array from another multidimensional array

2 Answers 2

8 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related