Date Query

[DarkPrince2005]

"select a.session_id,a.reference,a.payment_status,

b.Session,b.Product_Description as Product_Description,b.Product_Price as Product_Price,b.Product_Quantity as Product_Quantity,

c.REF,c.FILE as file,

d.reference,d.file,d.dl_count as dl_count,d.dl_date,d.dl_end

from order_details a

inner join cart b on b.Session = a.session_id

inner join products c on c.REF = b.Product_Description

inner join downloads d on d.reference = a.reference

where d.reference = '".$_GET["Reference"]."'

and c.FILE = '".$_GET["file"]."'

and a.payment_status = '1'

and not isnull(d.dl_count)

and d.dl_count != '0'

and UNIX_TIMESTAMP(d.dl_end) > 'now()'

limit 1"

 

 

Can anyone tell me what I'm doing wrong? I'm trying to select every record after todays date.

[PFMaBiSmAd]

now() returns a DATETIME value, which is either a 'YYYY-MM-DD HH:MM:SS' string or YYYYMMDDHHMMSS.uuuuuu numeric format. By putting now() inside of single-quotes in the query, you are making a string consisting of the characters  'n', 'o', 'w', '(', and ')'. Don't put single-quotes around mysql functions.

 

Since now() returns a DATETIME value, you can only compare it with other DATETIME values. UNIX_TIMESTAMP returns a Unix Timestamp, which is not a DATETIME value and cannot be directly compared with the value now() returns.

 

What is the data type of d.dl_end?

[DarkPrince2005]

datetime

[PFMaBiSmAd]

Doesn't that suggest to you that you could compare it directly to the value that now() returns?

[DarkPrince2005]

 

select a.session_id,a.reference,a.payment_status,

b.Session,b.Product_Description as Product_Description,b.Product_Price as Product_Price,b.Product_Quantity as Product_Quantity,

c.REF,c.FILE as file,

d.reference,d.file,d.dl_count as dl_count,d.dl_date,d.dl_end

from order_details a

inner join cart b on b.Session = a.session_id

inner join products c on c.REF = b.Product_Description

inner join downloads d on d.reference = a.reference

where d.reference = '".$_GET["Reference"]."'

and c.FILE = '".$_GET["file"]."'

and a.payment_status = '1'

and not isnull(d.dl_count)

and d.dl_count != '0'

and d.dl_end > now()

limit 1

I tried, and it doesn't work

[PFMaBiSmAd]

it doesn't work

It didn't work before either. Just telling us that something doesn't work does not provide any useful information so that someone could actually help. We know it does not work or you would not be posting in a help forum.

 

What does it do v.s. what you expect? What symptoms or errors do you get? We are not standing right next to you, nor do we have access to your server, so you must communicate sufficient information about what you are doing and what happens in front of you when you do it.

 

For all we can tell from the information you have posted, you don't have any matching data or the column is not actually a datetime data type or there is something else wrong in the query and it is producing a sql error...

[DarkPrince2005]

OK, I'm producing a downlist from purchased records and inserting it into a downloads table with a expiry date into the dl_end field. and when the link is clicked it checks if the expiry date is before or after the current date. if the dl_end date is before the current date it does nothing. if it's after the download continues. But currently it downloads eveerytime.

[PFMaBiSmAd]

That's nice  But, the only thing that tells us is that the greater-than comparison between the values in d.dl_end and now() is true or that you have a logic error in your code that is always allowing the download.

[DarkPrince2005]

I can't see where the error in my logic might be...I've looked oer it countless times.

 

<?php
// Sample usage

	include "inc/config.php";
	mysql_connect("$host","$user","$pass");
	mysql_select_db("$dbase");
	$date = date('Y-m-d H:i:s');
	$check = mysql_query("select a.session_id,a.reference,a.payment_status,
	b.Session,b.Product_Description as Product_Description,b.Product_Price as Product_Price,b.Product_Quantity as Product_Quantity,
	c.REF,c.FILE as file,
	d.reference,d.file,d.dl_count as dl_count,d.dl_date,d.dl_end
	from order_details a
	inner join cart b on b.Session = a.session_id 
	inner join products c on c.REF = b.Product_Description
	inner join downloads d on d.reference = a.reference
	where d.reference = '".$_GET["Reference"]."'
	and c.FILE = '".$_GET["file"]."'
	and a.payment_status = '1'
	and not isnull(d.dl_count)
	and d.dl_count != '0'
	and now() <= d.dl_end
	limit 1");


if(mysql_num_rows($check) > 0){

function sendTest()
{	
$resultssss = $_GET["file"];
if(file_exists($resultssss)){
$res = sendFile($_GET["file"], 'application/octet-stream');
if ($fh = @fopen(dirname(__FILE__).'/result.txt','w')) {
  @fwrite($fh, var_export($res, true));
  @fclose($fh);
  }
}
}

// The sendFile function streams the file and checks if the
// connection was aborted.

function sendFile($path, $contentType = 'application/octet-stream')
{


ignore_user_abort(true);

header('Content-Transfer-Encoding: binary');
header('Content-Disposition: attachment; filename="' .
basename($path) . "\";");
header("Content-Type: $contentType");

$res = array('status'=>false,'errors'=>array(),'readfileStatus'=>null,'aborted'=>false);

$res['readfileStatus'] = readfile($path);
	if ($res['readfileStatus'] === false) {
	$res['errors'][] = 'readfile failed.';
	$res['status'] = false;
	}

if (connection_aborted()) {
$res['errors'][] = 'Connection aborted.';
$res['aborted'] = true;
$res['status'] = false;
}

return $res;
}

// Save the status of the download to some place

function saveDownloadStatus($res)
{
$ok = false;
$fh = fopen('download-status-' . $_SERVER['REMOTE_ADDR'] . '-' .
date('Ymd_His'), 'w');
	if ($fh) {
	$ok = true;
	if (!fwrite($fh, var_export($res, true))) {
	$ok = false;
	}
		if (!fclose($fh)) {
		$ok = false;
		}
	}
return $ok;
}


sendtest();
exit;
}
?>

[PFMaBiSmAd]

So, you have changed the logic to now() <= d.dl_end, instead of d.dl_end > now(). Which do you want to do (for all we know you are testing with values having today's date) and have you actually tried with values of d.dl_end that should and should not work? Have you executed the query directly against your database to make sure it returns what you expect?

 

What does one of your d.dl_end values look like?

 

 

[DarkPrince2005]

SELECT a.session_id, a.reference, a.payment_status, b.Session, b.Product_Description AS Product_Description, b.Product_Price AS Product_Price, b.Product_Quantity AS Product_Quantity, c.REF, d.file AS

FILE , d.reference, d.dl_count AS dl_count, d.dl_date, d.dl_end AS dl_end

FROM order_details a

INNER JOIN cart b ON b.Session = a.session_id

INNER JOIN products c ON c.REF = b.Product_Description

INNER JOIN downloads d ON d.reference = a.reference

WHERE d.reference = '".$_GET["Reference"]."'

AND d.file = '".$_GET["file"]."'

AND a.payment_status = '1'

AND NOT isnull( d.dl_count )

AND d.dl_count != '0'

and d.dl_end > NOW()

LIMIT 1

 

 

It was an error in my query