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

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.

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!