Send mail to users with unpaid orders php

Question

I have a custom php eCommerce website.

In which i have two tables named order & users.

Now i need to send email to those users whose order state is 0.

user id is stored in users table.

Here is my php code :

<?php
   $query = mysqli_query($link, "SELECT * FROM dg_order WHERE state=0");
    while ($row   = mysqli_fetch_assoc($query)) {
        $uid   = $row['uid'];
        $users = mysqli_query($link, "SELECT * FROM dg_users WHERE uid='$uid'");
        while ($rw    = mysqli_fetch_assoc($users)) {
            $em = $rw['email'];

            mail($em, $subject, $message, $headers);
        }
    }

But it sends email to one user several times according to number of unpaid orders. How can i send email to those who have unpaid orders only once and also how can I send list of unpaid orders in email too?

First, see JOIN. If you're still struggling, consider providing proper CREATE and INSERT statements TOGETHER WITH a desired result. — Strawberry, Jul 05 '16 at 07:05
Please take a look here http://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join?lq=1. About joining table — YVS1102, Jul 05 '16 at 07:07
you so if you know can you give me the code to achieve this? — Rohit Kishore, Jul 05 '16 at 07:12

score 1 · Answer 1 · answered Jul 05 '16 at 07:14

Note, this will only send each user one email, regardless of how many outstanding orders they may have...

 $query = "
  SELECT DISTINCT u.email
             FROM dg_order o
             JOIN dg_users u
               ON u.uid = o.uid
            WHERE o.state = 0;
            ";

 $result = mysqli_query($link,$query);

    while($row = mysqli_fetch_assoc($result))
    {
        $em = $row['email'];
        mail($em, $subject, $message, $headers);
    }

score 0 · Accepted Answer · answered Jul 05 '16 at 07:13

0

You can modify your code using IN and DISTINCT as

$users = mysqli_query($link, "SELECT email FROM dg_users WHERE uid IN(SELECT DISTINCT uid FROM dg_order WHERE state=0)");
while ($rw = mysqli_fetch_assoc($users)) {
    $em = $rw['email'];
    mail($em, $subject, $message, $headers);
}

answered Jul 05 '16 at 07:13

Saty

22,443
7
33
51

but potentially inefficient – Strawberry Jul 05 '16 at 07:18
Yep join is most efficient then IN query – Saty Jul 05 '16 at 07:18
`WHERE uid IN(SELECT ` is not inefficient way – Maksym Semenykhin Jul 05 '16 at 11:02

Send mail to users with unpaid orders php

2 Answers2