PHPSpreadsheet not getting data from SQL Query?

I am trying to generate a report in an Excel spreadsheet. I looked up and read about using PHPSpreadsheet so, I downloaded and installed Composer in my XAMPP project directory in order to use it.

I got the code working far enough to generate the file as I need to. However, there is no data being obtained from the file other than the headers I define in the code. Here is an example:

First, here is my current code as it stands:

<?php

require_once('../../vendor/autoload.php');
require_once('../dbconnect.php');

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$excel_writer = new Xlsx($spreadsheet);

$spreadsheet->setActiveSheetIndex(0);
$active_sheet = $spreadsheet->getActiveSheet();

$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();

if($stmt->num_rows > 0) {
	$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++;
	}
}

$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();
*/

?>

My current table structures are as follows:

Members:

Attendance:

Am I doing something wrong?

Must be, if there is data but your code is not extracting it or outputting it to the spreadsheet.

First thing is to find out which is failing - is it the query to retrieve the data from your table, or is it the code that inserts it into the spreadsheet? Change your code to just echo the query results for now, see if it brings up any data.

I’m not sure your query syntax is correct, does it work if you execute it directly in whatever your equivalent of phpmyadmin is?

Is this correct?

if($stmt->num_rows > 0) {

I thought it was rowCount() in PDO, and then only if the driver supports it? Easier surely to just have the while() loop, as it won’t execute if there are no results.

Apparently not. Here is the output from phpMyAdmin:

So just remove if($stmt->num_rows > 0) { entirely? I can use rowCount() if that would help out, but I can do either one - whichever works best and is the most necessary for this situation.

For some reason, I keep forgetting how to do that. I know how to prepare and execute the query, but I am not sure how to print out the results (if any are found.) I thought that you can use fetch() or fetchAll() to retrieve your results (though I don’t know what the difference is), but if the results come back as an array, you would use print_r() since the result is not a string.

Based on my posted screenshot, I believe the query might be incorrect. It is not bringing back any results and there should be at least 5 results since it is (supposed to be) pulling information from the attendance table. My phpMyAdmin shows that my attendance table has 5 records in it.

num_rows is from mysqli, so you can’t use that in PDO. Remove the opening and closing lines of that if clause. The while loop will only execute if there are results.

Inside your loop, instead of calling the functions that stick the results into the spreadsheet, just echo them. For testing, you only have to echo one field, you just want to know whether something is coming out of the query.

And do they all have “1” in the present column? Does the member_id in attendance match the member_name field in members? Why are you matching the members.name, and not the members.id?

There is no data shown on the webpage, so there is no data being put in.

$i = 2;
while($row = $stmt->fetch()) {
	echo $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++;
}

Yes. The present column is required in the webform associated with the table, so it cannot be empty.

Because the query is not collecting the member’s id. I built a webpage that views the attendance table to the admin user. If I replaced the member’s name with the member’s ID number, the user will not understand the table because rather than seeing the name of the user, they will see the member’s ID number.

Now, if I need to, I can write out an SQL statement that says to select the member’s name if their ID is equal to the id column in the members table. This way, the user won’t be confused on why there is a number rather than a person’s name.

Yes it does, since the member_id field data is the member’s name.

The first thing to do is to get the query working in phpmyadmin, once you have that sorted then the rest should be OK.

I meant

echo $row['precinct'];

not echo whatever the setCellValue() function returns, which may be nothing.

Then I’d suggest you use a different column name. Calling it “something_id” when it doesn’t contain an id is confusing, and with no apparent advantage.

Here is my current SQL statement:

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

I’m not sure why it would return blank?

I agree, it is confusing, but I can’t change the column name since I get an undefined index error on a defined index. It’s a whole different story…

Also, I need to split the data from the name column in the members table. Is that possible or do I have to change the data and split up all of the names manually through an Excel spreadsheet and turn it back into an SQL file? I have a lot of records to do and I want to try and make that process easier.

You mean split it into separate columns for surname and first name? If there’s a rule, then you could do it as a query or a short bit of PHP code. For example if the format is always surname, followed by a comma, followed by the first name, then you could write a short bit of PHP to read every row, split the names, and write it back. There may be a way to do that in a query, ask in the “Databases” area as that wouldn’t be PHP related.

Either your WHERE condition is not finding any rows that have present = 1, or your INNER JOIN cannot match the two fields. Keep in mind that an INNER JOIN will only return rows where there is a match in both tables, so if you have managed to get a value in attendance.member_id that does not exist anywhere in your members table in the name column, you won’t get that row returned. This is why I queried whether you shouldn’t be matching to members.id (which I still think you should be doing, as I said elsewhere at length).

It’s very difficult to guess why it’s returning nothing without seeing your data, especially given the confusing column names. You could blur some of it if you are concerned about privacy issues, as long as you leave enough to actually diagnose with.

That would seem like a case for removing the index, renaming the column and then reinstating the index, but I am not a database expert by any means, and there may be more to it.

1 Like

Well, it used to that anyway. I worked on it and changed the column name from member_id to member_name and changed everything around in the form and api files and it works now. Not sure what I was doing wrong earlier…

Well, sort of. Here’s an example of my data:

The data 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 don’t necessarily have to do it throughout the entire table in phpMyAdmin. I only need the name to be split in the 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.

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?

IMHO, the database needs attention. (eg. ALTER)

But I know some desktop spreadsheet apps have “formulas” and the possibility to add “macros”.

I think there should be some here that could help if you would prefer to start a topic in #general-web-dev

I’ve created the topic here.

I’ve managed to correct the SQL statement and change a few things around in other parts of my code.

The sheet now opens and everything is visible, but it opens on the wrong spreadsheet.

How can I tell the program to open to the Delegates worksheet rather than the Precinct Officers worksheet?

<?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.id = 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['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.id = attendance.member_id WHERE alternate IS NULL 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['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++;
}

// County Officers Worksheet
$spreadsheet->createSheet();
$spreadsheet->setActiveSheetIndex(2);
$active_sheet3 = $spreadsheet->getActiveSheet()->setTitle("County Officers");

$active_sheet3->setCellValue('A1', 'Position');
$active_sheet3->setCellValue('B1', 'Officer Name');
$active_sheet3->setCellValue('C1', 'Residential Address');
$active_sheet3->setCellValue('D1', 'Member Email');

// Precinct Officers Worksheet
$spreadsheet->createSheet();
$spreadsheet->setActiveSheetIndex(3);
$active_sheet4 = $spreadsheet->getActiveSheet()->setTitle("Precinct Officers");

$active_sheet4->setCellValue('A1', 'Position');
$active_sheet4->setCellValue('B1', 'Officer Name');
$active_sheet4->setCellValue('C1', 'Residential Address');
$active_sheet4->setCellValue('D1', 'Member Email');

//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');

?>

image

Does this bit of code

$active_sheet = $spreadsheet->getActiveSheet()->setTitle("Delegates");

just set the title, or does it also return a reference to the sheet? As you seem to be using the return from it to then post your values in various sections of the code, could that be the issue?

According to PHPSpreadsheets, this is what the manual says:

Alternatively, one worksheet is always the currently active worksheet, and you can access that directly. The currently active worksheet is the one that will be active when the workbook is opened in MS Excel (or other appropriate Spreadsheet program).

// Retrieve the current active worksheet
$spreadsheet->getActiveSheet();

You can change the currently active sheet by index or by name using the setActiveSheetIndex() and setActiveSheetIndexByName() methods.

EDIT:

Looks like this bit of code will do the job!

$spreadsheet->setActiveSheetIndexByName("Delegates");

image

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.