Join mysql query results to an array

Question

I am trying to generate a table list from data held in 2 tables. One table is called PrimaryEvents and some sample data looks like:

|id|event |validity|eventsrequired
|1 |event1 |7 |10
|1 |event2 |25 |1
|1 |event3 |12 |50

id here is just the user id of whoever created the data.

The second table is called portfolio, some sample data looks like:

|id|name |number|datacompleted
|21|event1 |3 |2014年07月07日
|15|event1 |5 |2014年07月05日
|21|event2 |5 |2014年05月08日
|15|event1 |1 |2013年05月05日
|15|event1 |1 |2014年05月05日
|21|event1 |13 |2014年08月07日
|21|event1 |6 |2014年07月08日

id here is the id of the user that has completed the event.

I have a query that populates an array to allow a table to be shown to the user, the table shows a list of ALL the events from PrimaryEvents with a left join of data from portfolio if they have completed an event by the same name.

I would like to change the functionality slightly to allow for the following: Only events that are within the validity period date range are merged, AND the merged data automatically SUMs the number (from portfolio.number).

I am not sure how to progress this really. Currently, I have managed to extract the data range using the following code:

$currentDate = DATE("Y-m-d");
$eventList = query("SELECT event,validity FROM PrimaryEvents ORDER BY event");
foreach ($eventList as $row)
 {
 //
 $event["validity"] = $row["validity"];
 $validityDate = Date("Y-m-d", strtotime("-". $row["validity"] . "days"));
 $eventsDateValidCompleted = query("SELECT * FROM portfolio WHERE id = ? AND datecompleted BETWEEN ? AND ? ORDER BY name", $_SESSION["id"], $validityDate , $currentDate);
 } 

What I am missing is how to do something useful with the daterange sorted array data.

Questions: 1) How can I SUM the event data within the valid date range (IE returned in $eventsDateValidCompleted array)?

2) How can I LEFT JOIN this array of data to each line of my current query?

 $allEvents = query("SELECT * FROM PrimaryEvents LEFT JOIN portfolio ON (PrimaryEvents.event = portfolio.name) WHERE PrimaryEvents.role = ? AND (portfolio.id = ? Or portfolio.id is null) ORDER BY PrimaryEvents.event", $currentRole, $_SESSION["id"]);

3) Is there a better way to make this happen?

Thanks for any help that can be offered on this.

Answer 1

You can sum values from a query in MySQL using the SUM() function along with a GROUP BY clause. For example if you wanted to know the sum of all relevant portfolio.number values for a given user id and date range you could change this line:

$eventsDateValidCompleted = query("SELECT * FROM portfolio WHERE id = ? AND datecompleted BETWEEN ? AND ? ORDER BY name", $_SESSION["id"], $validityDate , $currentDate);

to this:

$eventsDateValidCompleted = query("SELECT SUM(number) AS total_number FROM portfolio WHERE id = ? AND datecompleted BETWEEN ? AND ? GROUP BY id", $_SESSION["id"], $validityDate , $currentDate);

And if you wanted to get this sum value by event, and as part of the original query you could do something like this:

SELECT e.event,e.validity, SUM(p.number) AS total_number
FROM PrimaryEvents e 
LEFT JOIN portfolio p ON p.name = e.event
GROUP BY e.id
ORDER BY e.event

• andy: Thank you. I will give this a try tomorrow and let you know how I get on.
• andy: Could you provide some amplification on how to use the second piece of code you posted? I would like to be able to run the query and then sum the number of events returned with the date range.
• oliakaoil: If you need to know the number of rows returned by a query, take a look at mysql_num_rows. Of course you could also run a count query directly, which is accomplished by replacing your SELECT parameters with COUNT(*). HTH.
• andy: Sorry, I am still slightly confused. Run my code as posted above, then within my current foreach loop add the new SELECT query you posted above to do the sum work?

Answer 2

I'm not sure I understand exactly what you want, but I suggest using SQL SUM() and GROUP BY along these lines:

SELECT pe.id, pe.event, sum(p.number) FROM PrimaryEvents pe
LEFT JOIN portfolio p ON (pe.event = p.name) AND p.id = pe.role AND p.datecompleted BETWEEN ? AND ?
WHERE pe.role = ? 
GROUP BY pe.id, pe.event

But, as I said, your (reduced) data model and the queries are not quite consistent, which is why I cannot tell if the above will do anything for you.