Email the results of a database query

Wappler General
#1

I am trying to email the results from a database query.

I create a Server Action
Add a database connection
Add a database query
Add setup mailer
Add a repeat
Inside the repeat i add my Send Mail.

It email s me but with as many emails as there are records, so i get 29 emails if it matches 20 results, even though i have only setup static content in the email for testing purposes, i still get 20 emails.
If i take the send mail out of the repeat though then i cant access the dynamic data returned by the query so I am stuck with it in the repeat step.

Please let me know if i am doing this incorrectly.

Repeat in eMail body
A Table Repeat In An HTML Email
2018: The Year in Review
#2

Hi Paul,
I don’t really understand what exactly is wrong?
What are you trying to achieve? What are the expected results and what happens actually?

#3

Hi Teodor,
I have an invoicing system, where a user places an order for say
1 x sunglasses
AND
2 x hats

When they click order on the product it adds a record to my database, with an order number. So for the above example my database is now populated with 2 rows
Row 1 with a quantity of 1, a product description of sunglasses, and an order number of 5555
Row 2 with a quantity of 2, a product description of hat, and an order number of 5555
As you can see both order numbers match each other.

Now the user needs to go to the checkout stage, so i display all products ordered that have an order number of 5555, and that all works fine, but now i want to email the user with what they have just purchased.

I create a query to my checkout table, and return 2 results that both match order number 5555, i want to send both results in a single email to a single address.

Hope that makes more sense. Here is a screenshot where i have tried to add the send mail component inside the repeat as well as outside the repeat, the one outside the repeat can not access the data returned by the query, but the one in the repeat can see the results correctly, but sends as many emails as results retuned by the query.

12
Screen Shot 2018-07-17 at 18.54.12.png361×1366 64.6 KB

56
Screen Shot 2018-07-17 at 18.56.56.png482×1298 75.5 KB

#4

If you put the send email step in a repeat which repeats your products, it will send as many emails as your products are.
If you have 10 products and repeat them, then it will send 10 emails.
What are you trying to achieve excatly?

#5

i would like a single email with the results of my database query as the final result, if you prefer i can PM you login details to the testing environment, or the file from Wappler with supporting files.

#6

You cannot currently use repeat regions/queries inside mail body.

#7

Aha, ok thanks Teodor, I will try to get around it another way, thanks for letting me know, I hope one day this does become available.

#8

Repeating region in email body should be added as its really important
@psweb how about you add a new field in the order table to hold all the order details with HTML formatting? this will work in theory at least :slightly_smiling_face:

1 Like
#9

Thats kind of what i am trying, but instead i will email the user with a link to confirm their order, in that link will be a URL with the 5555 order number, then they click that link and open a page with the full invoice and click confirm and hopefully that i can get into a single email, well I am hoping, lol.
I might do a short video of what i am trying to do so Wappler users can try help with the extra info.

#10

You know you can do that with this code (add your table inside the email body)
<?php
// multiple recipients
$to = ‘aidan@example.com’ . ', '; // note the comma
$to .= ‘wez@example.com’;

// subject
$subject = 'Birthday Reminders for August';

// message
$message = '
<html>
<head>
  <title>Birthday Reminders for August</title>
</head>
<body>
  <p>Here are the birthdays upcoming in August!</p>
  <table>
    <tr>
      <th>Person</th><th>Day</th><th>Month</th><th>Year</th>
    </tr>
    <tr>
      <td>Joe</td><td>3rd</td><td>August</td><td>1970</td>
    </tr>
    <tr>
      <td>Sally</td><td>17th</td><td>August</td><td>1973</td>
    </tr>
  </table>
</body>
</html>
';

// To send HTML mail, the Content-type header must be set
$headers  = 'MIME-Version: 1.0' . "\r\n";
$headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";

// Additional headers
$headers .= 'To: Mary <mary@example.com>, Kelly <kelly@example.com>' . "\r\n";
$headers .= 'From: Birthday Reminder <birthday@example.com>' . "\r\n";
$headers .= 'Cc: birthdayarchive@example.com' . "\r\n";
$headers .= 'Bcc: birthdaycheck@example.com' . "\r\n";

// Mail it
mail($to, $subject, $message, $headers);
?>
1 Like
#11

Thanks, that is almost exactly what I had planned on doing, I will however try to still use their Send Mail component, but in the content area I will try to bring in my stored variable. Trying to get the job right but at the same time get as much done using only Wappler code as is possible to see if i can. The only thing i would prefer about using my own code like you example is that then i can filter it through the Akismet service too, I use it all the time to try reduce spam.

1 Like
#12

At least in wappler we can add whats missing. (i had a really bad previous experience with other ‘closed system’)

Good luck with your project :slightly_smiling_face:

1 Like
#13

Thanks haha, think I am going to need all the luck I can get.

#14

Ok quite stuck here, i do not think i explained this to @Teodor very well, maybe @George or @patrick can try assist if possible.

What i am trying to do is
Imagine I have a database with my data inside it, I create a database query through Server Connect and then use something like Bootstrap 4 table generator and preview my work in the browser.
That returns 20 rows of data on my screen.
Is there a way to have exactly what my browser is showing at that stage to be emailed to the user and in their email client they will see all the same data as what they would see in the web browser?

#15

Have you tried building the email as a page and sending using that as a template rather than editing the email right in the sendmail action?

Do this by choosing ‘File’ instead of ‘Static’ in the Sendmail action.

47%20PM
Screen Shot 2018-07-17 at 1.15.47 PM.png704×324 25.7 KB

#16

By the way I tried the php mail solution but cant get it to work as the data such as

Joe is really more like {{person_name}} which in a browser renders perfectly as Joe, but when this emails to me it comes out on the email as {{person_name}}

Thanks though I thought this would work too, but now that it hasn’t it kind of makes sense that it possibly wouldn’t work.

#17

Thanks @brad that I havn’t tried as yet, will give it a go and report back.

2 Likes
#18

@brad I just did a couple tests and that does not look like it will work too well either, the issue with this approach is that i need to add the query in the same server action as the Send Mail component, and the only way I can use the dynamic data returned by the query is if i add a repeat step, and that causes multiple duplicate emails to be sent. Unless I am thinking of this incorrectly?

#19

I don’t think you would need the query at all in the Action. Can’t you just add the query to the page itself?

#20

hmmm, interesting idea, so I will create a .html page, add the bootstrap and app connect to it, add a bootstrap 4 table generator to it, so i see everything i want in the file, then link that .php file as a file to my Send Mail step, have i understood the idea correctly?

1 Like