php/mysql question

brailleschool

I have two rows in a table called membership.

one is called login, the other is called suspended.

Membership looks like so:

CREATE TABLE login (
  id int(11) NOT NULL auto_increment,
  username varchar(250) NOT NULL default '',
  password varchar(250) NOT NULL default '',
  email varchar(250) NOT NULL default '',
  fname varchar(250) NOT NULL default '',
  sname varchar(250) NOT NULL default '',
  country text NOT NULL,
  interests text NOT NULL,
  age text NOT NULL,
  iam text NOT NULL,
  gender text NOT NULL,
  school text NOT NULL,
  status text NOT NULL,
  aboutu text NOT NULL,
  whyjoin text NOT NULL,
  hobbies text NOT NULL,
  memberstatus varchar(10) NOT NULL default 'Member',
  userpic varchar(255) NOT NULL default '',
  userpicalt varchar(255) NOT NULL default '',
  warned varchar(18) NOT NULL default 'No - Zero of Three',
  new varchar(8) NOT NULL default 'Yes',
  newsletter char(3) NOT NULL default 'Yes',
  lastlogin_date varchar(100) NOT NULL default '??-??-??',
  lastlogin_time varchar(100) NOT NULL default '??:??:??',
  account_active char(3) NOT NULL default 'No',
  welcomemsg char(3) NOT NULL default 'Yes',
  PRIMARY KEY  (id),
  FULLTEXT KEY country (country)
) TYPE=MyISAM PACK_KEYS=0 AUTO_INCREMENT=9875760;

the suspended row looks like so:

CREATE TABLE suspended (
  id int(11) NOT NULL auto_increment,
  username text NOT NULL,
  PRIMARY KEY  (id)
) TYPE=MyISAM AUTO_INCREMENT=1;

This is my question. I am going to put another column in the login table called reg_date. reg_date is going to show the DATE they registered.

What I am looking to do is this:
When a user registers, the login table within membership is populated with their information. (which works.)

If a user has not logged into their account within a specified time, lets say 30 days from the date they registered, then I would like to have a script that will automatically send the usernames concerned (when executed) into the suspended database (populate -- without deleting the information within the login database).

I am still very new to PHP/MySQL and would like to know how this can be done.

Please advise if you can.

dud3r

$result = mysql_query("SELECT * FROM login ORDER BY id");
while ($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
if ($row['reg_date'] < 2592000) { 
$suspended_username=$row['username'];
//inserts that user into `suspended`
mysql_query("INSERT INTO suspended (username) VALUES ('$suspended_username')");	
}
else {
	echo "no records found";
	exit;
}
}

You'll need to play around with that, but it should give you an idea.

brailleschool

thanks... working on it now

dud3r

That's weird. I haven't testing it out. I'm guessing here. Try this:

$month = "259200";
$usr_tbl = "login"; //table when user information is kept
$result = mysql_query("SELECT * FROM $usr_tbl WHERE (reg_date < '$month')");
while ($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
$suspended_username=$row['username'];
mysql_query("INSERT INTO suspended (username) VALUES ('$suspended_username')");	
}

brailleschool

i have

<?
include("config.php");
$connection = mysql_connect("$server", "$db_user", "$db_pass");
$db = mysql_select_db("$database", $connection);
$result = mysql_query("SELECT * FROM login ORDER BY id"); 
while ($row = mysql_fetch_array($result,MYSQL_ASSOC)) { 
if ($row['reg_date'] < 2592000) {  

$suspended_username=$row['username']; 
//inserts that user into `suspended` 
mysql_query("INSERT INTO suspended (username) VALUES ('$suspended_username')");
echo "$suspended_username suspended.<br>";  

} 
else { 
    echo "No users were suspended."; 
    exit; 
} 
}
?>

suspends all users.

As an example:
I would like to set the suspend time to 30 days. So if you register today ($reg_date = 01072005) and you dont login within 30 days ($lastlogin_date would then equal 02062005 in this example) then it will only suspend those users. Also, if $lastlogin_date = '??-??-??' and they registered 30 days or so prior to executing the script to auto suspend, then they get suspended.

But only suspends users who have not logged within a given time (in days) from the date they registered (which of course, would be different for each or a few users.)

Also, if the user exists in the suspend list, then not to add them to the suspend list

brailleschool

OK, I am getting closer but its still not working the way it should lol.

This is what I am trying to do now. I am getting todays date, and then taking 30 days away from it, then checking the database to see if there are any dates in the login database for dates 30 days or older.

