SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Outputting DB query into an Email

    Hi,

    I have the following code which produces a page which lists the differences between two tables. It outputs correctly (although as a newbie, the code is very very messy):

    PHP Code:
    //query to compare translations from two tables 

        
    $compare_translations "select $table2.Project, $table2.Notes, $table2.Name FROM $table2 LEFT JOIN $table ON $table2.Name=$table.Name WHERE $table.Name IS NULL AND $table2.Task_type='translations'";  
                  
    //executes query 
                        
    if (!($connection = @ mysql_connect($hostName$username$password)))  
                            die(
    "Could not connect to database");  
                      
                        if (!
    mysql_select_db($databaseName$connection))  
                                
    showerror();  
                          
                        if (!(
    $translations_result = @ mysql_query ($compare_translations$connection)))  
                                
    showerror();  
          
    //Displays Translation changes 
                    
    $trans = @ mysql_fetch_array($translations_result);  
                    
    $trans $trans["Name"];  
                    if (!empty(
    $trans))  
                    {  
                    
    mysql_data_seek($translations_result0);  
                    echo 
    "The <b>TRANSLATIONS </b>for the following projects have beed added/edited:<br>";      
                    
    $value ' ';  
                    while (
    $translations = @ mysql_fetch_array($translations_result))  
                        {  
                            if (
    $value != $translations["Project"])  
                            {  
                                echo 
    "- ".$translations["Project"]."<br>";  
                                
    $value $translations["Project"];  
                            }  
                        }  
                echo 
    "<br>The additions/changes are as follows:<br>";  
                
    $value ' ';  
                
    mysql_data_seek($translations_result0);  
                    while (
    $translations = @ mysql_fetch_array($translations_result))  
                            {  
                                if (
    $value != $translations["Project"])  
                                    {  
                                        echo 
    "<b><br><u>Project: ".$translations["Project"]."</u></b><br>";  
                                        
    $value $translations["Project"];  
                                    }  
                                
    //if ($translations["Notes"] != null) 
                                
    if(!empty($translations["Notes"]))  
                                    {  
                                        echo 
    "<br><b><i>".$translations["Notes"]."</i></b><br>";  
                                    }  
                                echo 
    $translations["Name"]."<br>";  
                        }  
                        echo 
    "<br><hr><br>";  
                } 
    However, instead of outputing onto a page which it currently does, I want the output to be within an email.

    I know when sending an email you can assign the body of the mail as e.g. $message. so therefore, I could replace echos with a variable and then concatenate them like $message.$message1 etc., however, as the while statment produces alist, this does not work as it only gives you one value, i.e the last in the loop:

    PHP Code:
    //query to compare translations from two tables 

        
    $compare_translations "select $table2.Project, $table2.Notes, $table2.Name FROM $table2 LEFT JOIN $table ON $table2.Name=$table.Name WHERE $table.Name IS NULL AND $table2.Task_type='translations'";  
                  
    //executes query 
                        
    if (!($connection = @ mysql_connect($hostName$username$password)))  
                            die(
    "Could not connect to database");  
                      
                        if (!
    mysql_select_db($databaseName$connection))  
                                
    showerror();  
                          
                        if (!(
    $translations_result = @ mysql_query ($compare_translations$connection)))  
                                
    showerror();  
          
    //Displays Translation changes 
                    
    $trans = @ mysql_fetch_array($translations_result);  
                    
    $trans $trans["Name"];  
                    if (!empty(
    $trans))  
                    {  
                    
    mysql_data_seek($translations_result0);  
                    
    $message "The <b>TRANSLATIONS </b>for the following projects have beed added/edited:<br>";      
                    
    $value ' ';  
                    while (
    $translations = @ mysql_fetch_array($translations_result))  
                        {  
                            if (
    $value != $translations["Project"])  
                            {  
                                
    $message1 "- ".$translations["Project"]."<br>";  
                                
    $value $translations["Project"];  
                            }  
                        }  
                
    $message2 "<br>The additions/changes are as follows:<br>";  
                
    $value ' ';  
                
    mysql_data_seek($translations_result0);  
                    while (
    $translations = @ mysql_fetch_array($translations_result))  
                            {  
                                if (
    $value != $translations["Project"])  
                                    {  
                                        echo 
    "<b><br><u>Project: ".$translations["Project"]."</u></b><br>";  
                                        
    $value $translations["Project"];  
                                    }  
                                
    //if ($translations["Notes"] != null) 
                                
    if(!empty($translations["Notes"]))  
                                    {  
                                        
    $message3 "<br><b><i>".$translations["Notes"]."</i></b><br>";  
                                    }  
                                
    $message4 $translations["Name"]."<br>";  
                        }  
                        echo 
    "<br><hr><br>";  
                } 

    I have searched the web for days looking for an answer and I am none the wiser, if anyone can help I would very much appreciate it as I am starting to have sleepless nights!!

    Thanks

  2. #2
    SitePoint Guru MikeBigg's Avatar
    Join Date
    Jun 2004
    Location
    Reading, UK
    Posts
    970
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    this does not work as it only gives you one value, i.e the last in the loop:
    Without looking closely at your code, I suspect that in the while loop you are over-writing the message variable each time rather than adding it to what was already there, which is why you only see the data from the last time in the loop.

    For example (in pseudo code):

    $msg = '' ; // init the variable
    while loop {
    $msg = $data_from_db;
    }

    will over-write the data $msg on every trip round the loop, whereas

    $msg = '' ; // init the variable
    while loop {
    $msg .= $data_from_db;
    }

    will build up the contents of $msg each time round.

    Mike

  3. #3
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Brilliant - this works a treat. Thanks very much.

    I have it working but for some reason my output in the mail has random !. Oh well, another few days trying to work that one out! lol

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    are you sure they are not question marks? your database may have characters that are not in the current character set.

  5. #5
    SitePoint Guru MikeBigg's Avatar
    Join Date
    Jun 2004
    Location
    Reading, UK
    Posts
    970
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by mcweb
    Brilliant - this works a treat. Thanks very much.
    That's good ... well done.

    I have it working but for some reason my output in the mail has random !. Oh well, another few days trying to work that one out! lol
    On something like that I find it invaluable to have some means of looking at the data in the table without writing a PHP script.

    I use PHPMyAdmin sometimes as it is available through the hosting accounts I have, but I find it to be clunky and unintuitive.

    I use and prefer MySQL-Front ... it allows you to connect to a remote mySQL database from your desktop PC, create, modify and delete tables, rows columns and data ... and much more.

    If there are od characters in your data, as longneck has suggested, this would probably be the best way to find them.

    Mike


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •