[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

email to sql table

I'm using the Feed to Program feature in webmin that hits a php file on the server when an email is sent through to the server.  I know need to know  insert  message in the body of that email into a mysql database table.  Anyway to do that?
0
COwebmaster
Asked:
COwebmaster
  • 9
  • 5
1 Solution
 
Mohamed AbowardaSoftware EngineerCommented:
You want to insert the message body into your database?

Set the field data type to TEXT and then insert the contents normally.
0
 
COwebmasterAuthor Commented:
ok but would I grab the contents from the message body?  I have this:

<?php
$con = mysql_connect("localhost","username","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_db", $con);

mysql_query("INSERT INTO Persons (Email Message)
VALUES ('what goes here?')");

mysql_close($con);
?>
0
 
Mohamed AbowardaSoftware EngineerCommented:
Execute SQL query to get the message body from the database then you can use mysql_fetch_array() to read the data.

Using mysql_query():
http://php.net/manual/en/function.mysql-query.php
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
COwebmasterAuthor Commented:
the issue is not that I can't retrieve the data from the db.  the issue is what does the code look like in the insert statement.  an email is triggering the php code and I need to insert the entire message in the body of the email that has triggered that page.

sorry, I'm a coldfusion developer and not a php developer but learning.
0
 
Mohamed AbowardaSoftware EngineerCommented:
How the email trigger your PHP page? Please post your code.
0
 
COwebmasterAuthor Commented:
I use Postfix on Apache using Virtualmin and in there is a feature called Feed to Program so it's all done on the server and no scripting except the line that points to the php script.  When an email is sent to a certain email address I created in Postfix, the email triggers that php script.  I would like to insert the contents of the email message into my mysql db table.
0
 
COwebmasterAuthor Commented:
any idea?
0
 
COwebmasterAuthor Commented:
ok, I found this code which I think should work.  However, how should the insert statement if I wanted to insert into my db table the entire message inside the body of the email?
#!/usr/bin/php -q
<?php

ob_start();

// read from stdin
$fd = fopen("php://stdin", "r");
$email = "";
while (!feof($fd)) {
    $email .= fread($fd, 1024);
}
fclose($fd);
// handle email
$lines = explode("n", $email);

// empty vars
$from = "";
$subject = "";
$headers = "";
$message = "";
$splittingheaders = true;

for ($i=0; $i < count($lines); $i++) {
    if ($splittingheaders) {
        // this is a header
        $headers .= $lines[$i]."n";

        // look out for special headers
        if (preg_match("/^Subject: (.*)/", $lines[$i], $matches)) {
            $subject = $matches[1];
        }
        if (preg_match("/^From: (.*)/", $lines[$i], $matches)) {
            $from = $matches[1];
        }
        if (preg_match("/^To: (.*)/", $lines[$i], $matches)) {
            $to = $matches[1];
        }
    } else {
        // not a header, but message
        $message .= $lines[$i]."n";
    }

    if (trim($lines[$i])=="") {
        // empty line, header section has ended
        $splittingheaders = false;
    }
}
ob_end_clean();
?>

Open in new window

0
 
Mohamed AbowardaSoftware EngineerCommented:
Add your SQL Non-Query at the end of FOR statement.
#!/usr/bin/php -q
<?php

ob_start();

// read from stdin
$fd = fopen("php://stdin", "r");
$email = "";
while (!feof($fd)) {
    $email .= fread($fd, 1024);
}
fclose($fd);
// handle email
$lines = explode("n", $email);

// empty vars
$from = "";
$subject = "";
$headers = "";
$message = "";
$splittingheaders = true;

for ($i=0; $i < count($lines); $i++) {
    if ($splittingheaders) {
        // this is a header
        $headers .= $lines[$i]."n";

        // look out for special headers
        if (preg_match("/^Subject: (.*)/", $lines[$i], $matches)) {
            $subject = $matches[1];
        }
        if (preg_match("/^From: (.*)/", $lines[$i], $matches)) {
            $from = $matches[1];
        }
        if (preg_match("/^To: (.*)/", $lines[$i], $matches)) {
            $to = $matches[1];
        }
    } else {
        // not a header, but message
        $message .= $lines[$i]."n";
    }

    if (trim($lines[$i])=="") {
        // empty line, header section has ended
        $splittingheaders = false;
    }
}
// SQL NON-Query goes here...
ob_end_clean();
?>

Open in new window


You can insert $message normally.
0
 
COwebmasterAuthor Commented:
so  like this?

$con = mysql_connect("localhost","username","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_db", $con);

mysql_query("INSERT INTO Table (Message)
VALUES ('$message')");

mysql_close($con);
0
 
Mohamed AbowardaSoftware EngineerCommented:
Exactly, your code should work perfectly, make sure that you set the value of $message first.
0
 
COwebmasterAuthor Commented:
I tried it but it didn't work.  I'm wondering if my server can't read from stdin.  How would I fix that so it can if it doesn't?
0
 
COwebmasterAuthor Commented:
ok, got it to work!

So it inserts a record but now how can I parse the mime headers on subject, from, to, and message and insert that without the mime headers?

I was told using a foreach to do that would work?
<?php
// read from stdin
$fd = fopen("php://stdin", "r");
$email = "";

while (!feof($fd)) {
$email .= fread($fd, 1024);
}
fclose($fd);

// handle email
$lines = explode("\n", $email);
$strLead = "";

for ($x = 0; $x < count($lines); $x++){
$strLead .= $lines[$x];
}

// Store email message to database

$con = mysql_connect("localhost","db","pwd");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("db", $con);

$sql = "INSERT INTO leads (message) VALUES('$strLead')";
$r = mysql_query($sql, $con)


?>

Open in new window

0
 
COwebmasterAuthor Commented:
Thanks!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now