However, when I echo the variables for the dates, I am getting the following (more than 30 days, actually it goes into years and thats not what I want.)

Please advise.

<?
// define the file that houses the global variables
include("config.php");

// connect to the server
$connection = mysql_connect("$server", "$db_user", "$db_pass");

// connect to the database
$db = mysql_select_db("$database", $connection);

// select every entry in the database by ID column
$result = mysql_query("SELECT * FROM login ORDER BY id"); 

// get todays date
$TodaysDate=date("m-d-Y");

// time in seconds
$Time = time();

// time converted to number of days
$TimeInDays = ($time / 60 / 60 / 24);

// subtract 30 days from this
$SubtractDays = $TimeInDays - 30;

// reconvert to seconds
$NewTime = ($SubtractDays * 24 * 60 * 60);

// display new date
$SuspendAmount = date("m-d-Y", $NewTime);

// echo todays date and the suspend date to the screen (suspend date is todays date minus 30 days)
echo "Todays Date: $TodaysDate<br>";
echo "Suspend Amount Value: $SuspendAmount<br>";

// start going through the database finding all user accounts that logged in over 30 days ago and add the
// username to the suspended database.
while ($row = mysql_fetch_array($result,MYSQL_ASSOC)) { 
if ($row['lastlogin_date'] < $SuspendAmount) {  

$SuspendedUsername=$row['username']; 
//inserts that user into `suspended` 
mysql_query("INSERT INTO suspended (username) VALUES ('$SuspendedUsername')");
echo "$SuspendedUsername suspended.<br>";  

} 
else { 
    echo "No users were suspended."; 
    exit; 
} 
}
?>

When I do the:

echo "Todays Date: $TodaysDate<br>";
echo "Suspend Amount Value: $SuspendAmount<br>";

The following is printed to the screen:

Todays Date: 01-07-2005
Suspend Amount Value: 12-31-1969
admin suspended.
sweetsugar_56 suspended.

admin and sweetsugar_56 are administration usernames, they log in daily. So they definately wouldnt need to be suspended. The other usernames that are 30 days or older since last login stay where they are instead of the username being "copied" to the suspended database UGH UGH UGH

Please help if you can.

rajavel

u can try using this function

<?php
echo "today = ".date("m/d/Y");
echo "<br>tmrw = ".date("m/d/Y",mktime(0, 0, 0, date("m")  , date("d")+1, date("Y")));
echo "<br>minus 30 = ".date("m/d/Y",mktime(0, 0, 0, date("m")  , date("d")-30, date("Y")));
echo "<br>Plus 30 = ".date("m/d/Y",mktime(0, 0, 0, date("m")  , date("d")+30, date("Y")));
?>

the output will be

today = 01/08/2005
tmrw = 01/09/2005
minus 30 = 12/09/2004
Plus 30 = 02/07/2005

u can add the registered date by 30 and then compare it with the current date....

thx

brailleschool

Sure does calculate the days correctly which I was looking to do. I attempted to use that to take todays date, minus the 30 days from it and then tell the script to suspend all users that were 30 days or more over the last time they logged in (account being idle.)

However, it still is not suspending the right accounts.

This is what I have: (not sure if i have it right or not.)

<?
// define the file that houses the global variables

include("config.php");

// connect to the server

$connection = mysql_connect("$server", "$db_user", "$db_pass");

// connect to the database

$db = mysql_select_db("$database", $connection);

// select every entry in the database by ID column

$result = mysql_query("SELECT * FROM login ORDER BY id"); 

// echo todays date, tomorrows date and 
// todays date with 30 days taken off

echo "today = ".date("m/d/Y"); 
echo "<br>tmrw = ".date("m/d/Y",mktime(0, 0, 0, date("m")  , date("d")+1, date("Y"))); 
echo "<br>minus 30 = ".date("m/d/Y",mktime(0, 0, 0, date("m")  , date("d")-30, date("Y"))); 

// Now lets start doing the mathematics
// $TodaysDate is set to the value of todays date

$TodaysDate = date("m-d-Y");

// Minus 30 days
// $SubtractDays is set to the value of todays date
// minus the 30 days.

$SubtractDays = date("m-d-Y", mktime(0, 0, 0, date("m"), date("d") - 30, date("Y")));


// start going through the database finding all
// user accounts that logged in over 30 days ago 
// and add the username to the suspended 
// database.

// fetch all the information that is relevant from the database

