pull the data in mySql database into a dataframe and create a subset
personQuery <- dbGetQuery(mySqlConnStr, "SELECT p.person_id, p.gender, p.birthdate, a.address1, a.address2, a.city_village, a.state_province, a.country,n.given_name, n.middle_name, n.family_name
FROM person p
INNER JOIN person_address a ON p.person_id = a.person_id
INNER JOIN person_name n ON p.person_id = n.person_id
")
persons <-distinct(personQuery, person_id, gender, birthdate, given_name, middle_name, family_name, address1, address2, city_village, state_province, country)
persons$fullName <- paste(persons$given_name, " ", persons$family_name, sep = "")
persons$address <- paste(persons$address2, ", ", persons$city_village, ", ", persons$state_province, ", ", persons$country, sep = "")
cleanPersonsData <- select(persons, person_id, gender, birthdate, fullName, address)
datatable(cleanPersonsData, class = 'cell-border stripe', options = list(
initComplete = JS(
"function(settings, json) {",
"$(this.api().table().header()).css({'background-color': '#26868d', 'color': '#fff', 'text-align': 'center !important'});",
"$(this.api().table().body()).css({'color': '#000', 'text-align': 'center !important'});",
"}")
))