Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to send 2 select from mysql database in email like tables

Status
Not open for further replies.
ersatz

ersatz

Programmer
Oct 29, 2002
114
0
0
US
I need your help. I want to send an email with data from 2 selects, one with only one row and another with several rows (details table).
Tested separately, works very well, but it doesn't work when I want to put both results in the text message.

**********************************************************************
working
************************************************************************
<?php
include_once('z_connection.php');
$stmt = $conn->prepare("select * from batches where batch_id=2")
$stmt->execute();
$batches= $stmt->fetchAll();
foreach($batches as $batch)
{
?>
<tr>
<td><?php echo $batch['comp_id']; ?></td>
<td><?php echo $batch['description']; ?></td>
<td><?php echo $batch['quantity']; ?></td>
</tr>
<?php
}
?>
--------
mail($to, $subject, $message, $headers);

**********************************************************************
working
************************************************************************
<?php
include('mailer.php');
require '../../connect/config.php';
$pdo = new mypdo();
$mail = new mymailer();
?>
$message = '<html>
<head></head>
<body>
<table border="1" cellspacing="3" width="20%">
<tr><td>Date:</td><td>'.$date.'</td></tr>
<tr><td>User:</td><td>'.$c_user.'</td></tr>
<tr><td>Email:</td><td>'.$to.'</td></tr>
<tr><td>FormulaId:</td><td>'.$formula_id.'</td></tr>
<tr><td>Product:</td><td>'.$c_product.'</td></tr>
<tr><td>Batch Quantity:</td> <td>'.$c_pquant.'</td></tr>
</table>
</body>
</html>';


mail($to, $subject, $message, $headers);

Here 2 images with what I need
first_select_irrcxw.png
detail_crmmed.png
 
Sort by date Sort by votes
Hi

Hard to understand what are you doing there.
[ul]
[li]In the 1[sup]st[/sup] code how does the $message variable get its value ?[/li]
[li]In the 2[sup]nd[/sup] code how do the $date, $c_user, $to, etc variables get their values ?[/li]
[/ul]
Probably would be better to show us the code that is not working so we can tell what the problem is.


Feherke.
feherke.github.io
 
Upvote 0 Downvote
Hi, thank you for your answer. Here is the code . I don't know what type of connection to use to get data from the database.
In all my pages I have <?php
include('mailer.php');
require '../../connect/config.php';
$pdo = new mypdo();

If I use that for the second code, the loop is not working...

$headers .= "MIME-Version: 1.0" . "\r\n";
$headers .= "Content-type:text/html;charset=UTF-8" . "\r\n";
$headers .= 'From: Webpage <mailer@example.com>'. "\r\n";
//Multiple CC can be added, if we need (comma separated);
$headers .= 'Cc: admin@example.com' . "\r\n";
//Multiple BCC, same as CC above;
$headers .= 'Bcc: user1@example.com' . "\r\n";
$cntd = $pdo->get_one("SELECT u.fname, u.lname, u.email from users u join locations l on u.loc_id=l.loc_id where fname='contador' and l.loc_id='".$_POST['loc_id']."'");
$to=$cntd['email'];

$subject='New Batch Created';
$title='ECCA';


$date=date('Y-m-d');
$user= $pdo->get_one("SELECT * FROM users WHERE user_id='".$_SESSION['uid']."'");
$c_user=$user['fname'].' '.$user['lname'];
$product= $pdo->get_one("SELECT * FROM products WHERE prd_id='".$_POST['product_id']."'");
$c_product=$product['description'];
$c_pquant=$_POST['qty'];

$message = '<html>
<head></head>
<body>
<table border="1" cellspacing="3" width="20%">
<tr><td>Date:</td><td>'.$date.'</td></tr>
<tr><td>User:</td><td>'.$c_user.'</td></tr>
<tr><td>Email:</td><td>'.$to.'</td></tr>
<tr><td>FormulaId:</td><td>'.$formula_id.'</td></tr>
<tr><td>Product:</td><td>'.$c_product.'</td></tr>
<tr><td>Batch Quantity:</td> <td>'.$c_pquant.'</td></tr>
</table>
</body>
</html>';