while ($row = mysql_fetch_array($result,MYSQL_ASSOC)) { 

// if the last login date $row['lastlogin_date'] and the
// $SubtractDays value is 30 days or more in 
// difference, suspend their usernames by copying 
// the username from login to suspend

if ($row['lastlogin_date'] < $SubtractDays) {  

$SuspendedUsername=$row['username']; 

//inserts that user into `suspended` 

mysql_query("INSERT INTO suspended (username) VALUES ('$SuspendedUsername')");

// if users were suspended, echo the username to the screen

echo "$SuspendedUsername suspended.<br>";  

} 

// if users were not suspended, tell me

else { 
    echo "No users were suspended."; 
    exit; 
} 
}
?>

I will try to explain the process:

When a users registers for the first time, the $row['lastlogin_date']; is set to ??-??-??. When the user logs in for the first time, the $row['lastlogin_date']; is updated with the date of login in the form at of M-DD-YYYY, for example: 1-7-2005. Everytime the user logs in, this field is updated with the current date of login. What I am looking to do is calculate todays date (when I execute the script.) and subtract 30 days from todays date. Then the script is to look through the database and find all users that have not logged in within 30 days or more of todays date and suspend their usernames (by copying the username from login to suspended.)

That is the problem I am finding right now. Does that make sense?

rajavel

ur logic is correct. check whether the control is coming inside the loop or not......if it is...convert $row['lastlogin_date'] to date using strtotime().........

look at this code i hv tried.......

<?php
echo "today = ".$_today = date("m/d/Y");
echo "<br>tmrw = ".$_tmrw = date("m/d/Y",mktime(0, 0, 0, date("m")  , date("d")+1, date("Y")));
echo "<br>minus 30 = ".$_m30 = date("m/d/Y",mktime(0, 0, 0, date("m")  , date("d")-30, date("Y")));
echo "<br>plus 30 = ".$_p30 = date("m/d/Y",mktime(0, 0, 0, date("m")  , date("d")+30, date("Y")));

echo "<br>date that is to be checked = ".$_date = "Dec-12-2004";

if ($_date < $_m30){
	echo "<br>".$_date." is lesser than ".$_m30;
} else {
	echo "<br>".$_date." is greater than ".$_m30;
}
?>

the output i am getting is

today = 01/10/2005
tmrw = 01/11/2005
minus 30 = 12/11/2004
plus 30 = 02/09/2005
date that is to be checked = Dec-12-2004
Dec-12-2004 is greater than 12/11/2004

Roger_Ramjet

You should be doing this in sql . Instead of selecting all users and then looping through in code, just select the right users in the first place; and personally I don't bother with unix timestamps but use mysql date and datetime column types. Much easier to use the date functions so change your varchar date and varchar time columns to one datetime column. Then in 1 query you can do it all.


$sql = "INSERT INTO suspended (username) SELECT username FROM login WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) > lastlogin";

// This will calculate the date 30 days ago and select all users who have not logged in on or since that date

Always try to do it in SQL: 20 times faster than code, more reliable when large nos of records involved, easier to code once you know the language and basic functions.

rajavel

i agree with Roger Ramjet....its a better idea.............

brailleschool

Originally posted by Roger Ramjet
You should be doing this in sql . Instead of selecting all users and then looping through in code, just select the right users in the first place; and personally I don't bother with unix timestamps but use mysql date and datetime column types. Much easier to use the date functions so change your varchar date and varchar time columns to one datetime column. Then in 1 query you can do it all.
$sql = "INSERT INTO suspended (username) SELECT username FROM login WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) > lastlogin";

// This will calculate the date 30 days ago and select all users who have not logged in on or since that date
Always try to do it in SQL: 20 times faster than code, more reliable when large nos of records involved, easier to code once you know the language and basic functions. [/B]

I changed the field to date, executed the code and nothing happened.

For the test user, I had the reg_date field set at 2001-07-20 and is a date column.

the lastlogin_date field is also set to date and has a value of 2001-08-20 which makes it exactly one month since login. Nothing was moved over to suspend.

Roger_Ramjet

I'm surpirsed at that. 2001-08-20 ?? That's a lot more than 30 days ago.

My example subtracts 30 days from today's date and compares it to the lastlogin date field. Just try using the SELECT part as a single query and echo the output, see if it selecting the correct records first. May just be field names or something. Then we'll look at the insert once we know that we are selecting the correct data.

rajavel

plzzz paste the latest code u r using... brailleschool...........

brailleschool

