[RESOLVED] TIMESTAMP equivalent of this

[dud3r]

I need a timestamp equivalent of this:

$result = mysql_query("SELECT COUNT(*) AS unique_members FROM users WHERE last_login > DATE_SUB(now(), interval 1 day)");

The last_login field IS in TIMESTAMP format. However, I want to use that for another field (signup_date) which is in DATETIME format. I can't convert the DATETIME field. Thanks for any help.

[bpat1434]

So, you want to insert a time-stamp as a datetime format?

$dtformat = date('Y-m-d h:i:s', $timestamp);

Or, you want to get a timestamp from a date-time field?

list($date, $time) = explode(' ', $datetime);
list($year, $mon, $day) = explode('-', $date);
list($hour, $min, $sec) = explode(':', $time);

$timestamp = mktime($hour, $min, $sec, $mon, $day, $year);

~Brett

[dud3r]

When I try doing this:

$result= mysql_query("SELECT * FROM users");
while ($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
$dtformat = date('Y-m-d h:i:s', $row['signup_date']); 
}
$result = mysql_query("SELECT COUNT(*) AS unique_new_members WHERE $dtformat > DATE_SUB(now(), interval 1 day)");
while ($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
echo $row['unique_new_members'];
}

It doesn't work. See what I'm trying to do here? I can't figure it out.

[bpat1434]

while ($row = mysql_fetch_array($result,MYSQL_ASSOC)) { 
list($date, $time) = explode(' ', $row['sign_up']);
list($m, $d, $y) = explode('-', $date);
list($h, $mi, $s) = explode(':', $time);
$timestamp = mktime($h, $mi, $s, $m, $d, $y);
}
$result = mysql_query("SELECT COUNT(*) AS unique_new_members WHERE $timestamp > DATE_SUB(now(), interval 1 day)"); 
while ($row = mysql_fetch_array($result,MYSQL_ASSOC)) { 
echo $row['unique_new_members']; 
} 

~Brett

[dud3r]

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/public_html/index.php on line 397

[bpat1434]

You have to run a query in order to use mysql_fetch_array(). The code I posted was to help you, not do a direct copy/paste and expect it to work.

~Brett

[dud3r]

I noticed that and I did run a query. That was the result.

[bpat1434]

Then you have an error in your query somewhere. The query isn't returning a valid result (hence the aptly named error). Add an "or die(mysql_error())" after the mysql_query() and see what you get.

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

~Brett

[dud3r]

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE -100862 > DATE_SUB(now(), interval 1 day)' at line 1

The code doesn't work because of this query:

$result = mysql_query("SELECT COUNT(*) AS unique_new_members WHERE $timestamp > DATE_SUB(now(), interval 1 day)");

You're not telling it what table to get that information from.

[bpat1434]

well, that's why code posted on this forum is a guide, not the literal answer. People make mistakes, oh well.

If you add the "FROM users" or whatever table you're extracting info from, it should work. I also wonder about that timestamp conversion.... You say that "last_login" is already a TIMESTAMP, and yet you want to convert it to a "m/d/Y" format for comparison? Well, DATE_SUB should return a string formating of the date, and you'd have to make a timestamp there as well.

~Brett

[dud3r]

You're not understanding me. Forget everything I wrote above. What I what to do is get today's date in timestamp format and subtract the signup_date filed from it (which is also in timestamp format). Then I want it to count the rows that return the timestamp which is less than or equal to 1 day (to see the number of user registrations in the last day).

[bpat1434]

Okay.... so I showed you how to make a timestamp:

bpat1434 wrote:

list($date, $time) = explode(' ', $row['signup_date']);
list($year, $mon, $day) = explode('-', $date);
list($hour, $min, $sec) = explode(':', $time);

$timestamp = mktime($hour, $min, $sec, $mon, $day, $year); 

Now, you've got two timestamps, then you can do a difference:

$dif = $timestamp - (60*60*60*24);
// Subtract 24 hours from timestamp

Then use that in your query:

SELECT count(*) AS unique_new_users
FROM users
WHERE $dif < UNIX_TIMESTAMP(NOW())

