How to return a recordset with multiple JOINS where a row may not exist in a table
All users have 1 or more lots. So when a user logs in, they will see the amount due for each of their lots. A user can have multiple lots so i have a lots table linked to the user via a owners table as each lot can also have multiple owners. (i have these results in a repeat region). This works as expected for every user.
Each lot can have additional fees associated with it. (late fees, non compliance penalties, etc) These can be multiple so i have them in their own table. (also, within that repeat region)
When logged is as an owner with fees associated, the page works as expected as I am able to echo the values from the DB and repeat this for each lot.
However, when a user logs in that has NO FEES, the code breaks where we try to echo the recordset with fees.
Both return correctly if there is a row in the fees table. However, if no row for that user in the fees table, the rest of the code breaks after the arrears echo.
I don't know how to write this so that if there is no fees row, that we simply ignore that part. I tried using an if/else statement but i am not formatting it correctly I cannot get results without errors.
I need your advice. I am sure this could be handled better/differently?
Once I solve this, I need to be able to add all of this together into one total balance. Somehow need to SUM all the lots dues + all arrears + all fees including any that are repeating due to having multiple lots.
Advice on that as well is appreciated!
Here's the queries:
I have this query which returns the data of the lot of the user that logs in. It works as expected. For context, it returns the amount of the dues owed for that lot each year.
$rsLots = new WA_MySQLi_RS("rsLots",$gm,0);
$rsLots->setQuery(" SELECT * FROM lots JOIN lotOwners ON lots.lotNumber = lotOwners.lotNumber LEFT JOIN users ON lotOwners.ownerID = users.UserID WHERE userID = ?");
$rsLots->bindParam("i", "".($_SESSION['UserID']) ."", "-1"); //colname
I have this second query that displays any fees this user may have. It works as expected. (but only if the user has a row in the 'fees' table).
$rsFees = new WA_MySQLi_RS("rsFees",$gm,0);
$rsFees->setQuery(" SELECT amtDue FROM fees JOIN lotOwners ON fees.lotNumber = lotOwners.lotNumber JOIN users ON lotOwners.ownerID = users.UserID WHERE userID = ?");
$rsFees->bindParam("i", "".($_SESSION['UserID']) ."", "-1"); //colname
I've tried to combine these into 1 recordset as follows but have the same issue (which I can understand in this case).
$rsTotal = new WA_MySQLi_RS("rsTotal",$gm,0);
$rsTotal->setQuery("SELECT lots.arrears AS arrears, lotOwners.lotNumber AS lotNumber, fees.amtDue AS fees
JOIN lotOwners ON lotOwners.lotNumber = lots.lotNumber
JOIN fees ON fees.lotNumber = lotOwners.lotNumber
JOIN users ON lotOwners.ownerID = users.UserID
WHERE userID = ?");
$rsTotal->bindParam("i", "".($_SESSION['UserID']) ."", "-1"); //colname