Filter a date field.

Anonymous · Apr 15, 2005

I am trying to figure out how to filter out results from a database queiry. One of the fields in a database record contains a field with a date value, I want to filter out results that have a date that is 30 days or older.

The specifics are:

Program using:
http://www.dew-code.com

Is implemented here:
http://www.enhancementresearch.com/partners/directory/new.html

Now I want to filter out results listed here and only list links that are newer than 30 days. It currently lists all of the links that are in my database...sorted by date.

Now this issue post has already been started here:
http://www.dew-code.com/modules/newbb/viewtopic.php?topic_id=301&post_id=1063&order=0&viewmode=flat&pid=1046&forum=8#forumpost1063

However we can not seem to figure it out...if anyone can help I would be most grateful.

Alexej Kubarev · Apr 15, 2005

simply by comparing the unix timestamp with a current date..

$date_current = date("U");
$date = $date_current - (60*60*24*30);
$sql = "SELECT * FROM table_name WHERE date_field>'" .$date. "' ";

Anonymous · Apr 15, 2005

Why do you do all this stuff in PHP, Alexei, when doing it in MySQL is faster?

Code:

SELECT * FROM table_name WHERE date_field > (NOW() - INTERVAL 30 DAY);

Alexej Kubarev · Apr 15, 2005

because i like PHP more

Its easier for me... I prefere doing all in PHP only because in most of my apps there is no need in super high performace and that is not a critical thing to do.. and therefore i prefere doing it in PHP..

Lets say its my "style".. however i am using SQL built-in functions when needed.. (in applications that require it..)

Anonymous · Apr 15, 2005

Lesha,

It still doesn't seem to work. Here is the way that i have it:

