Grab inactivity

[topflight]

I have to tables in the database. One is called members and another one is called hours I am trying to figure out how can I get the members who haven't filed any hours with 30 days after the last hours filed also get members who haven't filed hours 14 days after their join date. Is their a way to do that if so may you please give me an example thanks.

[corbin]

What does your table structure look like?  I'm going to assume you have DATETIME fields.

 

 

(By the way, technically your question is a MySQL question, not PHP.)

 

 

 

So, I'm going to assume this structure:

 

CREATE TABLE members (

    user_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

    user_name varchar(32) NOT NULL,

    when_joined DATETIME

);

 

CREATE TABLE hours (

    user_id INT NOT NULL,

    what_day DATE,

    hours TINYINT NOT NULL

);

 

First the 14 days after join date thing:

 

The first thing that comes to mind is:

 

SELECT m.* FROM members m LEFT JOIN hours h ON h.user_id = m.user_id WHERE ISNULL(hours);

 

Which is flawed because it will JOIN the entire two tables, and then do the WHERE (if my logic is correct).

 

I'm hoping someone better at SQL can come along with something better.

 

 

 

As for 30 days after the last hours....  That's a bit hard.  Technically you want this:

 

-Find the second to last hours entry, find the last, subtract the days and see if it's > 30.

 

Easy in logic, but in SQL that's a bit hard.

 

Actually, without a subquery, a LIMIT clause and so on, I have no idea how to do that one.

 

 

I can tell you the ghetto, slow way if you want, but I would feel dirty.  ;p.

 

[Brian W]

use two fields in your table, join_date and last_filed (use date or int(16) with unix time stamp).

When they join, set both fields to the date and time they are registering. Then use the several functions out there to determin the difference in days between the current date and those two fields. A few if statements later and you have code that will execute when they try logging in or something.

Or, you'll need to write a chron job which I don't know anything about but that will allow you to have this done automatically without user/admin interaction.

Either way, when you making those fields, I'd use the unix time stamp... its easier to handle imo.

 

I didn't read corbin's post... hope I don't step on toes

 

[corbin]

We suggested different things.  You suggested he slightly denormalize his structure (which I thought about suggesting, and would probably be much better performance wise, and I suggest a way to do it without denormalizing structure.

 

 

 

 

I do have a question though...  Why int(16)?

 

First off, 16 has nothing to do with the size of the field so it affectively does nothing.

 

Also, 4294967295 is the most an int (unsigned in that case) can ever be.  The length is 10.

[Brian W]

Corbin, you have opened my eyes to something I had not known. Why does it allow me to tell it to accept longer numbers such as 16 digits? Numbers over 10 digits long must use float? 16 was just a number I pulled out that I knew should be large enough for the timestamp.

[topflight]

so I am confused what it the m for in the query? and how should I go upon doing this?

[corbin]

Brian W:

 

 

Well, MySQL will limit the display to that many digits, but it cannot hold over what ever the binary size of the type is (var types such as varchar do not have a traditional limit though).

 

 

So yes, you would have to store larger numbers in a different field type.  BIGINT or what ever.

 

 

The only real use I've ever seen of "limiting" the size of a field is to use ZEROFILL with it.

 

(I.E. INT(4) zerofilled would return 0001 instead of 1.)

 

 

topflight:

 

 

m is an alias, a table alias to be specific.

 

 

SELECT .... FROM members m is short for SELECT .... FROM members AS m.

 

 

Once an object in MySQL is aliased, it can be referred to by its alias in the query.

 

(Technically sometimes you have to use the full name in spots because of the parsing/execution order of SQL statements, but I don't feel like getting into that.)