Time

[slimjim]

What I want to do is get all registered users for the NOW Month and display them in order by oldest date first.

$query="SELECT fname, lname, website, rdate FROM members";

//Using this to display it
<? echo date("d M Y H:i:s",$myrow["rdate"]) ?>

Right it is being displayed like this but not in order by oldest first:
Registration date: 14 Oct 2003 18:59:13

[Carello]

"$query="SELECT fname, lname, website, rdate FROM members";"

Try this instead:

$query="SELECT fname, lname, website, rdate FROM members ORDER BY rdate DESC";

That should "sort" the search, and present it by the date, descending.

[slimjim]

Try this instead:

$query="SELECT fname, lname, website, rdate FROM members ORDER BY rdate DESC";

That should "sort" the search, and present it by the date, descending.

That worked for desending order. What i actually am trying to do is take the register date and query it and list members who's been registered for 1 year, Also list current months new registers.

sorta like an annversary listings of members.

So if Joe registered in janu of last year then jan of this year his name and date will show up. And if jane registered this month as a new member then her name will show up in another table.

But jane will not sure up in either table in febuary on account of she didnt register in Febuary and nor has she been a registered member for a year. But next year she will show up in january as a 1 year member.

Now can this be done all using the register date ?

[weekender]

//get current month and year
$month = date("m");
$year = date("y"); //use Y for 4 digit (2004) or y for 2 digit (04)
$query="SELECT fname, lname, website, rdate ".
    "FROM members ".
    "WHERE MONTH(rdate) = $month ".
    "AND YEAR(rdate) = $year ".
    "ORDER BY rdate DESC";
//will fetch all where rdate is this year, this month.
//to get anniversaries, just search where rdate is this month (no year)

hope that helps

adam

[weekender]

oh btw, one thing that confuses many people is that when mysql fetches a timestamp, it is a mysql timestamp. PHP uses unix timestamps.

mysql are in format "YYYYMMDDHHMMSS"

unix are seconds since the start of the unix epoch (1st jan 1970, 00:00)

so if you fetch a date from mysql and put it into, say, date(), it won't work. You need to do this

$query = "SELECT UNIX_TIMESTAMP(rdate) AS rdate FROM...";

adam

[slimjim]

$month = date("m");
$year = date("y"); //use Y for 4 digit (2004) or y for 2 digit (04)
$query="SELECT fname, lname, website, rdate ".
"FROM members ".
"WHERE MONTH(rdate) = $month ".
"AND YEAR(rdate) = $year ".
"ORDER BY rdate DESC";

If I use the above code it doesn't list any listings.
Now i know i should have one listed.
Do i use this echo ?

<? echo date("M Y",$myrow["rdate"]) ?>

Or did I misunderstand you ?

[weekender]

you need to use that unix_timestamp thing i told you about (above)

$month = date("m"); 
$year = date("y"); //use Y for 4 digit (2004) or y for 2 digit (04) 
$query="SELECT fname, lname, website, UNIX_TIMESTAMP(rdate) AS rdate ". 
"FROM members ". 
"WHERE MONTH(rdate) = $month ". 
"AND YEAR(rdate) = $year ". 
"ORDER BY rdate DESC"; 

//get the result, with error checking
$result = mysql_query($query)
    or die("ERROR: " . mysql_error());

//show the number of rows returned
echo mysql_num_rows($result) . " rows returned<BR>";

while($myrow = mysql_fetch_assoc($result)){
    echo date("M Y", $myrow['rdate']);
    echo "<BR>";
}

elaborated a bit. That should work, if there are matching rows in the db

[slimjim]

Not making sence to me cause it is showing 0 rows and i know i have one date in there for this month.

It is getting inserted like this :

	$dt1=strtotime(date("d M Y H:i:s"));
	q("insert into members (id, login, pswd, fname, lname, email, city, state, country, zip, phone, fax, prcode, status, rdate, website, birthday)values('0','$login','$pswd_1','$fname','$lname','$email','$city','$state','$country','$zip','$phone','$fax','','0','$dt1','$website',$birthdate)");

Here is the field info

rdate int(11) default NULL,

Shows up like 1073525397 ( which is this month )

If this helps at all

[slimjim]

can anyone offer me more help. Sorta been trying to figure this out all day. Now just confused as heck. The last quesry should work that weekender made but it doesnt and i know at least one person should show up and it doesnt.

