PHP - Using Php To Export Sql Results Into Csv & E-mail

I am using magento for sending mail with condition,

My code:

<?php
class Gta_MerchantNotification_Model_Observer {
    public function merchantremainder($Observer) {
       $order = $Observer->getEvent()->getOrder();
       $order_details = $order->getAllVisibleItems();

       $itemData = array();
       foreach ($order_details as $list) {
          $incrementid = $order->getIncrementId();
          $sku = $list->getsku();
          $name = $list->getName();
          $price = $list->getPrice();
          $Qty = $list->getQtyOrdered();
          $extra = $order->getIncrementId();
          $message = 

          "
          <tr>
          <!-- <td>$incrementid</td> -->
          <td>$sku</td>
          <td>$name</td>
          <td>$price</td>
          <td>$Qty</td>
          </tr>";

          $itemData[$list->getId()] = $message;

      }
  
      $finalMessage =  "
      <p>Order Id : $incrementid</p>
      <table border='1'>

      <tr>
      <!-- <th>Id</th> -->
      <th>Sku</th>
      <th>Product name</th>
      <th>Price</th>
      <th>Qty Ordered</th>
      </tr>";
      if (!empty($itemData)) {
          foreach ($itemData as $data) {
             $finalMessage .= $data;
         }

         $finalMessage .= "</table>";
         $this->sendMail($finalMessage);
     }

 }

 public function sendMail($message) {
   $body ="$message";
   $emailTemplate = Mage::getModel('core/email');
   $emailTemplate->setFromName('abc');
   $emailTemplate->setBody($body);
   $emailTemplate->setSubject("Custom Email from observer");
   $emailTemplate->setType('html');
   $emailTemplate->setToEmail('abc@gmail.com');
   $emailTemplate->send();

}
}

?>

 

Output :

 

If order placed mail send to abc@gmail.com.
How to set email sender based on SKU $sku from order.

I want :

1) If SKU starts with 2, email should go to the mail id abc@gmail.com,
screenshot :

 

2) If SKU starts with 3, email should go to the mail id xyz@gmail.com,
screenshot :

 

3) If SKU starts with 4, email should go to the mail id qwe@gmail.com,
screenshot :

 

FYI - If an order contains 10 items email should go separately based on SKU. But an order id the same must include all the emails.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Hi all,

I'm coding an automated mailer for a dentist office. They set appointment dates through a web interface, and then this appointment is mailed to the customer via PHP's mail() function.

However, some users are not receving the mail. They claim that it is not even in the junk mail folder. Is it because I'm setting or not setting certain properties in the header? Should I be setting something else?

Here is my code
Code: [Select]
ini_set("SMTP", "mail.isp-provider.net");
$headers = 'From: Schedule Manager <schedule@address.com>' . "\r\n" .
'Reply-To: Schedule Manager <schedule@address.com>' . "\r\n" .
'Bcc: internal@address.com' . "\r\n" .
'Content-type: text/html; charset=iso-8859-1' . "\r\n" .
'X-Mailer: PHP/' . phpversion();

mail($customerAddress, $subject, $body, $headers);
Note that customerAddress, subject, and body are all just regular normal strings.

As I said, it works for most customers, but some customers do not receive the emails.

Hello there,

I've come across a issue with using PHP's mail function due to the fact I've tried a assortment of different headers yet Hotmail continues to place my sent emails in the junk folder.

Here is my method of emailing my clients:


function Email($AccountID, $Subject, $Message)
{
if($this->IsValidAccount($AccountID))
{
$Account = $this->AccountKeys($AccountID);

$headers .= 'To: '.$Account['account_client'].' <'.$Account['account_email'].'>' . "\r\n";

$headers .= "From: X-Host <no-reply@X-Host.co.uk>\r\n"; 
$headers .= "Reply-To: X-Host <no-reply@X-Host.co.uk>\r\n"; 
$headers .= "Return-Path: no-reply@X-Host.co.uk\r\n"; 
$headers .= "MIME-Version: 1.0\r\n"; 
$headers .= "Content-Type: text/html; charset=ISO-8859-1\r\n"; 
$headers .= "Organization: X-Host\r\n";
echo $Account['account_email']. $Subject. $Message;

return mail($Account['account_email'], $Subject, $Message, $headers);
}
else return InvalidAccount;
}


