Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    Mar 2004
    Location
    Sydney Australia
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create CSV file from a mySql table

    PHPers

    The below code is a working example of how I create a CSV file from a mySql table I have been trying to modify it to only include the fields I need, so far no luck, I would apreciate any ideas?

    BTW:my database connection is in an include file ("scripts/auditdb.php")
    containing:
    ------start include file contents-----------------
    $conn = mysql_connect("localhost", "root", "admin") or die(mysql_error());
    mysql_select_db("pdaAuditAlpha",$conn) or die(mysql_error());
    ------finish include file contents-----------------

    <?
    if ($_GET['branch'] != "")
    {
    header("Content-Disposition: attachment; filename=branch.csv");
    include("scripts/auditdb.php");
    $errorList = array();
    $count = 0;

    if (sizeof($errorList) == 0)
    {
    //START FILLING IN THE FIELDNAME ON TOP OF EXCEL SHEET.
    $fieldSql = "SHOW FIELDS FROM sitedetails";
    $getFieldInfo = mysql_query($fieldSql);
    $i = 0;
    while ($row = mysql_fetch_array($getFieldInfo))
    {
    echo $row['Field'] . ",";
    }
    echo ("\n");

    //START FILLING IN THE ROWS IN EXCEL SHEET.
    $sql = "SELECT * FROM sitedetails WHERE BSBCode = '".$_GET['bsb']."'";
    $getInfo = mysql_query($sql);
    while($row = mysql_fetch_array($getInfo, MYSQL_ASSOC))
    {
    while (list($key, $value) = each($row))
    {
    echo ("$value" . ",");
    }
    echo ("\n");
    }
    mysql_close($conn);
    }
    else
    {
    listErrors();
    }
    }
    else
    {
    echo "need to pass QS values";
    }
    ?>

    Thanking you in advance
    wind is your friend

  • #2
    Regular Coder
    Join Date
    Jun 2002
    Location
    Adirondacks
    Posts
    516
    Thanks
    4
    Thanked 4 Times in 4 Posts
    I dunno mysql or php very well but this is no prob with PHPMyAdmin, that well known free program.

  • #3
    Super Moderator
    Join Date
    May 2002
    Location
    Perth Australia
    Posts
    4,073
    Thanks
    11
    Thanked 98 Times in 96 Posts
    as FJbrain notes PHPmyadmin can do this for you .. else something similar to the below may do the job

    PHP Code:
    <?
    /*e.g. the actual database fieldnames*/
    $fields=array('fieldname1','fieldname2','etc');
    foreach(
    $fields as $f){
        echo 
    $f ",";
        echo (
    "\n");
      }
      
    //START FILLING IN THE ROWS IN EXCEL SHEET.
    $sql "SELECT ".implode(',',$fields)." FROM sitedetails WHERE BSBCode = '".$_GET['bsb']."'";
    ?>
    resistance is...

    MVC is the current buzz in web application architectures. It comes from event-driven desktop application design and doesn't fit into web application design very well. But luckily nobody really knows what MVC means, so we can call our presentation layer separation mechanism MVC and move on. (Rasmus Lerdorf)

  • #4
    Mega-ultimate member
    Join Date
    Jun 2002
    Location
    Winona, MN - The land of 10,000 lakes
    Posts
    1,855
    Thanks
    1
    Thanked 45 Times in 42 Posts
    Looking at the mysqldump man page might help you as well. Although I've never tried it, there is an option to create a tab delimited text file using mysqldump. Looks to be the --tab=file.txt parameter.

    More information here:
    http://www.hmug.org/man/1/mysqldump.html

    Theoritically, the syntax (from the command line) would be:

    Code:
    $> mysqldump --tab=myfile.txt databaseName -u userName -p

  • #5
    Regular Coder
    Join Date
    Jun 2002
    Location
    Adirondacks
    Posts
    516
    Thanks
    4
    Thanked 4 Times in 4 Posts
    PHPMyAdmin is free and as useful as it is popular. In the last few years, I dunno if I've ever been at sourceforge and NOT seen it in the top ten most downloaded scripts.
    Wanted to mention it again for a couple reasons:
    It's so useful, somewhat powerful, I almost forgot you could do things like these two other responses you got.
    The second, it's really cool that there's enough knowledgable folks here that you got 3 useful answers to one question. Quietly says alot about these boards

  • #6
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    creating these CSV files shoul no tbe done over the webserver. The commandline is a better option for this (see bcarl314 post)

    If it must be done over the webserver, then all that you need is a
    select ... INTO OUTFILE ...

    --> more info in the manual.
    http://dev.mysql.com/doc/mysql/en/SELECT.html
    or run a search at the mysql forum. I'm sure i've posted some code there.

    About phpMyAdmin : its one of the ugliest, slowest and least userfriendly db-frontends i've ever used. It is realy perplexing how such a webfront can be so widely used by so many capable PHP coders, without being shaped up.
    For mySQL db's, MySQLFront is by far a more userfriendly and powerfull front
    http://www.mysqlfront.de/
    (sadly, it's no longer available as a free download but it's probably worth 25 euro)

    On my developmentmachine, i use MySQLFront and i always feel frustrated if i need to do some db-work on a hosted account that uses phpMyAdmin.
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html


  •  

    Posting Permissions

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