Split up a column into multiple columns in MySQL database?

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?

you’re joining on the wrong columns

FROM attendance INNER JOIN members 
ON members.name = attendance.member_id 

sometimes all it takes is a fresh pair of eyes, eh

as for splitting up the name column, do that in php, where it’ll be a lot easier than in sql

That’s what I’m trying to do:

How would I be able to do that?

The problem with this is the following response: Define a suffix in a machine-understandable way.

What do you mean?

Okay. Let me try to explain what i mean.
You tell me, what rules you apply, to determine the First Name, Last Name, Suffix, and Middle names, of the following names:
“Abercrombie, Noah Thomas”
“Smith, Mary Jane Kennedy”
“Johnson, Tom Jr”

We can recognize a name suffix, I think for us that’s because we remember the ones we’ve seen and in context any not yet known can be assumed. (eg. never seen a “III” before, but knowing “II” figure it is “the third”).

I suppose you could add a “dictionary” of sorts that included all possible variations. Or maybe just split on the last space. Or maybe a regex pattern.

Each has pros and cons and depends largely on the source data.

“Jr”
“JR”
“Jr.”
“II”

Split on the last space - well, if you DONT have a suffix, suddenly your suffix is your middle name. Or your first name.

1 Like

The dangers of exceptions. Similar but related:

i’m pleased everyone sees that splitting the column is highly problematic

remember, if you do come up with a good algorithm, it’ll be easier to implement in php than sql

That’s what I am trying to figure out. If I can split it up using PHP rather than MySQL, how do I do it?

I’m gonna have to split the names up anyway, so I either need to find out how to do it in PHP, or I need to completely reconfigure 50K records…

We’re trying very politely to tell you you’re going to have some manual steps.

You can easily separate the last name. (“Everything before the comma is the last name.”)
The other 3 parts are the problem. There are no simple rules for those.

You can pitch some ‘rules’, but they will have mop up.

For example:
For everything after the comma:
Everything before the first space is the first name.
Everything else is the middle name

And for most people, that would probably do.

You’d then have to manually examine for suffixes, or for multi-word first names. (“Mary Jo Smith” would like a word with your rules…)

I see. My apologies if I came across as frustrated. That wasn’t my intention.

You are saying that it is possible, but not recommended for my situation… I presume?

If the data is limited in what it can be before being INSERTed (eg. drop down selects instead of text inputs) then it makes coding easier but not as friendly to users (eg. first and last name, but that’s it, nothing else) what does “Dr. John Franklin Smith Jr.” do, would he mind being “John Smith”?

I think what I’ve seen most often are forms where some inputs are required and others are optional. eg. I can say I’m a “Sr.” if I am.

If you can devise a workable compromise it should be possible to get in front of problem data before it gets created.