PHP MYSQL查询在包含值的列中返回null

Question

I'm using PHP Version 7.0.32 and MySQL 5.6

I created a simple query generator that determine how to build a query based on the parameters given. I'm using PDO to connect to my DB and execute queries. The queries themselves are coming out fine, and I will provide samples below. When I run the queries in the database directly I get the proper returned results. However, whenever I run the code and query the DB through PHP, it seems like all the column values that do not have a Collation set become NULL.

The confusing part is that this only happens with one specific query. All of my other queries return the proper values in the columns, and one of the other queries includes the result set of the improperly working query but is properly giving the values in this case.

Working Queries:

SELECT * FROM fm_submission AS sub WHERE sub.archived = :archived ORDER BY sub.dateSubmitted LIMIT :start, :limit;

SELECT * FROM fm_submission AS sub LEFT JOIN fm_submission_saved AS saved ON sub.submissionID = saved.submissionID WHERE sub.archived = :archived AND saved.submissionID IS NOT NULL ORDER BY sub.dateSubmitted LIMIT :start, :limit;

SELECT * FROM fm_submission AS sub WHERE sub.archived = :archived ORDER BY sub.dateSubmitted;

Not Working Query:

SELECT * FROM fm_submission AS sub LEFT JOIN fm_submission_saved AS saved ON sub.submissionID = saved.submissionID WHERE sub.archived = :archived AND saved.submissionID IS NULL ORDER BY sub.dateSubmitted LIMIT :start, :limit;

A proper result set returns close to as follows:

["formID"]=> string(1) "1" ["personnelID"]=> int(0) ["storeName"]=> string(6) "Albany"

To improper result set for the one query not working is as follows:

["formID"]=> string(1) "1" ["personnelID"]=> NULL ["storeName"]=> string(11) "River Ridge"

If researching the issue I found that this can happen when there is an issue with the Collation and php charset not working together. I have made sure that all are set to be the same, however I can't do that for types like INT, FLOATS, DATE, etc.

I would appreciate in any help on the topic and any solutions even more. Ask if there is any other information I can provide to help.

Answer 1

I found the problem, which turned out to be a small problem that didn't report an error.

SELECT * FROM fm_submission AS sub LEFT JOIN fm_submission_saved AS saved ON sub.submissionID = saved.submissionID WHERE sub.archived = :archived AND saved.submissionID IS NULL ORDER BY sub.dateSubmitted LIMIT :start, :limit;

The issue with the above query was the ambiguous nature of the * in the SELECT.

SELECT sub.* FROM fm_submission AS sub LEFT JOIN fm_submission_saved AS saved ON sub.submissionID = saved.submissionID WHERE sub.archived = :archived AND saved.submissionID IS NULL ORDER BY sub.dateSubmitted LIMIT :start, :limit;

Adding the sub reference to the * allowed the data to pull from the proper place and return that data. I'm not sure as to why there wasn't an error and some of the data was returned without issue. I'd be interested in learning why it partially worked, instead of failing or passing.