Originally posted by rajavel
plzzz paste the latest code u r using... brailleschool...........

<?
// define the file that houses the global variables 

include("config.php"); 

// connect to the server 

$connection = mysql_connect("$server", "$db_user", "$db_pass") or die ("Cannot login to the server using the login credentials."); 

// connect to the database 

$db = mysql_select_db("$database", $connection) or die ("Cannot select database: $database"); 

$sql = "INSERT INTO suspended (username) SELECT username FROM login WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) > lastlogin_date";
?>

NOTES: lastlogin_date is the name of the column that shows the date they last logged in.

I am not sure what DATE_SUB means.

I have multiple hundreds of records in the database. I would like to know if you are able to do the format of MM-DD-YYYY without having to redo every single record. setting the column to DATE will do it in YYYY-MM-DD if I am not mistaken.

Right now, it is not moving any usernames over.

brailleschool

Originally posted by Roger Ramjet
I'm surpirsed at that. 2001-08-20 ?? That's a lot more than 30 days ago.

My example subtracts 30 days from today's date and compares it to the lastlogin date field. Just try using the SELECT part as a single query and echo the output, see if it selecting the correct records first. May just be field names or something. Then we'll look at the insert once we know that we are selecting the correct data.

I did an echo and was given nothing on screen.

I went into PHPMyAdmin, clicked on the SQL tab and inserted the following:

INSERT INTO suspended (username) SELECT username FROM login WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) > lastlogin_date;

and it added 280 usernames -- many of them were not even close to the 30 day mark. lol.

Roger_Ramjet

date_sub means date subtract or subtract x from a date and return the result.

Roger_Ramjet

Originally posted by brailleschool

NOTES: lastlogin_date is the name of the column that shows the date they last logged in.

I am not sure what DATE_SUB means.  

I have multiple hundreds of records in the database.  I would like to know if you are able to do the format of MM-DD-YYYY without having to redo every single record.  setting the column to DATE will do it in YYYY-MM-DD if I am not mistaken.

Right now, it is not moving any usernames over. [/B][/QUOTE] 

MySQL store dates as yyyy-mm-dd, so if that is not what you have then you are not using a date column type. No wonder the date functions are not working. Storage and display are two separate matters, since mysql does not display the dates only stores them.

You use php [URL=http://uk.php.net/manual/en/function.date.php]date function[/URL]  to format the output that you want. By default it uses the local format of the machine it is running on. So if your server is set up as US locale then you will get mm-dd-yyyy. If set up as eg UK locale then you would get dd-mm-yyyy. You can also customise the format very many ways.

Now. Create 2 more columns in your table, both of them date or datetime. Simply copy the data from the existing columns to the new ones and mysql will take care of the conversion itself. Then you can run your queries as we have scripted them for you, only using the new colimns. OK.

brailleschool

is there a fast way to send all dates from lastlogin_date to the new field? I have over 27000 records.

table now looks like so:

CREATE TABLE login (
id int(11) NOT NULL auto_increment,
username varchar(250) NOT NULL default '',
password varchar(250) NOT NULL default '',
email varchar(250) NOT NULL default '',
fname varchar(250) NOT NULL default '',
sname varchar(250) NOT NULL default '',
country text NOT NULL,
interests text NOT NULL,
age text NOT NULL,
iam text NOT NULL,
gender text NOT NULL,
school text NOT NULL,
status text NOT NULL,
aboutu text NOT NULL,
whyjoin text NOT NULL,
hobbies text NOT NULL,
memberstatus varchar(10) NOT NULL default 'Member',
warned varchar(18) NOT NULL default 'No - Zero of Three',
new varchar(8) NOT NULL default 'Yes',
newsletter char(3) NOT NULL default 'Yes',
lastlogin_date varchar(100) NOT NULL default '??-??-??',
lastlogin date NOT NULL default '0000-00-00',
lastlogin_time varchar(100) NOT NULL default '??:??:??',
account_active char(3) NOT NULL default 'No',
welcomemsg char(3) NOT NULL default 'Yes',
reg_date varchar(100) NOT NULL default '',
registerdate date NOT NULL default '0000-00-00',
PRIMARY KEY (id),
FULLTEXT KEY country (country)
) TYPE=MyISAM PACK_KEYS=0 AUTO_INCREMENT=28781;

Roger_Ramjet

UPDATE table SET new_field1=old_field1, new_filed2=old_field2

Looking back at your table layout, I'm sure we covered this last week.