So could anybody help me out please and tell me what headers I should be using.

Dear freak gurus out there,
I'm new to php and sql as i learn from online examples  .

Though there are lots of links and answers out there related to my post but none reflects what i need. You guys my only hope.

What i have:

3 tables : each has its own number of fields (tbl1=14 ,tbl2=9,tbl3=9). There is a field in each tables that can be related (FamilyField).

What i need:

export to csv all the records from all 3 tables which relates to search criteria (by user) based on a field (MembershipField) which the data may vary from other tables.

pseudo code:
$membership_type =$_POST['mtype'];

$sql=" SELECT field1,field2,field5,MembershipField from tbl1, (SELECT field1,field2,MembershipField from tbl2), (SELECT field3,field6,field8,MembershipField from tbl3) WHERE MembershipFied=$membership_type";

export to csv file by using INTO OUTFILE;

I have tried all the code i can find to export from 1 table to csv, and i manage to use UNION to get output from 3 tables but unable to export
as the error message was "incorrect usage of UNION and INTO.

is this possible? or any workaround?
guides with samples will be a great help.

I need to write a cron job that will sync 2 databases.
Is there a quick way to write an export from one database for import into another?
Or do i manually have to write the select statements per table, format them as insert ignore strings, then execute them on the other database?

Thanks guys!

Hi,

I have tried these PDF generators:

1. TCPDF- doesn't support css class. Styles will work if inline.
2. DOMPDF - Supports CSS class. Doesn't have Unicode support. Already tried load_font to convert ttf to amf but still Chinese char are not displayed properly.
- Also tried (installed) PDFlib, watermark appears in pdf but characters are not displayed properly.

Any suggestion in PDF generator that can do the functionality of TCPDF and DOMPDF? BTW i'm using xampp.
Thanks in advance.

Hi,
I found a couple of topics in the search on this but can't resolve my problem.
I have a cart and want to mail it to myself and the client. The cart I'm using works fine but the moment I "check out" (mail the order) it gives a error. I build an error trap but see nothing wrong then I tried a different way to send the order in html with no avail.
6x files I use: Index.php -The products I'm selling and "add to chart"
                      Cart.php - To update and display the products in the basket
                      SendOrder.php - To mail the cart etc. to me and client
as the main files and some functions, db connection and global's...
1) Index.php by Simon Mayer

<?php
// Start the session
session_start();
// Include MySQL class
require_once('inc/mysql.class.php');
// Include database connection
require_once('inc/global.inc.php');
// Include functions
require_once('inc/functions.inc.php');
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>MedSET Store</title>
<link rel="stylesheet" href="css/styles.css" >
</head>

<body>
<div id="shoppingcart">

<h1>Your Shopping Cart</h1>

<?php
echo writeShoppingCart();
?>

</div>

<div id="booklist">

<h1>Products</h1>

<?php
$sql = 'SELECT * FROM books ORDER BY id';
$result = $db->query($sql);
$output[] = '<ul>';
while ($row = $result->fetch()) {
$output[] = '<li>"'.$row['title'].'" by '.$row['author'].': &pound;'.$row['price'].'<br /><a href="cart.php?action=add&id='.$row['id'].'">Add to cart</a></li>';
}
$output[] = '</ul>';
echo join('',$output);
?>

</div>

</body>
</html>


2)Cart.php by Simon Mayer

<?php
// Start the session
session_start();
// Include MySQL class
require_once('inc/mysql.class.php');
// Include database connection
require_once('inc/global.inc.php');
// Include functions
require_once('inc/functions.inc.php');

