php loop on array to calculate entries from database

Question

I have an arrays that are created from 2 database tables, staff and time. The arrays look like this:

`array(44) { 
[0]=> string(2) "25" 
["id"]=> string(2) "12" 
[1]=> string(2) "12" 
["staff_id"]=> string(2) "12" 
[2]=> string(3) "9.6" 
["timein"]=> string(3) "9.6" 
[3]=> string(0) "" 
["onholiday"]=> string(0) "" 
[4]=> string(10) "2015-06-29" 
["dateadded"]=> string(10) "2015-06-29" 
[5]=> string(0) "" 
["ill"]=> string(0) "" 
[6]=> string(1) "1" 
["notes"]=> string(1) "1" 
[7]=> string(2) "12" 
[8]=> string(4) "name" 
["staff_name"]=> string(4) "name" 
}`

There are arrays with the same staff_id. What I want to do is create a PHP loop that finds all the arrays with the same staff_id and calculate the time_in values.

I currently have

$sql = "SELECT *  
        FROM time, staff 
        WHERE dateadded BETWEEN '$startdate' AND '$enddate' 
        AND time.staff_id = staff.id 
        ORDER BY dateadded DESC;";
$result =  mysqli_query($connect, $sql);
while($row = mysqli_fetch_array($result)){
echo $row['staff_name'];
}

But as you can see this is only very simple and gives back the list of names from the database.

My Database tables are as follows: Staff:

id |staff_name|department_id
----------------------------
1  |Joe Bloggs|1
2  |John Smith|3
10 |staff3    |6
12 |name      |1
13 |Jo Bloggs |1

time:

id |staff_id|timein|onholiday|dateadded|ill|notes
-------------------------------------------------
12 |12      |3     |         |         |   |
13 |1       |6     |         |         |   |
14 |1       |4     |         |         |   |
15 |1       |7     |         |         |   |
16 |1       |7     |         |         |   |
17 |1       |7     |         |         |   |
18 |10      |7     |         |         |   |
19 |2       |7     |         |         |   |
20 |1       |8     |         |         |   |
21 |1       |7     |         |         |   |
22 |1       |7     |         |         |   |
23 |2       |8     |         |         |   |
24 |10      |9     |         |         |   |
25 |12      |9.5   |         |         |   |

Please use mysqli_fetch_assoc($result) because it will be easier to work with your return array. — MonkeyZeus
– MonkeyZeus, Commented Jun 29, 2015 at 13:05
please post the table structure for both tables and let us know what you need to get from both, final result — Alex Andrei
– Alex Andrei, Commented Jun 29, 2015 at 13:05
Also please go read tutorials/php manual, because what you are asking for is a simple PHP script, that you would like someone to write for you. — Siim Kallari
– Siim Kallari, Commented Jun 29, 2015 at 13:06

Anders · Accepted Answer · 2015-06-29 13:15:03Z

2

If I understand you right this can be done using only SQL and no additional PHP:

SELECT staff.id, SUM(time.timein) As TimeIn
    FROM time, staff 
    WHERE dateadded BETWEEN '$startdate' AND '$enddate' 
    AND time.staff_id = staff.id
    GROUP BY staff.id;

I did some guessing when it comes to column names, so you will need to modify so it fits your DB. Read more about GROUP BY here.

answered Jun 29, 2015 at 13:15

Anders

8,66310 gold badges60 silver badges101 bronze badges

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

3 Comments

Anthony Broadbent Over a year ago

Thanks @anders, this looks like it does the job. I cant upvote it because I dont have enough rep yet but I have accepted this and my answer.

Anthony Broadbent Over a year ago

Ah @anders Ive just realised that I am going to need to split the result into 4 weeks but this statement outputs the total. Is there a way I can use multiple between statements and output each result separately or is it more complex than that?

Anders Over a year ago

It is possible to do in SQL. Group not only by staff.id, but also by an expression returning the number of the week. On MySQL it would look something like WEEK(dateadded). Read more about WEEK here.

Collectives™ on Stack Overflow

php loop on array to calculate entries from database

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related