shows 0 rows.

thank You

[weekender]

assuming you have done all the things like mysql_connect and mysql_select_db, then put in an echo $query, copy the query into phpmyadmin (or mysql command line) and see if it works there.

[Carello]

<?php
mysql_connect("server","user,"password") or die(mysql_error());
mysql_select_db("database") or die(mysql_error());
$query = mysql_query("SELECT fname,lname,webiste,rdate "
 ."FROM members WHERE rdate>$newdate "
 ."ORDER BY rdate DESC");

while ($row = mysql_fetch_array($query) {
echo "Member: " . $row['fname'] . " " . $row['lname'] ."<br>";
echo "Registered: " . $row['rdate'] . "<br>";
}
?>

Set $newdate to the minimum date to search for, if you wish to search for members that registered less than 1 year ago, $newdate should be todays date -1 year.

It might not be pretty, but it should work to your satisfaction.

[slimjim]

Well weekender still not working. ITs like its not finding the month and year or something, because i registered as a member again and tried it and it should have found at least that one. But it keeps showing 0 rows.
BUt when i do a print of the query it is showing this :

SELECT fname, lname, website, UNIX_TIMESTAMP(rdate) AS rdate FROM members WHERE MONTH(rdate) = 01 AND YEAR(rdate) = 04 ORDER BY rdate DESC0 rows returned

WHich is showing me it has the right month and year. But no results found. Which cant be. I tried so many different variations of the code and now I am just dumb.

Using this code :

$month = date("m");  

$year = date("y"); //use Y for 4 digit (2004) or y for 2 digit (04) 
$query="SELECT fname, lname, website, UNIX_TIMESTAMP(rdate) AS rdate ".  

"FROM members ".  

"WHERE MONTH(rdate) = $month ".  

"AND YEAR(rdate) = $year ".  

"ORDER BY rdate DESC";  

//get the result, with error checking 
$result = mysql_query($query) 
    or die("ERROR: " . mysql_error()); 

//show the number of rows returned 
echo mysql_num_rows($result) . " rows returned<BR>"; 

while($myrow = mysql_fetch_assoc($result)){ 
    echo date("M Y", $myrow['rdate']); 
    echo "<BR>"; 
}

[weekender]

i think you're rdate field is not a timestamp - the rdate field will need to be of type TIMESTAMP for MONTH and YEAR to work

[leatherback]

Have you tried:

$month = date("m");   

$year = date("y"); //use Y for 4 digit (2004) or y for 2 digit (04) 
$query="SELECT fname, lname, website, UNIX_TIMESTAMP(rdate) AS Outdate ".   

"FROM members ".   

"WHERE MONTH(rdate) = $month ".   

"AND YEAR(rdate) = $year ".   

"ORDER BY rdate DESC";   

//get the result, with error checking 
$result = mysql_query($query)  

    or die("ERROR: " . mysql_error());  

//show the number of rows returned 
echo mysql_num_rows($result) . " rows returned<BR>";  

while($myrow = mysql_fetch_assoc($result)){  

    echo date("M Y", $myrow['rdate']);  

    echo "<BR>";  

}

(Note the newdate: If you give it the same name as the colum, then in the rest of the query hte UNIX formtatted time will be used. Which doesn't do well in mysql queries, I believe.

[edit:]
Weekender: I think you are right: Look atprevious post:
rdate int(11) default NULL,
[/edit]

[slimjim]

Ok here is what inserts the register date into the rdate field

strtotime(date("d M Y H:i:s"))

This is what shows up in rdate after someone registers

1073826519

Here is how field is set up

rdate int(11) default NULL

I really cant go in and change the way that is setup right now cause it would mess up everyones rdate.

Now is there a way to get this to work with that info.

[leatherback]

hi,

You COULD work with it, I suppose. But.. if I were you:
I'd make a new colum, date/time, and insert all the existing dates into that colum. If I am correct the strtotime will make a unix timestamp already, so you should be able to convert that fairly easily.

It will make future work easier..

J.

[slimjim]

When i do this query :

$query="SELECT fname, lname, website, rdate FROM members ORDER BY rdate DESC";

It shows this result :
Jane Doe 11 Jan 2004 13:08:39 Visit Site

So I know there must be a way for this to work. Some how.