// Process actions
$cart = $_SESSION['cart'];
$action = $_GET['action'];
switch ($action) {
case 'add':
if ($cart) {
$cart .= ','.$_GET['id'];
} else {
$cart = $_GET['id'];
}
break;
case 'delete':
if ($cart) {
$items = explode(',',$cart);
$newcart = '';
foreach ($items as $item) {
if ($_GET['id'] != $item) {
if ($newcart != '') {
$newcart .= ','.$item;
} else {
$newcart = $item;
}
}
}
$cart = $newcart;
}
break;
case 'update':
if ($cart) {
$newcart = '';
foreach ($_POST as $key=>$value) {
if (stristr($key,'qty')) {
$id = str_replace('qty','',$key);
$items = ($newcart != '') ? explode(',',$newcart) : explode(',',$cart);
$newcart = '';
foreach ($items as $item) {
if ($id != $item) {
if ($newcart != '') {
$newcart .= ','.$item;
} else {
$newcart = $item;
}
}
}
for ($i=1;$i<=$value;$i++) {
if ($newcart != '') {
$newcart .= ','.$id;
} else {
$newcart = $id;
}
}
}
}
}
$cart = $newcart;
break;
}
$_SESSION['cart'] = $cart;

?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>MedSET Store</title>
<link rel="stylesheet" href="css/styles.css" >
</head>

<body>

<div id="shoppingcart">

<h1>Your Shopping Cart</h1>

<?php
echo writeShoppingCart();
?>

</div>

<div id="contents">

<h1>Please check quantities...</h1>

<?php
echo showCart();
?>
<form action="sendOrder.php" method="post"> //I added the action file to handle the form
<input type="submit" name="sendmail" value="Check Out" >
</form>
<p><a href="index.php">Continue Shopping...</a></p>

</div>

</body>
</html>


      2.1)MySQL.class.php

<?php
/**
* MySQL Database Connection Class
* @access public
* @package SPLIB
*/
class MySQL {
    /**
    * MySQL server hostname
    * @access private
    * @var string
    */
    var $host;

    /**
    * MySQL username
    * @access private
    * @var string
    */
    var $dbUser;

    /**
    * MySQL user's password
    * @access private
    * @var string
    */
    var $dbPass;

    /**
    * Name of database to use
    * @access private
    * @var string
    */
    var $dbName;

    /**
    * MySQL Resource link identifier stored here
    * @access private
    * @var string
    */
    var $dbConn;

    /**
    * Stores error messages for connection errors
    * @access private
    * @var string
    */
    var $connectError;

    /**
    * MySQL constructor
    * @param string host (MySQL server hostname)
    * @param string dbUser (MySQL User Name)
    * @param string dbPass (MySQL User Password)
    * @param string dbName (Database to select)
    * @access public
    */
    function MySQL ($host,$dbUser,$dbPass,$dbName) {
        $this->host=$host;
        $this->dbUser=$dbUser;
        $this->dbPass=$dbPass;
        $this->dbName=$dbName;
        $this->connectToDb();
    }

    /**
    * Establishes connection to MySQL and selects a database
    * @return void
    * @access private
    */
    function connectToDb () {
        // Make connection to MySQL server
        if (!$this->dbConn = @mysql_connect($this->host,
                                      $this->dbUser,
                                      $this->dbPass)) {
            trigger_error('Could not connect to server');
            $this->connectError=true;
        // Select database
        } else if ( !@mysql_select_db($this->dbName,$this->dbConn) ) {
            trigger_error('Could not select database');
            $this->connectError=true;
        }
    }

    /**
    * Checks for MySQL errors
    * @return boolean
    * @access public
    */
    function isError () {
        if ( $this->connectError )
            return true;
        $error=mysql_error ($this->dbConn);
        if ( empty ($error) )
            return false;
        else
            return true;
    }

    /**
    * Returns an instance of MySQLResult to fetch rows with
    * @param $sql string the database query to run
    * @return MySQLResult
    * @access public
    */
    function query($sql) {
        if (!$queryResource=mysql_query($sql,$this->dbConn))
            trigger_error ('Query failed: '.mysql_error($this->dbConn).
                           ' SQL: '.$sql);
        return new MySQLResult($this,$queryResource);
    }
}

