Jump to content


Photo

Need help selecting a MySQL table from today's date.


  • Please log in to reply
3 replies to this topic

#1 bios

bios
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 29 October 2003 - 07:28 PM

Basically what I want to do is extract todays orders from a online store.

$select = \"SELECT * FROM orders\";

But I only want it to extract todays date from my orders, or maybe orders with a status of pending or the number 3.

My colum for date purchaced is named: date_purchased and it formats the date and time like this: 10/29/2003 9:28:16 PM. I want all with the date of today to be downloaded.

My colum for orders status is named: orders_status and it formats the status by using a number: 1,2,3. I also want to have all with a status of 1 to be downloaded.

Can anyone help me with this code?

#2 bios

bios
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 29 October 2003 - 07:31 PM

my whole code is this:


<?php/********************************************This code will extract the data from your table and formatit for an excel spreadsheet download. It is very quick,simple, and to the point. If you only want to extract certain fields and not the whole table, simply replacethe * in the $select variable with the fields you wantto extract./********************************************/// Include Server parametersrequire(\'includes/configure.php\');define(db_link, mysql_connect(DB_SERVER,DB_SERVER_USERNAME,DB_SERVER_PASSWORD));mysql_select_db(DB_DATABASE);//Write the query, call it, and find the number of fields$select = "SELECT * FROM orders";$export = mysql_query($select);$count = mysql_num_fields($export);//Extract field names and write them to the $header variablefor ($i = 0; $i < $count; $i++) {	$header .= mysql_field_name($export, $i)."t";}// Extract all data, format it, and assign to the $data variablewhile($row = mysql_fetch_row($export)) {	$line = \'\';	foreach($row as $value) {          	  if ((!isset($value)) OR ($value == "")) {  	$value = "t";  } else {  	$value = str_replace(\'"\', \'""\', $value);  	$value = \'"\' . $value . \'"\' . "t";  }  $line .= $value;	}	$data .= trim($line)."n";}$data = str_replace("r", "", $data);//Set the default message for zero recordsif ($data == "") {	$data = "n(0) Records Found!n";      }//Set the automatic download sectionheader("Content-type: application/octet-stream");header("Content-Disposition: attachment; filename=orders.xls");header("Pragma: no-cache");header("Expires: 0");print "$headern$data";?>


#3 GeoffOs

GeoffOs
  • Members
  • PipPip
  • Member
  • 24 posts
  • LocationCheshire, England

Posted 30 October 2003 - 08:18 PM

It sound like you need a where clause on your query
Your query should be something like this:
$select  = "SELECT * FROM orders";$select .= " where orders_status = 1";$select .= " and DATE(date_purchased) = DATE(sysdate())";

The DATE returns only the current date and not the time, so the query will return all records that were created today and have a status of 1
Beyond a critical point within a finite space, freedom diminishes as numbers increase....[br]The human question is not how many can possibly survive within the system, but what kind of existence is possible for those who do survive."[br]-- Frank Herbert - Dune

#4 bios

bios
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 31 October 2003 - 04:39 PM

thanks i actually got it working yesterday, but now i do need help selecting the file off the harddrive and inserting this it back into the database and updating the order status.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users