SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict
    Join Date
    Feb 2009
    Location
    israel
    Posts
    211
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Can I send 2 MySql queries' results to a csv file?

    Hi everyone,
    A code that contains 2 quries: first one lists hours made for a company (company1) and second: total of those hours, writes the results in a csv file goes like this:
    PHP Code:
    <?php
       
    // Connect to database server
       
    $con=mysqli_connect("localhost","root","root","db");
       if (
    mysqli_connect_errno())
        {
          echo 
    "Failed to connect to MySQL: " mysqli_connect_error();
        }
        
    $sql1 "SELECT startDate,endDate,intervalTime, institution, company
       INTO OUTFILE 'd:/companies/xxx.csv'
       FIELDS TERMINATED BY ','
       LINES TERMINATED BY '\n'
       FROM june2013
       WHERE company='company1'"
    ;
        
       
    $sql2 "SELECT company, SEC_TO_TIME(SUM(TIME_TO_SEC(intervalTime))) 
         INTO OUTFILE 'd:/companies/xxx.csv'
          FIELDS TERMINATED BY ','
          FROM june2013 GROUP BY company
          WHERE COMPANY='company1'"
    ;
       
      if (
    mysqli_query($con,$sql1))
        {
        echo 
    "List added to text file successfully";
        }
        else
    {
         echo 
    "Error transferring list " mysqli_error($con);
        }
      if (
    mysqli_query($con,$sql2))
        {
         echo 
    "Summary added to text file successfully";
        }
        else
       {
         echo 
    "Error transferring summary " mysqli_error($con);
        }
      
    mysqli_close($con);
    ?>
    If I run 1 query only (just sql1 for example), it goes well. If I add a second query (sql2) I get an error message that says:
    Error transferring summary File 'd:/companies/xxx.csv' already existsError transferring summary

    Is there a way to send 2 queries' results to a csv file?
    Thanks a lot!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    why not create 2 csv files? they have different content anyway
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Feb 2009
    Location
    israel
    Posts
    211
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    You must be kidding me :D

    The csv file contains a list of hours worked in a company and at the end a row with total hours. Why should I separate it ?!
    If sending 2 queries to one csv is impossible I thought I might create a temporary table with the 1st query's result and then add it a row with "total" query result. I'll need to know how to create that temporary table but before doing so I wanted to make sure I can't send 2 queries to a csv file.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by deotpit View Post
    You must be kidding me
    i assure you, i am not

    you could also open up the first csv in excel, and create a total row using the sum function

    but even better would be to combine your two queries into a single UNION query, and output that
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Feb 2009
    Location
    israel
    Posts
    211
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you r937,
    "UNION" is the magic word. Thanks so much !

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    let me know if you have any problems with that query
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Feb 2009
    Location
    israel
    Posts
    211
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    let me know if you have any problems with that query
    Hi r937
    That "UNION" idea you provided me with utterly solved all my problems (for this time being :-) ). I'm very grateful to you hoping you'll bother offering an hand for problems to come. I'm just starting with MySql though I used to work with SQLServer 6 years ago. Apparently, much has been forgotten :-(
    Thanks again


Tags for this Thread

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
  •