/**
* MySQLResult Data Fetching Class
* @access public
* @package SPLIB
*/
class MySQLResult {
    /**
    * Instance of MySQL providing database connection
    * @access private
    * @var MySQL
    */
    var $mysql;

    /**
    * Query resource
    * @access private
    * @var resource
    */
    var $query;

    /**
    * MySQLResult constructor
    * @param object mysql   (instance of MySQL class)
    * @param resource query (MySQL query resource)
    * @access public
    */
    function MySQLResult(& $mysql,$query) {
        $this->mysql=& $mysql;
        $this->query=$query;
    }

    /**
    * Fetches a row from the result
    * @return array
    * @access public
    */
    function fetch () {
        if ( $row=mysql_fetch_array($this->query,MYSQL_ASSOC) ) {
            return $row;
        } else if ( $this->size() > 0 ) {
            mysql_data_seek($this->query,0);
            return false;
        } else {
            return false;
        }
    }

    /**
    * Returns the number of rows selected
    * @return int
    * @access public
    */
    function size () {
        return mysql_num_rows($this->query);
    }

    /**
    * Returns the ID of the last row inserted
    * @return int
    * @access public
    */
    function insertID () {
        return mysql_insert_id($this->mysql->dbConn);
    }
    
    /**
    * Checks for MySQL errors
    * @return boolean
    * @access public
    */
    function isError () {
        return $this->mysql->isError();
    }
}
?>

   2.2)Global.inc.php

<?php
$host = 'localhost';
$user = 'MyUserName';
$pass = 'MyPassword';
$name = 'MyDataBase';
$db = &new MySQL($host,$user,$pass,$name);
?>


   2.3)Function.inc.php

<?php
function writeShoppingCart() {
$cart = $_SESSION['cart'];
if (!$cart) {
return '<p>You have no items in your shopping cart</p>';
} else {
// Parse the cart session variable
$items = explode(',',$cart);
$s = (count($items) > 1) ? 's':'';
return '<p>You have <a href="cart.php">'.count($items).' item'.$s.' in your shopping cart</a></p>';
}
}

function showCart() {
global $db;
$cart = $_SESSION['cart'];
if ($cart) {
$items = explode(',',$cart);
$contents = array();
foreach ($items as $item) {
$contents[$item] = (isset($contents[$item])) ? $contents[$item] + 1 : 1;
}
$output[] = '<form action="cart.php?action=update" method="post" id="cart">';
$output[] = '<table>';
foreach ($contents as $id=>$qty) {
$sql = 'SELECT * FROM books WHERE id = '.$id;
$result = $db->query($sql);
$row = $result->fetch();
extract($row);
$output[] = '<tr>';
$output[] = '<td><a href="cart.php?action=delete&id='.$id.'" class="r">Remove</a></td>';
$output[] = '<td>'.$title.' by '.$author.'</td>';
$output[] = '<td>&pound;'.$price.'</td>';
$output[] = '<td><input type="text" name="qty'.$id.'" value="'.$qty.'" size="3" maxlength="3" /></td>';
$output[] = '<td>&pound;'.($price * $qty).'</td>';
$total += $price * $qty;
$output[] = '</tr>';
}
$output[] = '</table>';
$output[] = '<p>Grand total: <strong>&pound;'.$total.'</strong></p>';
$output[] = '<div><button type="submit">Update cart</button></div>';
$output[] = '</form>';
} else {
$output[] = '<p>You shopping cart is empty.</p>';
}
return join('',$output);
}
?>

     
3)SendOrder.php

<?php 
$cart = $_SESSION['cart'];
$to = "me@myself.com";
$subject =  "MedSET Order";
$message = "<html>
<head>
<title>Email Order<Title>
</head>
<body>
This is my message in body
</body>";
$headers = "MIME-Version: 1.0" . "\r\n";
$headers .= "Content-type:text/html;charset=iso-8859-1" . "\r\n";
$headers .= 'From: "email address" <an email existing on same server as shopping cart>' .  "\r\n";

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

?>


Any help will be appreciated...

system: vista ultimate sp2
IIS 7 server installed and running
PHP 5
fastCGI

im not sure if this is within the purview of the forum but you all seem like smart people with an expertise in this area.

i have a web site http://penumbraproductions.dyndns-remote.com/ .  it is hosted locally on my desktop using the above mentioned IIS7 server options using dyndns to list the url cause my ISP has DHCP without static ip's for non-business accounts

that has a contact form... form mail.  i've tried numerous php codes to get it to send mail to my email account but none of them are working

my IIS smtp setting a
email to: archaismic@gmail.com
smtp server: smtp.gmail.com
port number: 465
using login credentials

i've also tried to configuring the smtp options in IIS to dump the mail to a folder on my computer which also did not work

attached is the feedback.php file im currently working with minus the captcha private key

and the code for the form i'm using is:

Code: [Select]
<form action="feedback.php" method="post">
<table border="0" cellpadding="8" cellspacing="8">
<tr><td><label for="tswname">Name</label>:</td><td><input type="text" name="fullname" id="tswname" size="25" /></td></tr>
<tr><td><label for="tswemail">Email address</label>:</td><td><input type="text" id="tswemail" name="email" size="25" /></td></tr>
<tr>
<td colspan="2">
<label for="tswcomments">Comments</label><br />
<textarea rows="15" cols="45" name="comments" id="tswcomments"></textarea>
</td>
</tr>
<tr>
<td align="center" colspan="2">
<script type="text/javascript" src="http://api.recaptcha.net/challenge?k=6LcYjcASAAAAAH1NwZ0IH_TUO4XDANZqWu3Ei9yh"></script>
<noscript>
<iframe src="http://api.recaptcha.net/noscript?k=6LcYjcASAAAAAH1NwZ0IH_TUO4XDANZqWu3Ei9yh" height="300" width="500" frameborder="0" title="CAPTCHA test"></iframe>
<br />
<label for="tswcaptcha">Copy and paste the code provided in above box he </label><br />
<textarea name="recaptcha_challenge_field" id="tswcaptcha" rows="3" cols="40"></textarea>
<input type="hidden" name="recaptcha_response_field" value="manual_challenge" />
</noscript>
</td>
</tr>
<tr>
<td align="center" colspan="2">
<input type="submit" value="Send Feedback" />
</td>
</tr>
</table>
</form>

Hi,

Am having a total brain melt down on a php/mysql export script. I've a table wp_bp_xprofile_data with this sort of layout:
ID
field_id
user_id
value

It shows data like so:
id | field_id | user_id | value
1 | 1 | 1| John Smith
2 | 2 | 1 |john@smith.com
3 | 3 | 1 |23 arcacia avenue
4 | 1 | 2 |Fred Smith
5 | 2 | 2 |fred@smith.com
6 | 3 | 2 |999 lets be avenue


field_id joins to another table wp_bp_xprofile_fields, where it has
id
name
This shows data like so:
id | name
1 | Name
2 | Email
3 | Address

What I need to do, is export the data to a csv so the CSV will then show:

Name | Email | Address
John Smith | john@smith.com | 23 arcacia avenue
Fred Smith | fred@smith.com | 999 lets be avenue

However, mine keeps coming back
Name | Email | Address
John Smith
john@smith.com
23 arcacia avenue
Fred Smith
fred@smith.com
999 lets be avenue

Here's my code:
[php]
   header ("Content-Disposition:\"inline; filename=export.csv");
   header ("Content-type: text/csv\"");
   
   $query = "SELECT VALUE FROM `wp_bp_xprofile_data` ORDER BY `wp_bp_xprofile_data`.`user_id` ASC";
   $result = mysql_query($query) or die("Query failed : " . mysql_error());
   
   echo "email,telephone,county,town,add 2,add 1,company name,name,acc type,date joined,country\r\n"; //header

   while($row = mysql_fetch_row($result)){
      echo "$row[0],$row[1],$row[2],$row[3],$row[4],$row[5],$row[6],$row[7],$row[8],$row[9],$row[10],$row[11]\r\n"; //data
      
      echo "$row[]\r\n"; //data
   }
