1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Filter mysql rows by timestamp

Discussion in 'MySQL' started by gclass, Nov 15, 2011.

0
  1. #1
    Hi,

    I have a short script to display the content of my table, I would like to tweak it so it will only display rows from the last 72 hours or the last 3 days).
    the first cell in each row is a time stamp created by mysql on the row insert.

    here is the code:
    
    $query_string = "SELECT * FROM $table";
	$result_id = mysql_query($query_string, $connection)
	or die("display_db_query:" . mysql_error());
	// find out the number of columns in result
	$column_count = mysql_num_fields($result_id)
	or die("display_db_query:" . mysql_error());
	// Here the table attributes from the $table_params variable are added
	print("<HTML><HEAD><TITLE>Displaying a MySQL table</TITLE></HEAD><BODY><TABLE><TR><TD>\n");
	print("<TABLE border='2' >\n");
	// optionally print a bold header at top of table
	if($header_bool) {
		print("<TR>");
		for($column_num = 0; $column_num < $column_count; $column_num++) {
			$field_name = mysql_field_name($result_id, $column_num);
			print("<TH>$field_name</TH>");
		}
		print("</TR>\n");
	}
	// print the body of the table
	while($row = mysql_fetch_row($result_id)) {
		print("<TR ALIGN=LEFT VALIGN=TOP>");
		for($column_num = 0; $column_num < $column_count; $column_num++) {
			print("<TD>$row[$column_num]</TD>\n");
		}
		print("</TR>\n");
	}
    Code (markup):
    will be glad to have some help with it,

    Thx
    G
     
    Solved! View solution.
    gclass, Nov 15, 2011 IP
  2. #2
    Can you paste the table structure? Hard to suggest without seeing how the table is organized.

    Most likely something like:

    SELECT * FROM $table WHERE time_column >= DATE_ADD(NOW(), INTERVAL -3 DAY);
     
    jestep, Nov 15, 2011 IP
    gclass likes this.
  3. gclass

    gclass Active Member

    Messages:
    337
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    55
    #3
    the table has 9 columns, the first column is time stamp in YYYY-MM-DD HH:MM:SS format.
    all the other 8 columns are simple text.

    thx
     
    gclass, Nov 15, 2011 IP
  4. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #4
    If you replace the query and the timestamp column name with what I posted it should work.
     
    jestep, Nov 15, 2011 IP
  5. gclass

    gclass Active Member

    Messages:
    337
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    55
    #5
    Jestep - its working :)
    thx for your help.
     
    gclass, Nov 15, 2011 IP