Using mysql_get in reports

Hi nmsinc, 

What's the tablename that you want to load those fields from and what is there WHERE on conditions you want to filter on (eg: first record, record 456, record where title = 'something').

Let me know and I'll try and help.  Thanks!

Hi Dave,

I need to report the field: 'employee_number' from the 'accounts' section based on the field value of 'employee_name' stored in the 'paysheet_hours' section! 

Thanks - nmsinc

The Reports Plugin although very good has its limits and I need to bring in other field data that is not supported. Does interactivetools have a plugin with csv conversion with more options?

It would be great to have search options built in!

Thanks - nmsinc

Ahh, it's for Report Builder.  I've been coding blind! :) 

You need a join for that, can you post your full query into your message?  I'll see if I can update it to do a mysql join for you.

Cheers,

Hi Dave,

See code below:

------------------------------------------------------------------------------------------------------

SELECT DATE_FORMAT(date_hours_worked, '%M %D %Y') as 'Work Date',
       assinged_to_which_group           as 'Member Company',
       employee_name                     as 'Imployee Number',
       hours_worked                      as 'Hours Worked',
       payroll_accounting_group          as 'Work Group',
       assign_contract_group_id          as 'Assigned To Work Group'

FROM `<?php echo $TABLE_PREFIX ?>paysheet_hours`
<?php
$start = date("Y-m-d 00:00:00", strtotime('2013-02-03'));
$end = date("Y-m-d 00:00:00", strtotime('2013-02-28'));
$timedate = strtotime('date_hours_worked');
?>

<?php if ($timedate > $start OR $timedate < $end): ?>

WHERE assinged_to_which_group = 177 AND date_hours_worked > '<?php echo $start; ?>' AND date_hours_worked < '<?php echo $end; ?>'
GROUP BY date_hours_worked
ORDER BY YEAR(date_hours_worked) DESC, MONTH(date_hours_worked) DESC

<?php endif; ?>

----------------------------------------------------------------------------------------------

I want to bring in information from two sections:

SECTION #1: "payroll_dates" with fields 'start_date' and 'end_date' to replace the start and end date sorting above.

SECTION #2: "accounts" with the field 'fullname' of the employee based upon the field  'employee_name' in the report above. The field 'employee_name' in the table 'paysheet_hours' is populated when the user chooses an employee from a list of employee names and it saves the 'employee_number' field rather then the 'num' field.

Thanks - nmsinc

Hi nmsinc, 

I'm not exactly sure about how to integrate payroll_dates but here's a join for getting the employee number:

SELECT DATE_FORMAT(ph.date_hours_worked, '%M %D %Y') as 'Work Date',
       ph.assinged_to_which_group           as 'Member Company',
       ph.employee_name                     as 'Imployee Number',
       ph.hours_worked                      as 'Hours Worked',
       ph.payroll_accounting_group          as 'Work Group',
       ph.assign_contract_group_id          as 'Assigned To Work Group',
       a.employee_number                    as 'Employee #'
     FROM `<?php echo $TABLE_PREFIX ?>paysheet_hours` ph
LEFT JOIN `<?php echo $TABLE_PREFIX ?>accounts` a ON ph.employee_name = a.employee_name

How it works is we add an alias after eachg table name (ph for paysheet_hours) and then add that to all the fieldnames so mysql knows which table they're from.  Then where paysheet_hours.empoyee_name is the same as accounts.employee_name we're load a.employee_number.

Hope that helps!  Let me know if you can get any further with that or any other questions.

Hi Dave,

I tried to use SET lc_time_names in a request, but that does not function in reportBuilder.

SET lc_time_names = 'es_ES.UTF-8';
SELECT DATE_FORMAT(`date`, '%d %M %Y') FROM `dates`;

Do you think that it is possible to add the function directly in the script?

Thank you for your suggestion.

Djulia

Hi Djulia, 

The problem is PHP's mysql_query() does not support multiple queries.  

What about putting that first line in a plugin that runs on every CMS page load.  Because you probably want that setting everywhere don't you?

Let me know if that would work for you or if I can help with a plugin.  Thanks!