mail($to, $subject, $message, $headers);
 
Upvote 0 Downvote
Hi

ersatz said:
$cntd = $pdo->get_one("SELECT u.fname, u.lname, u.email from users u join locations l on u.loc_id=l.loc_id where fname='contador' and l.loc_id='".$_POST['loc_id']."'");
Click to expand...
First of all, never user input data directly interpolated in SQL statement. That way you make your code vulnerable to SQL injection attacks. The Bobby Tables site explains the problem and the solution in simple terms.

Apparently there are user 2 completely different data access solutions. Without knowing what z_connection.php and ../../connect/config.php contains we can not know whether those two are connecting to the same database or not.

ersatz said:
If I use that for the second code, the loop is not working...
Click to expand...
Uhm... By "second code" you mean what in your 21 Jan post was first and in your 22 Jan post is missing ?

Well, about that one I asked "how does the $message variable get its value" ? there you are just writing the table HTML and the data to he output, but the $message variable, which you will send in the e-mail will not contain it. Assuming that your problem is around that variable and that question, I would try to change the code like one of these :
Code: 
[teal]<?php[/teal]
[red]include_once[/red][teal]([/teal][i][green]'z_connection.php'[/green][/i][teal]);[/teal]
[navy]$stmt[/navy] [teal]=[/teal] [navy]$conn[/navy][teal]->[/teal][COLOR=orange]prepare[/color][teal]([/teal][i][green]"select * from batches where batch_id=2"[/green][/i][teal]);[/teal]
[navy]$stmt[/navy][teal]->[/teal][COLOR=orange]execute[/color][teal]();[/teal]
[navy]$batches[/navy][teal]=[/teal] [navy]$stmt[/navy][teal]->[/teal][COLOR=orange]fetchAll[/color][teal]();[/teal]
[COLOR=orange]ob_start[/color][teal]();[/teal]
[b]foreach[/b][teal]([/teal][navy]$batches[/navy] [b]as[/b] [navy]$batch[/navy][teal])[/teal]
[teal]{[/teal]
    [teal]?>[/teal]
    [b]<tr>[/b]
        [b]<td>[/b][teal]<?php[/teal] [b]echo[/b] [navy]$batch[/navy][teal][[/teal][i][green]'comp_id'[/green][/i][teal]]; ?>[/teal][b]</td>[/b]
        [b]<td>[/b][teal]<?php[/teal] [b]echo[/b] [navy]$batch[/navy][teal][[/teal][i][green]'description'[/green][/i][teal]]; ?>[/teal][b]</td>[/b]
        [b]<td>[/b][teal]<?php[/teal] [b]echo[/b] [navy]$batch[/navy][teal][[/teal][i][green]'quantity'[/green][/i][teal]]; ?>[/teal][b]</td>[/b]
    [b]</tr>[/b]
    [teal]<?php[/teal]
[teal]}[/teal]
[navy]$messages2[/navy] [teal]=[/teal] [COLOR=orange]ob_get_clean[/color][teal]();[/teal]
[teal]?>[/teal]
Code: 
[teal]<?php[/teal]
[red]include_once[/red][teal]([/teal][i][green]'z_connection.php'[/green][/i][teal]);[/teal]
[navy]$stmt[/navy] [teal]=[/teal] [navy]$conn[/navy][teal]->[/teal][COLOR=orange]prepare[/color][teal]([/teal][i][green]"select * from batches where batch_id=2"[/green][/i][teal]);[/teal]
[navy]$stmt[/navy][teal]->[/teal][COLOR=orange]execute[/color][teal]();[/teal]
[navy]$batches[/navy][teal]=[/teal] [navy]$stmt[/navy][teal]->[/teal][COLOR=orange]fetchAll[/color][teal]();[/teal]
[navy]$message2[/navy] [teal]=[/teal] [i][green]''[/green][/i][teal];[/teal]
[b]foreach[/b][teal]([/teal][navy]$batches[/navy] [b]as[/b] [navy]$batch[/navy][teal])[/teal]
[teal]{[/teal]
    [navy]$message2[/navy] [teal].=[/teal] [i][green]'[/green][/i]
[i][green]    <tr>[/green][/i]
[i][green]        <td>'[/green][/i] [teal].[/teal] [navy]$batch[/navy][teal][[/teal][i][green]'comp_id'[/green][/i][teal]] .[/teal] [i][green]'</td>[/green][/i]
[i][green]        <td>'[/green][/i] [teal].[/teal] [navy]$batch[/navy][teal][[/teal][i][green]'description'[/green][/i][teal]] .[/teal] [i][green]'</td>[/green][/i]
[i][green]        <td>'[/green][/i] [teal].[/teal] [navy]$batch[/navy][teal][[/teal][i][green]'quantity'[/green][/i][teal]] .[/teal] [i][green]'</td>[/green][/i]
[i][green]    </tr>[/green][/i]
[i][green]    '[/green][/i][teal];[/teal]
[teal]}[/teal]
[teal]?>[/teal]
Then the other code could stay unchanged, just at the end send both variable's values : [tt]mail[teal]([/teal][navy]$to[/navy][teal],[/teal] [navy]$subject[/navy][teal],[/teal] [navy]$message[/navy] [teal].[/teal] [green]"\n"[/green] [teal].[/teal] [navy]$message2[/navy][teal],[/teal] [navy]$headers[/navy][teal]);[/teal][/tt] .


