CODEWITHSUNDEEP
phpmysqlarrays
SLV
SLV

Reputation: 45

Adding a key to a multidimensional array from another multidimensional array

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

Upvotes: 1

Views: 45

Answers (2)

Arth
Arth

Reputation: 13110

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

Upvotes: 1

NSjonas
NSjonas

Reputation: 12042

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

Upvotes: 1

Related Questions