Jump to content
phppup

multiple select statements

Recommended Posts

It's been a fun week of learning while battling coding problems.  So now, I need advice for the challenge..

The objective:  SELECT *FROM table WHERE moneyisdue != 0 AND x30daynotice = ' ' ;  then, send email saying "PAY ME NOW" and

UPDATE table SET x30daynotice = 'YES' WHERE moneyisdue != 0 AND x30daynotice = ' ' ;   then

SELECT *FROM table WHERE x30daynotice = 'YES ' AND x10daynotice = ' ' ;   then, send email with LEGAL THREAT and

UPDATE table SET x10daynotice = 'YES' WHERE moneyisdue != 0 AND x10daynotice = ' ' ;

My initial desire was to create a single script and run it daily to remain current.

Naturally, this is where I ran into trouble.

I've devised a simple solution of running 2 separate files: one for 30 day and one for 10 day.

But the question remains, from a PHP perspective, is a SINGLE file more efficient? 

And if so, can you provide an outline that I can use for guidance?

 

Note: I am ONLY using "YES" in the table for testing purposes and immediacy.  In actuality, a timestamp or date mechanism will be implemented. 

Right now I am just interested in having the 10 day notice follow the 30 day notice effectively.

Edited by phppup

Share this post


Link to post
Share on other sites

Now we have seen your proposed solution, what is the problem you are trying to solve?

Share this post


Link to post
Share on other sites
44 minutes ago, phppup said:

But the question remains, from a PHP perspective, is a SINGLE file more efficient? 

Probably, but so minor it doesn't matter.  More important is reducing duplicated script.  Problem when you do is you change one but forget to change the other.  You can create a single function and then have both call that function.

Share this post


Link to post
Share on other sites

My problem is in establishing SQL1 and SQL2.

I've seen different examples with conflicting information.

Can I name them SQL1 and SQL2?  Or return_1 and return_2?  This effort seems to fail in all efforts.

Using just SQL twice seems to only run the FIRST of the SQL statements.

And, yes, my email is in two separate functions depending on whether the 30_day or 10_day is in effect per SQL statement.

So, as it stands now, SQL statement equals 30_day function criteria, and all is well.

If I were live I'd now have 18 days to work out the 10_day trigger (or simply duplicate the 30_day script with 10_day criteria which, at the very least, adds a duplicate file to my storage).

How do I include the second SQL without effecting the first?

 

Share this post


Link to post
Share on other sites

I really don't understand what you are trying to do.  Are you invoking these scripts using a CRON?  Back to Barand's earlier post, start over and explain what you are trying to do.

Share this post


Link to post
Share on other sites
1 hour ago, phppup said:

My problem is in establishing SQL1 and SQL2.

No that isn't your problem - that is how you are trying to solve your problem. We need to know the business problem. What are the 30 and 10 days relative to?

(Unless those 30 and 10 days are relative to the same date for everyone, then tomorrow you need another two files, and the day after another two, and so on ad absurdum.)

Share this post


Link to post
Share on other sites

I don't know if this is close to your situation. Consider this scenarion.

We have a set of customer accounts

account table

+------------+-----------+-------------+
| account_id | name      | email       |
+------------+-----------+-------------+
|          1 | Company A | a@gmail.com |
|          2 | Company B | b@gmail.com |
|          3 | Company C | c@gmail.com |
+------------+-----------+-------------+

 

When they buy from us we raise invoices and store the dates and amounts

payment_due table

+----------------+------------+------------+------------+--------+
| payment_due_id | account_id | invoice_id | due_date   | amount |
+----------------+------------+------------+------------+--------+
|              1 |          1 |        101 | 2018-04-15 | 100.00 |
|              5 |          2 |        102 | 2018-04-15 | 200.00 |
|              2 |          1 |        103 | 2018-05-15 | 100.00 |
|              4 |          3 |        104 | 2018-06-01 | 300.00 |
|              3 |          1 |        105 | 2018-06-15 | 100.00 |
+----------------+------------+------------+------------+--------+

 

The customers make payments (hopefully) against these invoices

payments_recd table

+-----------------+------------+------------+------------+---------+
| payment_recd_id | account_id | invoice_id | recd_date  | payment |
+-----------------+------------+------------+------------+---------+
|               1 |          1 |        101 | 2018-04-15 |  100.00 |
|               5 |          2 |        102 | 2018-06-15 |  150.00 |
|               4 |          3 |        104 | 2018-06-10 |  300.00 |
+-----------------+------------+------------+------------+---------+

 

After 30 days we want to sent a reminder that any unpaid moneys are due. After 60 days we send the "red frightener" letters. So a single query like this will show the overdue payments.

        SELECT a.name
             , a.email
             , d.due_date
             , d.invoice_id
             , due
             , IFNULL(paid, 0) as paid
             , due - IFNULL(paid, 0) as owed
             , DATEDIFF(CURDATE(), d.due_date) as days_overdue
        FROM
            account a
            INNER JOIN
                (
                    SELECT account_id
                         , invoice_id
                         , due_date
                         , SUM(amount) as due
                    FROM  payment_due
                    GROUP BY account_id, invoice_id
                ) d USING (account_id)
            LEFT JOIN
                (
                    SELECT account_id
                         , invoice_id
                         , SUM(payment) as paid
                    FROM  payment_recd
                    GROUP BY account_id, invoice_id
                ) p
                    ON d.invoice_id = p.invoice_id
         WHERE
                due_date <= CURDATE() - INTERVAL 30 DAY
                AND due > IFNULL(paid,0)


        
 Query results
 

+-----------+-------------+------------+------------+--------+--------+--------+--------------+
| name      | email       | due_date   | invoice_id | due    | paid   | owed   | days_overdue |
+-----------+-------------+------------+------------+--------+--------+--------+--------------+
| Company A | a@gmail.com | 2018-05-15 |        103 | 100.00 |   0.00 | 100.00 |           33 |
| Company B | b@gmail.com | 2018-04-15 |        102 | 200.00 | 150.00 |  50.00 |           63 |
+-----------+-------------+------------+------------+--------+--------+--------+--------------+

(You would also need to record when the two types of email were sent and use this information to prevent multiple copies being sent to customers.)

  • Like 1

Share this post


Link to post
Share on other sites

That's the idea of what I want to do, but not quite as complicated. At least not yet.

At this point I wanted the script to run through the table and send and email to those 30-day customers and a different email to the 10-day customers.  

My approach was to SELECT those that were at the 30-day mark and use a second SELECT  to grab the 10-day folks.  And yes, I was coordinating it so as not to 'step on my own feet' in the process.  As two separate scripts, the goal is achieved.  But when I tried to combine the two I ran into conflicts.

I had thought I could somehow control them by referencing them as variable ten_day and thirty_day (or something in that vain) but it was not as simple as I had hoped.

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.