Using mysql_get in reports

12 posts by 3 authors in: Forums > CMS Builder: Plugins & Add-ons
Last Post: April 3, 2013   (RSS)

By nmsinc - February 14, 2013

I need to grab a field from the user section and add it to the SELECT GROUP below. I tried using mysql_get with no luck - any suhhestions?

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'

 Thanks - nmsinc

nmsinc

By Dave - February 14, 2013

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!

Dave Edis - Senior Developer
interactivetools.com

By nmsinc - February 15, 2013

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

nmsinc

By Dave - February 17, 2013

Hi nmsinc, 

CMSB doesn't have any support for anything like that.  But here's two ways to get data from two tables with PHP and MySQL.  They're both a little tricky but will hopefully point you in the right direction.

Using a MySQL join:

   SELECT t1.fullname, t2.num
     FROM cms_accounts t1
LEFT JOIN cms_accounts t2 ON t1.fullname = t2.fullname

Basically saying "join table1 with table2 when both fields match.  Using your fieldnames it might look like this:

  SELECT t1.employee_name, t2.employee_number
    FROM {$TABLE_PREFIX}paysheet_hours t1
EFT JOIN {$TABLE_PREFIX}accounts t2 ON t1.employee_name = t2.employee_name

Or if you're working in PHP, another way is just to create a lockup array of employee names to nums:

$accounts     = mysql_select('accounts');
$accountNames = array_pluck($accounts, 'employee_name');
$accountNums  = array_pluck($accounts, 'employee_number');
$namesToNums  = array_combine($accountNames, $accountNums);

print "Bob is account # " .@$namesToNums['Bob']. "<br/>\n";
print "And here's a list of all the names to nums:\n";
showme($namesToNums);
exit;

And if it's easy to do, it's generally better to associate records by  number (num, or employee_number) instead of name, since they're less likely to change than names, labels, or titles.

Hope that helps, let me know any questions.

Dave Edis - Senior Developer
interactivetools.com

By nmsinc - February 18, 2013

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

nmsinc

By Dave - February 18, 2013

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,

Dave Edis - Senior Developer
interactivetools.com

By nmsinc - February 18, 2013

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

nmsinc

By Dave - February 21, 2013

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.

Dave Edis - Senior Developer
interactivetools.com

By Djulia - April 1, 2013 - edited: April 1, 2013

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

By Dave - April 2, 2013

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!

Dave Edis - Senior Developer
interactivetools.com