That should work. It counts the rows as unique_new_users where the timestamp of 24 hours ago ($dif) is less than the current UNIX_TIMESTAMP of NOW(). So if $dif is less than NOW(), you'll know they're new. OTherwise, they're old.

~Brett

[dud3r]

As far as I know it should be

$dif = $timestamp - (60*60*24);

Not 606060.

Also, this would be the better/same thing:

$result = mysql_query("SELECT COUNT(*) AS unique_new_members FROM users WHERE signup_date > now()")  or die(mysql_error());
while ($row = mysql_fetch_array($result,MYSQL_ASSOC)) { 
echo $row['unique_new_members']; 
}

However, it returns ALL results.

This is what I have so far:

$result = mysql_query("SELECT * FROM users")  or die(mysql_error()); 
while ($row = mysql_fetch_array($result,MYSQL_ASSOC)) { 
if ((time() - $row['signup_date']) <= 86400) {
echo count($row['signup_date']);
}
}

However, it returns 15 ones (111111111111111), one for each match. That's correct, but how do you add them together to say 15?

[bpat1434]

umm... increment a number. count() counts how many items are in an array.

Do this:

 $counter = 0; // reset the counter
$result = mysql_query("SELECT * FROM users")  or die(mysql_error());
while ($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
if ((time() - $row['signup_date']) <= 86400) {
$counter+1;
}
} 

And your query should probably be:

SELECT COUNT(*) AS unique_new_members 
FROM users 
WHERE signup_date > (now()-(60*60*24))

~Brett

[dud3r]

Neither works. The counter one gives me a blank.
Also you said "count() counts how many items are in an array." That's exactly what I need, because the items in the array are the values that returned TRUE for the if() statement.

[bpat1434]

no no no.... read the manual. It counts how many are in an array:

So: count(array('1', '2', '3', '7', '10')) will return 5.
count('11111') will return 1

A string passed to the count() function will be considered 1 item in an array, and return 1. Otherwise, it counts the number of items in the designated array. Since you're only counting how many items are in the string "unique_new_members" for each row returned, then you'll always get a "1".

And change the line "$counter+1" to "$counter++". That was a mistake on my part. It can take one of two forms:
$counter = $counter+1
or $counter++

If you look at your code, you're looping through each row that is returned by the query one at a time. With that, each row gives a number as "unique_new_members" and so it's counting the string "15" which isn't an array. You can do one of two things:
1.) Change your query to select only those members whos signup_date is later than now()-(606024)
2.) Use the counter example from me

~Brett

[dud3r]

$counter = 0;
$result = mysql_query("SELECT * FROM users")  or die(mysql_error()); 
while ($row = mysql_fetch_array($result,MYSQL_ASSOC)) { 
if ((time() - $row['signup_date']) <= 7776000) {
$counter++;
}
}

Doesn't work either. It returns a blank.

So there is now way to add together all those 1's in the array?

Also this:

$result = mysql_query("SELECT COUNT(*) AS unique_new_members FROM users WHERE (now() - signup_date) <= 86400")  or die(mysql_error()); 
while ($row = mysql_fetch_array($result,MYSQL_ASSOC)) { 
echo $row['unique_new_members']; 
}

returns 0 when it should return 15.

It shouldn't be (now() - 606024) because we're subtracting the time now from the signup date to get a number, and if that number is less than or equal to 1 days (86400) then it should count those rows.

[bpat1434]

Look, this will work:

 $counter = 0;
$result = mysql_query("SELECT * FROM users")  or die(mysql_error());
while ($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
  if ((time() - $row['signup_date']) <= 7776000) {
    $counter = $counter+1;
  }
}
echo $counter;

It's guarnateed to work. I don't have mySQL up and running yet on my test server to get teh query version, but if you give my your DB scheme, I'd be happy to look at it for you.

~Brett

[dud3r]

Ok that works. Thank you so much.

Now where is that button to mark the thread resolved.

[bpat1434]

In "Thread Tools".

Glad you got it working.

~Brett