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.
Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    New Coder
    Join Date
    Sep 2007
    Posts
    59
    Thanks
    7
    Thanked 0 Times in 0 Posts

    New to PHP, copied script to export to excel from sql

    I found a script online that will export a SQL table and ask you to save as, and it's an excel spreadsheet.

    It works and everything, but the data is crazy formatted. Is there a way to get the spreadsheet to look nice?

    http://www.foodprotect.org/getfile.php

    Like I said, I don't know anything about php, but I was able to get this script working, but I just want the spreadsheet to be formatted when exported.

    Thanks for the help.

  • #2
    New Coder
    Join Date
    Oct 2007
    Posts
    84
    Thanks
    0
    Thanked 8 Times in 8 Posts
    Maybe post your table layout and the script? I can't tell too much from looking at the end result.

  • #3
    New Coder
    Join Date
    Sep 2007
    Posts
    59
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Oh yea sorry. That would help, duh.

    here's the php

    <?php

    /*
    This is where we declare all the database connection variables and their respective values


    /*
    This section builds the required query to fetch the data we need. The second line is calling or executing the query, and the third line is counting the number of fields in the database returned by the query we built above.
    */
    $select = "SELECT * FROM registration07";
    $export = mysql_query($select);
    $fields = mysql_num_fields($export);

    /*
    Here we loop and extract all the field names from our database.
    */
    for ($i = 0; $i < $fields; $i++) {
    $header .= mysql_field_name($export, $i) . "t";
    }

    /*
    In this section we are exporting the values from database and writing them to correct columns of our excel spreadsheet.
    */
    while($row = mysql_fetch_row($export)) {
    $line = '';
    foreach($row as $value) {
    if ((!isset($value)) OR ($value == "")) {
    $value = "\r\n";
    } else {
    $value = str_replace('"', '""', $value);
    $value = '"' . $value . '"' . "\r\n";
    }
    $line .= $value;
    }
    $data .= trim($line)."\r\n";
    }
    $data = str_replace("r","",$data);

    if ($data == "") {
    $data = "n(0) Records Found!n";
    }
    header("Content-type: application/x-msdownload");
    header("Content-Disposition: attachment; filename=extraction.xls");
    header("Pragma: no-cache");
    header("Expires: 0");
    print "$headern$data";

    ?>

    And I'm not sure what you mean by table layout? like all of the header categories?
    Last edited by t-buck; 11-02-2007 at 05:45 PM.

  • #4
    New Coder
    Join Date
    Oct 2007
    Posts
    84
    Thanks
    0
    Thanked 8 Times in 8 Posts
    First, you can remove the username and password from your post

    I was just asking how your table was layed out in the database. I'll scan over this and get back to you.

    edit: Also, never use root for a script. Set up a user with SELECT access.

  • #5
    New Coder
    Join Date
    Sep 2007
    Posts
    59
    Thanks
    7
    Thanked 0 Times in 0 Posts
    sorry, I'm dumb. thanks.

  • #6
    New Coder
    Join Date
    Oct 2007
    Posts
    84
    Thanks
    0
    Thanked 8 Times in 8 Posts
    The problem comes in with the outputting of the file. The output looks like:
    Code:
    "42"
    "1194021077"
    "11-02-2007"
    "Bob"
    "Dole"
    "bobby"
    "Bob Dole Entepises"
    "111 Bob Dole Way"
    "Matin"
    "SC"
    "57551"
    "555-555-1212"
    That would be why there are no "lines" in the spreadsheet. Replace the "\r\n" with "\t" or "," depending if you want tab delimited or comma seperated.

  • #7
    New Coder
    Join Date
    Sep 2007
    Posts
    59
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Hey that helped! but now they are all split into cells, but every registrant is on one line. How do I tell the php to go to the next row after exporting 1 registrant?

  • #8
    New Coder
    Join Date
    Sep 2007
    Posts
    59
    Thanks
    7
    Thanked 0 Times in 0 Posts
    oh, nevermind..I just messed around with it...and I got it.

    Thanks.

  • #9
    New Coder
    Join Date
    Sep 2007
    Posts
    59
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Oh wait I spoke too soon. Can I get it to export the header categories at the beginning row?

  • #10
    New Coder
    Join Date
    Oct 2007
    Posts
    84
    Thanks
    0
    Thanked 8 Times in 8 Posts
    replace:
    PHP Code:
    for ($i 0$i $fields$i++) {
    $header .= mysql_field_name($export$i) . "t";

    with:
    PHP Code:
    $header '';
    for (
    $i 0$i $fields$i++) {
    $header .= mysql_field_name($export$i) . "t";

    Try that out.

  • #11
    New Coder
    Join Date
    Sep 2007
    Posts
    59
    Thanks
    7
    Thanked 0 Times in 0 Posts
    I replaced, but still no headers showing up.....hmmmmm

  • #12
    New Coder
    Join Date
    Oct 2007
    Posts
    84
    Thanks
    0
    Thanked 8 Times in 8 Posts
    Just noticed the last line:
    PHP Code:
    print "$headern$data"
    Change it to
    PHP Code:
    print "$header\r\n$data"

  • #13
    New Coder
    Join Date
    Sep 2007
    Posts
    59
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Hey that exported the categories, but they are all in the same cell. Did I miss something that will make them distribute through the cells?

  • #14
    New Coder
    Join Date
    Sep 2007
    Posts
    59
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Hey I got it! I just needed to put a backslash before the t, in your extra code.

    You rock! Ok one last question, and I'll leave you alone. Is there a line I can add to the code, that will only export today's registrations? the category label is date

  • #15
    New Coder
    Join Date
    Oct 2007
    Posts
    84
    Thanks
    0
    Thanked 8 Times in 8 Posts
    Change your SQL query to SELECT * FROM table WHERE date field is less than one day

    It depends on what kind of time stamp you are using on how you would set that query up.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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