if($show == "new"){
$sql .= "added";
$date_current = date("U");
$date = $date_current - (60*60*24*30);
$sql .= "SELECT * FROM $tb_links WHERE Added>'" .$date. "' ";

I will include the whole file for this segment of the code:

Code:

<?
// *******************************************************************
//  include/show.php
// *******************************************************************

if(!isset($sr)){
	
	$sr=0;
}

$get_sites_count = sql_query("
	select
		ID
	from
		$tb_links
");

$total_sites = sql_num_rows($get_sites_count);

$sql = "
	select
		$tb_links.ID as site_id,
		$tb_links.SiteName as site_name,
		$tb_links.SiteURL as site_url,
		$tb_categories.Category as category_name,
		$tb_categories.ID as cat_id,
		$tb_links.Added as added,
		$tb_links.LastUpdate as last_update,
		$tb_links.Description as site_description,
		$tb_links.Country as country,
		$tb_links.HitsIn as hits_in,
		$tb_links.HitsOut as hits_out,
		count($tb_reviews.ID) as total_reviews
	from
		$tb_links
	left join
		$tb_categories
	on
		$tb_links.Category = $tb_categories.ID
	left join
		$tb_reviews
	on
		$tb_links.ID = $tb_reviews.SiteID
	group by
		site_id
	order by 
";

if($show == "new"){
 $sql .= "added";
 $date_current = date("U"); 
 $date = $date_current - (60*60*24*30); 
 $sql .= "SELECT * FROM $tb_links WHERE Added>'" .$date. "' ";

}

if($show == "cool"){
	
	$sql .= "hits_in ";
}

if($show == "pop"){
	
	$sql .= "hits_out ";
}

$sql .= "
		desc
	limit
		$sr, $pp
";

$get_sites = sql_query($sql);
echo draw_sites($total_sites, $get_sites);

?>

Anonymous · Apr 15, 2005

Try my code. There's less margin for error.

Then echo your query and see if it looks like you expect it to. Past it into a MySQL client (NOT phpMyAdmin) and see if you get any errors.

If you get any errors, post them here in their entirety.

Anonymous · Apr 16, 2005

Hey swirlee,

I'm pretty new at this stuff, so I am uncertain as to where and how I would implement his code...sorry...could you please explain further?

Alexej Kubarev · Apr 16, 2005

Swirlee's code is a simple SQL query that you should excecute... it actualy is faster -- as it is SQL built-in functions..

In the code that you have posted i really dont like that $.sql="added"; part as your query will look like:

"added SELECT * FROM...."

try removing that added part... or simply excecute swirlee's query -- you should get the same result..

Anonymous · Apr 16, 2005

Lesha,

Okay, I'm getting pretty frustrated because it still is not working for me... I have tried 3 different variatons, neither of them work...swirlees stuff, I still haven't figured out how to implent it.

Here are the ways that I tried it:

Code:

if($show == "new"){
$sql .= "added";
 SELECT * FROM $sql WHERE added > (NOW() - INTERVAL 30 DAY);
}

Code:

if($show == "new"){ 
$date_current = date("U"); 
$date = $date_current - (60*60*24*30); 
$sql .= "SELECT * FROM $tb_links WHERE Added>'" .$date. "' ";
}

Code:

if($show == "new"){ 
$date_current = date("U"); 
$date = $date_current - (60*60*24*30); 
$sql .= "SELECT * FROM $sql WHERE added>'" .$date. "' ";
}

I am still not getting any results.......what am I doing wrong?

Anonymous · Apr 16, 2005

Code:

if($show == "new"){
$sql .= "added";
 SELECT * FROM $sql WHERE added > (NOW() - INTERVAL 30 DAY);

^^That is incorrect format.

Code:

if($show == "new")
    $sql .= " SELECT * FROM $sql WHERE added > (NOW() - INTERVAL 30 DAY)";

Alexej Kubarev · Apr 16, 2005

For swirlies code use:

if($show == "new"){
$sql = "SELECT * FROM $sql WHERE added > (NOW() - INTERVAL 30 DAY)";
}

however there is one problem... some servers do NOT allow you to excecute several queries at once.. try outputing error.. what database are you sing anyway?

Can be vety helpfull to know that.. for mysql i wouldve done something like this:

$result = mysql_query($sql) or die(mysql_error());

Alexej Kubarev · Apr 16, 2005

for my solution and for swierlies solution you are using different column names... Added and added are different as column names are case sensitive

Anonymous · Apr 16, 2005

Lesha,

The reason why I use one capitalised and the other not is because I am trying to figure out which table I should be comparing....
If you look at the full source code of this file(posted earlier) you will see this little tidbit of code:

Code:

$sql = " 
   select 
      $tb_links.ID as site_id, 
      $tb_links.SiteName as site_name, 
      $tb_links.SiteURL as site_url, 
      $tb_categories.Category as category_name, 
      $tb_categories.ID as cat_id, 
      $tb_links.Added as added, 
      $tb_links.LastUpdate as last_update, 
      $tb_links.Description as site_description, 
      $tb_links.Country as country, 
      $tb_links.HitsIn as hits_in, 
      $tb_links.HitsOut as hits_out, 
      count($tb_reviews.ID) as total_reviews 
   from 
      $tb_links 
   left join 
      $tb_categories 
   on 
      $tb_links.Category = $tb_categories.ID 
   left join 
      $tb_reviews 
   on 
      $tb_links.ID = $tb_reviews.SiteID 
   group by 
      site_id 
   order by 
";

More specifically:

Code:

$tb_links.Added as added,

This is why sometimes I have it capitalised and at times I do not, if you notice, I am also comparing different tables when I change the capitalization as well.

Anonymous · Apr 16, 2005

After trying what you suggested, I still get no results. I will leave the code online so you can see what it looks like:
http://www.enhancementresearch.com/partners/directory/new.html

It says that there are no new sites in this section, yet has 4 pages of listings.....maybe I should post the draw_sites() function to try to figure out what's causing this problem...

Now the other thing is I'm not sure how to find out what database I'm using, again forgive my ignorance..

Spasibo(Thanks)

Serj

Anonymous · Apr 16, 2005

Lesha,

To give you some more input into what's going on we are also working on this problem here:
http://www.dew-code.com/modules/newbb/viewtopic.php?topic_id=301&post_id=1072&order=0&viewmode=flat&pid=1071&forum=8#forumpost1072

Alexej Kubarev · Apr 16, 2005

in what format do you save your dates? i thought they were in unix format... that might be the problem. how about you changing the way you gonna save your dates...or atleast telling me how are they daved.. in what format..

Anonymous · Apr 17, 2005

Alexei,

Here is the lines of code in function draw_sites that displays the date that the site was added:

Code:

.
.
.
ereg("([0-9]{4})([0-9]{2})([0-9]{2})([0-9]{2})([0-9]{2})([0-9]{2})", $sites_array["added"], $regs) . "";
$htmlsrc .= date($FormattedDate,mktime($regs[4], $regs[5], $regs[6], $regs[2], $regs[3], $regs[1]));
.
.
.

Hope this helps, I would also encourage for you to look at some of the headway done on this forum, if you haven't done so already...maybe it will give you some more insight:
http://www.dew-code.com/modules/newbb/viewtopic.php?topic_id=301&post_id=1072&order=0&viewmode=flat&pid=1071&forum=8#forumpost1072

Anonymous · Apr 17, 2005

Also Alexei,

I highly doubt that it has anything to do with the way that the date is formated in the database as I have tried changing the comparison code from ">" to:
"<" to:
"==" to:
"=" to:
"!="

and they all produce the same result, I believe that it has something to do with the query it self. I don't know what...but it has something to do with that....

Alexej Kubarev · Apr 17, 2005

the thing is that its only possible to compare unix timestamp with unix timestamp... its the best way to do it.. and the date should be saved in a unix format i believe..

i will take a better look at the problem thou.. however when i test it: it works for me... both my way and swirlies way..

Anonymous · Apr 18, 2005

Okay, I figured out how the date is stored:

It is a 14 digit timestamp created by inserting now() MySQL resulting in YYYYMMDDHHMMSS

2004-08-14 18:08:04 = 20040814180804

Filter a date field.

Anonymous

Guest

Alexej Kubarev

New member

Anonymous

Guest

Alexej Kubarev

New member

Anonymous

Guest

Anonymous

Guest

Anonymous

Guest

Alexej Kubarev

New member

Anonymous

Guest

Anonymous

Guest

Alexej Kubarev

New member

Alexej Kubarev

New member

Anonymous

Guest

Anonymous

Guest

Anonymous

Guest

Alexej Kubarev

New member

Anonymous

Guest

Anonymous

Guest

Alexej Kubarev

New member

Anonymous

Guest