I have a table called "customers". This has all the customer information, such as billing and service addresses and other information about the customer.
I also have a table called "customerservicedate". This table holds all service data that is done to any set of customers. Basically, it has a CustomerID (which is tied in with the customers table), and also has information such as service notes (in this case how much a customer has paid), the amount the service costed the customer, and if the service was provided, and if it was paid for, among other things.
This is where I'm having some issues. I think I might have the SQL syntax pretty much correct, however, that's 1/2 of the issue:
SELECT * FROM customerservicedate INNER JOIN customers ON customerservicedate.Custom
erID = customers.CustomerID WHERE ServiceDate <= NOW() AND ServiceProvided='Yes' AND ServicePaid='No';SELECT * FROM customerservicedate INNER JOIN customers ON customerservicedate.Custom
erID = customers.CustomerID WHERE ServiceDate <= NOW() AND ServiceProvided='Yes' AND ServicePaid='No';
This code DOES do what it is suppose to, however, it is a little slow - but it DOES return a good 3200 rows of information. This is for a report though, so that is okay, since it's not like its going to be used every day - its more of a monthly type ordeal. Let me know if I'm doing this query correct or not (as in if there are better ways about going about it).
So, what I'm actually trying to do is basically get a list of all the customers that have had service (up until the current date) and have not paid, then I need to join the customers table, so that I can get the Service Address/Billing address, etc. That's what the above statement does. Now, my problem is getting this to output correctly for PHP.
I REALLY need help on the PHP logic. I'm basically invoicing these, for late payments, and output to PDF using fPDF. I'm getting the hang of fPDF, however, my issue is this: I need to be able to loop through the SQL query and output everything that is late for each customer, and loop through for each instance. So basically run the query, and then output "Customer 1" and each result from the database, and then it loops again, and does "Customer 2", and each result from the database that pertains from this customer, and so on, until its all done. So what is unique would be the CustomerID.
Here's the loop, but I don't think it covers what I really need - I belive I might need a for each loop, and that I need help with:
$sql = mysql_query("SELECT * FROM customerservicedate INNER JOIN customers ON customerservicedate.Custom
erID = customers.CustomerID WHERE ServiceDate <= NOW() AND ServiceProvided='Yes' AND ServicePaid='No';SELECT * FROM customerservicedate INNER JOIN customers ON customerservicedate.Custom
erID = customers.CustomerID WHERE ServiceDate <= NOW() AND ServiceProvided='Yes' AND ServicePaid='No';");
$num = mysql_num_rows($sql);
$i=0; // row count
while ($i < $num) {
$row = mysql_fetch_assoc($sql);
// this is where each output will happen
$i++
}
I'm not sure if a while look is what I want, or if I should have a while loop, but also have a foreach loop inside of it. If anyone could help out, that would be great.