Feherke.
feherke.github.io
 
Upvote 0 Downvote
Thank you again, I really appreciate your help. Here is my script... I do not receive any email :-( .. I don't know what I am doing wrong.

<?php
session_start();
include('mailer.php');
$mail = new mymailer();

if (!isset($_SESSION['uid'])) die("Please login");
$uid = $_SESSION['uid'];
if (!isset($_SESSION['su'])) die("Please login");

require '../../connect/config.php';
$pdo = new mypdo();

if (isset($_GET['ch']) && $_GET['ch'] == "get_available_products") {
$prd_id = $_GET['prd_id'];
$loc_id = (int)$_GET['loc_id'];
$formulas = $pdo->get_all("SELECT b.formula_id, b.version, b.prd_id, c.min_solid_per, c.max_solid_per, c.min_ge, c.max_ge, c.min_ph, c.max_ph FROM formula_location a JOIN formulas b ON a.formula_id = b.formula_id LEFT JOIN products c ON b.prd_id = c.prd_id WHERE b.prd_id = ? AND a.loc_id = '$loc_id' AND b.status = 1", $prd_id);
}

if (isset($_POST['ch']) && $_POST['ch'] == "n_batch") {

$headers .= "MIME-Version: 1.0" . "\r\n";
$headers .= "Content-type:text/html;charset=UTF-8" . "\r\n";
$headers .= 'From: Webpage <mailer@example.com>'. "\r\n";
$headers .= 'Cc: user@example.com' . "\r\n";
//find acountant email for this location
$cntd = $pdo->get_one("SELECT u.email from users u join locations l on u.loc_id=l.loc_id where fname='contador' and l.loc_id='".$_POST['loc_id']."'");
$to=$cntd['email'];
$subject='New Batch Created';
$title='ECCA';

$date=date('Y-m-d');
//find who created the batch
$user= $pdo->get_one("SELECT * FROM users WHERE user_id='".$_SESSION['uid']."'");
$c_user=$user['fname'].' '.$user['lname'];
$product= $pdo->get_one("SELECT * FROM products WHERE prd_id='".$_POST['product_id']."'");
$c_product=$product['description'];
$c_pquant=$_POST['qty'];

$message = '<html>
<head></head>
<body>
<table border="1" cellspacing="3" width="20%">
<tr><td>Date:</td><td>'.$date.'</td></tr>
<tr><td>BatchCreatedBy:</td><td>'.$c_user.'</td></tr>
<tr><td>AccountantEmail:</td><td>'.$to.'</td></tr>
<tr><td>ProdId:</td><td>'.$prd_id.'</td></tr>
<tr><td>Product:</td><td>'.$c_product.'</td></tr>
<tr><td>Batch Quantity:</td> <td>'.$c_pquant.'</td></tr>
</table>
</body>
</html>';

$stmt = $pdo->get_all("select c.comp_id as comp_id, c.description as description, bc.quantity as quantity from batches b join batch_component bc on b.batch_id=bc.batch_id join component c on bc.comp_id=c.comp_id join locations L on L.loc_id=b.loc_id where batch_id='".$_POST['batch_id']."'");
$batches= $stmt->fetchAll();
$message2 = '';
foreach($batches as $batch)
{
$message2 .= '<html>
<head></head>
<body>
<table border="1" cellspacing="3" width="20%">
<tr>
<td>Comp_ID:</td><td>' . $batch['comp_id'] . '</td>
<td>RM:</td><td>' . $batch['description'] . '</td>
<td>Qt:</td><td>' . $batch['quantity'] . '</td>
</tr>
</table>
</body>
</html>
';
}

mail($to, $subject, $message . "\n" . $message2, $headers);

}
 
Upvote 0 Downvote
fixed. it can be canceled
for the second code i checked nbr of records instead of using fetchAll()

$message = '<html>
<head></head>
<body>
<table border="1" cellspacing="3" width="60%">
<tr><td><b>Date:</b></td><td>'.$date.'</td></tr>
<tr><td><b>User:</b></td><td>'.$c_user.'</td></tr>
<!--<tr><td>Email:</td><td>'.$to.'</td></tr>
<tr><td>FormulaId:</td><td>'.$formula_id.'</td></tr>-->
<tr><td><b>Product:</b></td><td>'.$c_product.'</td></tr>
<tr><td><b>Batch Quantity(Liters):</b></td> <td>'.$c_pquant.'</td></tr>
</table>';
$batches = $pdo->get_all("SELECT c.code as comp_code, c.description as description, bc.quantity as quantity FROM batches b join batch_component bc on b.batch_id=bc.batch_id join component c on bc.comp_id=c.comp_id join locations L on L.loc_id=b.loc_id WHERE b.batch_id='".$batch_id."'");



//$batches= $stmt->fetchAll();

if (count($batches) > 0) {
$message .=
<div class="container">
<div class="row">
<h2>BatchDetails</h2>
<table border="1" cellspacing="3" width="60%">
<head>
<tr>
<!--<th>Code</th>-->
<th>Raw Material</th>
<th>Quantity (Kg)</th>
</tr>
</head>
<tbody>';

foreach($batches as $batch)
{
$message .= '<tr>
<!--<td>' . $batch['comp_code'] . '</td>-->
<td>' . $batch['description'] . '</td>
<td>' . $batch['quantity'] . '</td>
</tr>';
}

$message .= "
</tbody>
</table>
</div>
</div>
</body>
</html>";
}

mail($to, $subject, $message, $headers);
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

SashaBuilder3
  • Locked
  • Question
import CSV file into MySql table in PhpMyAdmin
Replies
0
Views
114
SashaBuilder3
SashaBuilder3
lexer
  • Locked
  • Question
Join two mysql tables for populate datatable
Replies
3
Views
94
spamjim
spamjim
EdLentz
  • Locked
  • Question
I need a simple example of how to send a table of info .
Replies
8
Views
140
EdLentz
EdLentz
EdLentz
  • Locked
  • Question
Partial link in email
Replies
1
Views
57
spamjim
spamjim
rogerte
  • Locked
  • Question
How to parse associative array
Replies
13
Views
229
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top