current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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

share|improve this question
    
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 20 hours ago
    
so for paid, you either want there Id or zero? why not a 0 or 1 or true of false? –  NSjonas 20 hours ago
    
Marc - If it ain't broke... –  SLV 20 hours ago
    
NSjonas they are all fine. –  SLV 20 hours ago

2 Answers 2

up vote 1 down vote accepted

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
share|improve this answer
    
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 –  NSjonas 20 hours 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. –  Arth 20 hours ago
    
Yea i made my own edit before i saw that so it got automaticly refused. –  NSjonas 20 hours 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? –  NSjonas 20 hours ago
1  
@NSjonas thank you too. Such speedy reponses! –  SLV 19 hours ago
up vote 1 down vote

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
share|improve this answer
    
actually looks like I misunderstood the "paid key". I assumed you only wanted members who paid. Let me try again –  NSjonas 20 hours 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 –  SLV 20 hours ago
    
@SLV opps, I refactored based and missed that line –  NSjonas 20 hours 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. –  NSjonas 20 hours ago
    
Thanks. I was refreshing I don't know what happened. Thanks for your help. –  SLV 19 hours ago

Your Answer

 
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.