I am working on an attendance application. I currently have 3 tables that make the application work (not counting users and user roles).
The first table is the members table that simply contains a list of members, their address, what precinct they live in, gender and race info, and contact information:
The second is the attendance table where members are marked as present and if they plan to attend future events (alternate).
The third is the absence table where members can provide an excused absence for not attending the event if needed.
The question I have is based in the members table. An example screenshot is shown below.
The data inside the table is currently set to contain the entire member’s name. I would have to split it up as last_name , first_name , middle_name , and suffix (Row 19 has an example of the suffix column I would need. Not all members have a suffix in their name, but it is still necessary as some of them do.)
I am coding this application in PHP 7 and MySQL. I don’t necessarily have to do it throughout the entire table in phpMyAdmin . I only need the name to be split in an excel file I am trying to generate. This is actually my preferred method since my attendance form on another webpage is grabbing the member’s full name.
Here is my current php code that generates the Excel file. Some lines are commented out as I am trying to figure out why the SQL statement isn’t working.
<?php
require_once('../../vendor/autoload.php');
require_once('../dbconnect.php');
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
//Delegates Worksheet
$spreadsheet = new Spreadsheet();
$excel_writer = new Xlsx($spreadsheet);
$spreadsheet->setActiveSheetIndex(0);
$active_sheet = $spreadsheet->getActiveSheet()->setTitle("Delegates");
$active_sheet->setCellValue('A1', 'Precinct');
$active_sheet->setCellValue('B1', 'Member Name');
$active_sheet->setCellValue('C1', 'Residential Address');
$active_sheet->setCellValue('D1', 'Member Email');
$active_sheet->setCellValue('E1', 'Phone Number');
//$active_sheet->setCellValue('F1', 'Ethnicity');
//$active_sheet->setCellValue('G1', 'Gender');
//$active_sheet->setCellValue('H1', 'Party');
$sql = "SELECT precinct, name, residential_address, member_email, member_phone, present, alternate FROM attendance INNER JOIN members ON members.name = attendance.member_id WHERE present = 1 ORDER BY members.precinct";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$i = 2;
while($row = $stmt->fetch()) {
/*$active_sheet->setCellValue('A' . $i, $row['precinct']);
$active_sheet->setCellValue('B' . $i, $row['member_name']);
$active_sheet->setCellValue('C' . $i, $row['residential_address']);
$active_sheet->setCellValue('D' . $i, $row['member_email']);
$active_sheet->setCellValue('E' . $i, $row['member_phone']);*/
//$active_sheet->setCellValue('A' . $i, $row['precinct']);
//$active_sheet->setCellValue('A' . $i, $row['precinct']);
//$active_sheet->setCellValue('A' . $i, $row['precinct']);
$i++;
}
//Alternate Delegates Worksheet
$spreadsheet->createSheet();
$spreadsheet->setActiveSheetIndex(1);
$active_sheet2 = $spreadsheet->getActiveSheet()->setTitle("Alternate Delegates");
$active_sheet2->setCellValue('A1', 'Precinct');
$active_sheet2->setCellValue('B1', 'Member Name');
$active_sheet2->setCellValue('C1', 'Residential Address');
$active_sheet2->setCellValue('D1', 'Member Email');
$active_sheet2->setCellValue('E1', 'Phone Number');
//$active_sheet->setCellValue('F1', 'Ethnicity');
//$active_sheet->setCellValue('G1', 'Gender');
//$active_sheet->setCellValue('H1', 'Party');
$sql = "SELECT precinct, name, residential_address, member_email, member_phone, present, alternate FROM attendance INNER JOIN members ON members.name = attendance.member_id WHERE alternate = 1 ORDER BY members.precinct";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$i = 2;
while($row = $stmt->fetch()) {
$active_sheet2->setCellValue('A' . $i, $row['precinct']);
$active_sheet2->setCellValue('B' . $i, $row['member_name']);
$active_sheet2->setCellValue('C' . $i, $row['residential_address']);
$active_sheet2->setCellValue('D' . $i, $row['member_email']);
$active_sheet2->setCellValue('E' . $i, $row['member_phone']);
//$active_sheet->setCellValue('A' . $i, $row['precinct']);
//$active_sheet->setCellValue('A' . $i, $row['precinct']);
//$active_sheet->setCellValue('A' . $i, $row['precinct']);
$i++;
}
//Generate Excel Workbook
$filename = date("Y") . ' ' . 'County Information Spreadsheets.xlsx';
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $filename);
header('Cache-Control: max-age=0');
$excel_writer->save('php://output');
/*
$sql = "SELECT precinct, name, residential_address, member_email, member_phone, present, alternate FROM attendance INNER JOIN members ON members.id = attendance.member_id WHERE present = 1 ORDER BY members.precinct";
$stmt = $pdo->prepare($sql);
$stmt->execute();
*/
?>
If that’s possible, I would love to learn how to do it. If not, what’s another way I can get a similar result?