Honesty Rocks! truth rules.

Filter By Date Using This Script Why do I subject myself to this??? Why?

HOME      >>       Websites and Web Designing

karlosantana

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 files
class.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!)

Cheers
Kyle

Notice from rvalkass:
Removed your password from the code! :)


jlhaslip

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.


karlosantana

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!


Quatrux

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. :)

karlosantana

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