karlosantana 0 Report post Posted February 11, 2010 Well if you look here. You will see I have been working on a script to suck all the information from an sql database to Excel. I've had a problem in that I cant select data just from a certain date. I have copied the code I'm using below. It consits of three filesclass.sql2csv.php $FILENAME = 'exceptions.csv'; // connect to MySQL database $link = mysql_connect($params['host'], $params['user'], $params['password']) or die(mysql_error()); // start using the required database $db = mysql_select_db($params['database'], $link); // execute query to get the data required $res = mysql_query($query, $link) or die(mysql_error()); $colnames = array(); // get column captions and enclose them in doublequotes (") if $print_captions is not set to false if ($print_captions) { for ($i = 0; $i < mysql_num_fields($res); $i++) { $fld = mysql_fetch_field($res, $i); $colnames[] = '"'.$fld->name.'"'; } // insert column captions at the beginning of .csv file $CSV .= implode(",", $colnames); } // iterate through each row // replace single double-quotes with double double-quotes // and add values to .csv file contents if (mysql_num_rows($res) > 0) { while ($row = mysql_fetch_array($res, MYSQL_NUM)) { for ($i = 0; $i < sizeof($row); $i++) $row[$i] = '"'.str_replace('"', '""', $row[$i]).'"'; $CSV .= "\n".implode(",", $row); } } // send output to browser as attachment (force to download file header('Expires linenums:0'><?php class SQL2CSV { // initialize the class with passed parameters function SQL2CSV($params, $query, $print_captions = true) { // set initial .csv file contents $CSV = ''; $FILENAME = 'exceptions.csv'; // connect to MySQL database $link = mysql_connect($params['host'], $params['user'], $params['password']) or die(mysql_error()); // start using the required database $db = mysql_select_db($params['database'], $link); // execute query to get the data required $res = mysql_query($query, $link) or die(mysql_error()); $colnames = array(); // get column captions and enclose them in doublequotes (") if $print_captions is not set to false if ($print_captions) { for ($i = 0; $i < mysql_num_fields($res); $i++) { $fld = mysql_fetch_field($res, $i); $colnames[] = '"'.$fld->name.'"'; } // insert column captions at the beginning of .csv file $CSV .= implode(",", $colnames); } // iterate through each row // replace single double-quotes with double double-quotes // and add values to .csv file contents if (mysql_num_rows($res) > 0) { while ($row = mysql_fetch_array($res, MYSQL_NUM)) { for ($i = 0; $i < sizeof($row); $i++) $row[$i] = '"'.str_replace('"', '""', $row[$i]).'"'; $CSV .= "\n".implode(",", $row); } } // send output to browser as attachment (force to download file header('Expires: Mon, 1 Jan 1990 00:00:00 GMT'); header('Last-Modified: '.gmdate("D,d M Y H:i:s").' GMT'); header('Pragma: no-cache'); header('Content-type: text/csv'); header('Content-Disposition: attachment; filename='.$FILENAME); // print the final contents of .csv file print $CSV; } }?>Next is the page that does everything dump_data.php<?php require_once 'class.sql2csv.php'; $params = array( 'host' => 'sql113.000space.com', 'user' => 'space_5045542', 'password' => '********', 'database' => 'space_5045542_exceptions' ); $query = 'SELECT * FROM Data'; new SQL2CSV($params, $query);?> Then the button to tell everything to initialize <body> <a href="dump_data.php">Dump table contents</a></body> This will dump all the data from an SQL table onto a CSV which I then open in excel. However as I mentioned I need it to be able to recognize the date stamps from an SQL database, which are there already, but i need to be able to have a few buttons one for yesterday and today, or is there a way to select from one date to the other?. Either way can someone help me edit the above code to my need please? I'm getting a bit frustrated and I cant work out what I'm doing wrong! (I've tried loads of stuff!)CheersKyle Notice from rvalkass: Removed your password from the code! Share this post Link to post Share on other sites
jlhaslip 4 Report post Posted February 11, 2010 The dump_data.php is allowing for the entire db to be retrieved in the query.The Select statement requires a WHERE clause defining the date range. Share this post Link to post Share on other sites
karlosantana 0 Report post Posted February 11, 2010 Where would I put that? I'm still learning! It's taking me AGES! How do I make it do what I want? What do I replace??? And thankyou rvalkass that was a little stupid of me! Share this post Link to post Share on other sites
Quatrux 4 Report post Posted February 12, 2010 We can't help you if you won't provide us with your table information, but theoretically the query would need to look something like that: $query = 'SELECT * FROM Data WHERE {date_column} = '{some kind of date}';or$query = 'SELECT * FROM Data WHERE {date_column} > '{some kind of date}';or$query = 'SELECT * FROM Data WHERE {date_column} < '{some kind of date}';or$query = 'SELECT * FROM Data WHERE {date_column} BETWEEN '{some kind of date}' AND '{some kind of date}'; And so on, as there are plenty of options, you can play only with years, months andeven seconds, depends what you want to do, I suggest you to read sql tutorial of some kind for beginners. Share this post Link to post Share on other sites
karlosantana 0 Report post Posted February 12, 2010 Got it! Excellent Cheers Guys! For some reason after query my syntax doesn't like just nbeing in '' it has to have (" ") For some odd reason! But it works! Your amazing :)Thanks againKyle Share this post Link to post Share on other sites
