Spyder Tech Spyder Tech - 3 months ago 9
MySQL Question

Prepared Statement Not Fetching All Results

I have a prepared statement which is grabbing rentals in a storage facility and putting them in a list to keep track of in the back end when they are coming up due. The problem I am having is that when the statement outputs the list, if more than one person has the same number of days until they are due, the list only shows one of those renters. I have this is my prepared statement:

SELECT rentals.rental_startdate,
rentals.rental_renewdate,
rentals.rental_duedate,
rentals.rental_latedate,
rentals.rental_status,
rentals.rental_balance,
units.unit_id,
units.unit_number,
units.unit_status,
users.user_id,
users.user_fname,
users.user_lname,
@var := DATEDIFF(rentals.rental_renewdate,CURRENT_DATE) AS days
FROM rentals
INNER JOIN units ON units.unit_id = rentals.unit_id
INNER JOIN users ON users.user_id = rentals.user_id
WHERE rentals.rental_status=?
GROUP BY rentals.rental_renewdate HAVING days>=0 AND days<=?
ORDER BY rentals.rental_renewdate ASC


The issue seems to be coming from the
HAVING days>=0 AND days<=?
. The days are equal to the notification period which is defined in a variable as 5 days. When I take that off I see everyone who us coming up due whether their rent is due in 5 days or 10 which we don't really want but it fixes the problem of only seeing one person who is going to be due in 3 days when there are really 2. Does anything look out of place that would cause this issue? I'm using
while($select -> fetch()) {
to select the rows.

Answer

You need to add name data to your GROUP BY clause.

GROUP BY users.user_fname, users.user_lname, rentals.rental_renewdate

Should do the trick