[php]
Where am I going wrong?

TIA

I created this php class which recieves an sql query from flash and then produces a csv file for download. The code in its current form produces a csv file on the server. However, I want it to save on the client computer with a 'Save As' dialog box. I am testing on localhost.

Here is the class:


<?php

class CSVExport
{
public function __construct()
{
require_once 'Zend/Date.php';
require_once 'DatabaseConnection.php';
}

public function exportCSV($query)
{

$result = mysql_query($query);

$fname = 'CSVFile.csv';

$headers = array();
$rowArray = array();

$numFields = mysql_num_fields($result);

for ($i = 0; $i < $numFields; $i++) {
        $headers[] = mysql_field_name($result , $i);
}

$fp = fopen($fname, 'w');

if ($fp && $result) {
    header('Content-Type: application/csv');
    header('Content-Disposition: inline; filename='.$fname);

fputcsv($fp, $headers);
while ($thisrow = mysql_fetch_row($result)) {
fputcsv($fp, $thisrow);
}

readfile($fname);
fclose($fp);
}
        die;
}

}



Thanks

Hi,

I have a PHP web page that displays the results from an SQL query. I was wondering if there was anything out there that would allow my users to click a link and then export the results of that query to a CSV file?

Thanks
Matt

I have this code that is supposed to export the results of this query into a CSV. When I run the code in Firefox, it works perfectly. The only browser that it doesn't work in is IE. It opens an HTML page with the results on the page instead of prompting to download the CSV. Can anyone help me out?

Code: [Select]
<?php
//code to export report to excel
if(isset($_GET['date'])){
    
include('../inc/db.php');    
    
if (!$link = mysql_connect($dbhost, $dbuser, $dbpass)) {
    echo 'Could not connect to mysql';
    exit;
}

if (!mysql_select_db($dbname, $link)) {
    echo 'Could not select database';
    exit;
}
$date = $_GET['date']; 

$select = "SELECT patients.lname, patients.fname, patients.dob, patients.sex, visit_data.cc, 
FROM_UNIXTIME(visit_data.reg_time), FROM_UNIXTIME(visit_data.discharged_time), visit_data.disposition, visit_data.leftby 
FROM patients, visit_data WHERE discharge_date = '$date' AND patients.patientid = visit_data.patientid";

$export = mysql_query ( $select ) or die ( "Sql error : " . mysql_error( ) );

$fields = mysql_num_fields ( $export );

for ( $i = 0; $i < $fields; $i++ )
{
    $header .= mysql_field_name( $export , $i ) . "\t";
}

while( $row = mysql_fetch_row( $export ) )
{
    $line = '';
    foreach( $row as $value )
    {                                            
        if ( ( !isset( $value ) ) || ( $value == "" ) )
        {
            $value = "\t";
        }
        else
        {
            $value = str_replace( '"' , '""' , $value );
            $value = '"' . $value . '"' . "\t";
        }
        $line .= $value;
    }
    $data .= trim( $line ) . "\n";
}
$data = str_replace( "\r" , "" , $data );

if ( $data == "" )
{
    $data = "\n(0) Records Found!\n";                        
}

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=DailyLog.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";

}
?>

Is it possible to export a single value from a mysql DB into a pre-existing excel spread sheet?

I know how to create a spread sheet from php and mysql, but not a single value into a pre-existing excel document cell.

here's basically what I'm doing:

Creating a financial expense tracking report in php, user will input each expense,  it will keep a running tally of the available funds in user's bank account. The user has an excel spread sheet that requires the current value of available funds and updates a budget forecast according. SO, I need to somehow get the available balance of the bank account (generated by user input) into the pre-existing excel spreadsheet.

Any thoughts? I'm totally at a loss.

Thanks!

Hello,

How can I export mysql database (tables) with php from browser to backup.sql , something like in phpMyAdmin.
And simple option with file save as???

Thanks
Regards