stivenbr

Hello Everyone, I am having some trouble with a query and hopefully someone can guide me through this? I think this should be possible to accomplish. I have two tables. Table A stores hours and Table B stores driving time. I am trying to get all the people who have more than 6 hours worked in a day which I was able to do. But I also need to get the driving time for that specific day where they did more than 6 hours in a day. I hope I make sense. This is the query that I am using right now and is working to get everyone who worked more than 6 hours a day I just can get the minutes as well. The driving time is store in table B called ies_mileage and the field is duration. I have tried several things with no success. SELECT wk_date,full_name, SUM(hours) AS HOURS FROM `ies_weekly` LEFT JOIN infant_specialist ON ies_weekly.is_id = infant_specialist.is_id WHERE wk_date BETWEEN '2015-01-16' AND '2015-01-31' GROUP BY DAY(wk_date), ies_weekly.is_id HAVING SUM(hours) > 6 ORDER BY full_name ASC This is working, when I tried to add the duration the total gets messed up and it's no longer accurate. here is what i've tried SELECT wk_date,full_name, SUM(hours) AS HOURS, SUM(duration) AS minutes FROM `ies_weekly` LEFT JOIN infant_specialist ON ies_weekly.is_id = infant_specialist.is_id LEFT JOIN ies_mileage ON ies_weekly.wk_date = ies_mileage.mil_date AND ies_weekly.is_id = ies_mileage.ies_id WHERE wk_date BETWEEN '2015-01-16' AND '2015-01-31' GROUP BY DAY(wk_date), ies_weekly.is_id HAVING SUM(hours) > 6 ORDER BY full_name ASC Thank you for your help

Hello, I have this while loop that is somewhat working I am trying to group the records by user and send one email with all related records for that user. somewhere in my logic is not working right. the first record per each user is missing in my code. Thank you for your help $msg = ''; while($row = mysqli_fetch_array($q) ){ //$is = $row['is_id']; if($is == '' || $is == $row['is_id']){ if($row['month'] == $month){ $template = 'Annual'; } if($row['month'] == ($m2) ){ $template = 'Quarterly'; } if($row['month'] == ($m3) ) { $template = 'Semi-Annual'; } if($row['month'] == ($m4) ) { $template = 'Quarterly'; } $teacher = $row['full_name']; $msg .= $row['client_name'] . ' - ' . $row['full_name'] . ' - ' . $template . ' <a href="https://secure.iessi.net/docs.php?cid='.$row['case_no'].'">Click to Download</a> <br/>'; } //$lastis = $row['is_id']; if($is != $row['is_id']){ if(!empty($msg)){ echo 'Hello ' . $teacher . '<br/><br/>'; echo $msg . ' <br/> email sent <br/><br/><br/>'; } $msg = ''; $is = $row['is_id']; //$lastis = $row['is_id']; } }

I just tested everything but I am missing one record per user :/ I don't understand what is wrong with the code :/

I have to add one more thing to this email :/ I need to set what template is needed and this is dependent on a date field from the record. this logic is driving me a little crazy I hope I can explain myself :/ I have report month let's say report month is 11 this is the check I need to do against DB Field here is a little example if month == 11 then for months 4 and 10 template = Quarterly for month 7 then template = Semi-Annual for month 11 then Template = Annual. it depends on the report month something like this report month - 3 = quarterly and report month - 8 = quarterly, report month - 6 = semi-annual report month = 11 then its annual the code I have only works for this month but it won't work when the month lands on January through June here is what I have so far $is = ''; $msg = ''; while($row = mysqli_fetch_array($q) ){ if($is == $row['is_id']){ if($row['month'] == $month){ $template = 'Annual'; } if($row['month'] == ($month - 6) ){ $template = 'Semi-Annual'; } if($row['month'] == ($month - 3) ) { $template = 'Quarterly'; } if($row['month'] == ($month - ) { $template = 'Quarterly'; } $msg .= $row['client_name'] . ' - ' . $row['full_name'] . ' - IFSP Month ' . $row['month'] . ' - ' . $template . '<br/>'; } if($is != $row['is_id']){ if(!empty($msg)){ echo $msg . ' email sent <br/><br/><br/>'; } $msg = ''; } $is = $row['is_id']; }

thank you for your help.. this is working for me too I think it looks ok.. $is = ''; $msg = ''; while($row = mysqli_fetch_array($q) ){ if($is == $row['is_id']){ $msg .= $row['client_name'] . ' - ' . $row['full_name'] . ' - ' . $row['month'] . '<br/>'; } if($is != $row['is_id']){ if(!empty($msg)){ echo $msg . ' email sent <br/><br/><br/>'; } $msg = ''; } $is = $row['is_id']; }

Thank you for your help, I am still having a little trouble I'm not sure what I am doing wrong... here is my code. $is = ''; $msg = ''; while($row = mysqli_fetch_array($q) ){ if($is == $row['is_id']){ $msg .= $row['client_name'] . ' --- ' . $row['full_name'] . ' LINK <br/>'; } if($is != $row['is_id']){ echo $msg . ' email sent <br/>'; } $is = $row['is_id']; }

Hello, I was wondering if maybe someone could help me on this. I am having trouble with this query. I need to send an email to the users but group all the records related to that user instead of sending one email per record to the user. I hope this makes sense. Here is the code I have so far. All records are being displayed but how can I get all the records for each user (infant_specialist field) send one email and then go to the next user and send the other email and so on. Thank you for your help $conn = mysqli_connect('localhost','root','','dbname'); $sql = "SELECT case_no, client_name, infant_specialist.full_name, infant_specialist.is_email FROM ies_clients INNER JOIN infant_specialist ON ies_clients.infant_specialist = infant_specialist.is_id WHERE (date_closed IS NULL OR date_closed >= DATE_FORMAT(NOW() ,'%Y-%m-01')) ORDER BY client_name ASC"; $q = mysqli_query($conn,$sql); //echo mysqli_num_rows($q) . '<br/>'; while($row = mysqli_fetch_array($q) ){ echo $row['client_name'] . ' --- ' , $row['full_name'] . ' --- ' . $row['is_email'] . '<br/>'; }