Registered dates missing - Getting them from SMF table

[phil_roy]

Hi all,

I've got a substantial number of users (100+) who I've just noticed don't have a "Member Since" value when looking at their profile using Community Builder in a site that has a 1.1.4 bridged version of SMF.

Example....
http://www.nzmac.com/profile/task,userProfile/user,578/

On investigating, I've found all seem to have a registered date in SMF....
http://www.nzmac.com/component/option,com_smf/Itemid,178/action,profile/u,578

And on checking in phpMyAdmin, it's clear that registered dates are in the smf_members table but not in the jos_users table.

I've tried running sync in the SMF bridge and CB components (yes, I know the latter doesn't make sense) but had no luck.

I'm wondering if anyone could explain what the SQL command would be to take the registered date for each smf_member and place those into their registered date for their jos_users ID?

Even better, if anyone knows an approach that would only do this IF AND ONLY IF their registered date in jos_users is "0000-00-00 00:00:00"

From what I can see, new members are getting a registered date inserted properly, so I'm not sure when and how the glitch occurred.

Cheers,

Phil

[Kindred]

currently, the sync does not sync the last login date nor the member since date....



you can TRY this file...   but PLEASE PLEASE PLEASE backup your database first. This file makes changes directly to the smf and/or joomla databases...   there is no easy way to undo these changes. If you run this program, things WILL be changed.

first thing...  set this line near the top of the file:
$import_type=6;

as noted in the file:
// 1 for import from joomla into SMF, 2 for syncing the realname from joomla to SMF
// (If not already set in SMF. If set in SMF, it will sync the joomla realname to match SMF)...
// 3 lists SMF users, 4 lists joomla users...
// 5 sets dates in SMF, 6 sets dates in joomla...

this utility should work on bridge v 3.19a or 1.1.4

[phil_roy]

You drop this into the root level or your joomla site or in your forum folder and then run it?

Thanks, Phil

[phil_roy]

I think it's meant to go at the top level of the site, but it keeps giving me the error...

Warning: main(/SSI.php): failed to open stream: No such file or directory in /home/nzmaccom/public_html/fix_imports.php on line 34

Fatal error: main(): Failed opening required '/SSI.php' (include_path='.:/usr/lib/php:/usr/local/lib/php') in /home/nzmaccom/public_html/fix_imports.php on line 34

Phil

[Kindred]

it needs to go into the joomla directory...

[phil_roy]

Sorry, that's what I meant by top level....I put it into my joomla directory and get the error I mentioned.

Phil

[Kindred]

I'll take a look at it tomorrow.

[paki]

try this

line 34: require_once($smf_path . '/SSI.php');   

change to

require_once($smf_path . 'smf-forum-dir/SSI.php');   replace smf-forum-dir with the folder you installed SMF in

[phil_roy]

Eeek....now every user in Joomla has a reg date and late visit date at 0! Worse than before.

In SMF, dates and things still seem OK?

I've tried synching, but it seems to be failing. Note I'm having another synch issue also....
http://www.simplemachines.org/community/index.php?topic=88528.0

Phil

[Kindred]

remember.... I did tell you to back up your database before running this program?

it deleted all the dates because it tried to run through all the users in the joomla table without having an smf match...  hence the value it was reading from was "0"

Please restore the jos_users table

[phil_roy]

OK, I'm happy to do this, but I'm still (before restore) at the same place I'm trying to resolve....the SMF table still has all the dates I need for each user in them....before, some jos_users has registered dates and last online dates missing...now all do.

I'm basically at the same point....wanting to bring registered and last oneline dates across into jos_users.

Phil

[phil_roy]

OK,

Rather than restore the jos_users table date fields that are currently at zero (since it some date settings were missing anyway and the dates are still all there in smf) I'm trying to figure out why the dates didn't transfer across.

I'm not a coder, but I note that the two fields in jos_users (registerDate and lastvisitDate) are both defined as being type "datetime"

In smf_members, the two fields (dateRegistered and lastLogin) are of type "int(10)"

Would that not have any effect on the ability for the date information to be transferred from one table to the next?

Phil

[Kindred]

hmmmm...  as I said, I will take a look...

this utility worked for me to set the dates going the other way (joomla to smf) but I never tested it going smf to joomla for dates...

[phil_roy]

Thanks and I really really appreciate it. I can kinda interpret code and see what it does, but can't write the stuff at all. I was just looking at a file from the joomlahacks bridge and spotted this in the midst of a function

    $row->registerDate = date("Y-m-d H:i:s",$data->dateRegistered);

...which kinda looks significant??...but I'll shut up at that point as I'm probably way off track.

Thanks for anything you can offer. Night.

Phil

[phil_roy]

Anyone? Sorry to bump this but I can't figure out how to alter the code to go from INT to Datetime format.

Phil

[Kindred]

no guarantees...  but try this...

[phil_roy]

Thanks, and I apologise for the cross posting to the Joomla site.

I decided to not only download a backup, but also attempt all this on a local version to start with. Interstingly, as I ran the SQL command, one table came up with an error. Here's what the SQL from the live site is telling me to run to recreate the table locally...

DROP TABLE IF EXISTS `smf_log_online`;
CREATE TABLE `smf_log_online` (
  `session` varchar(32) NOT NULL default '',
  `logTime` timestamp NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `ID_MEMBER` mediumint( unsigned NOT NULL default '0',
  `ip` int(10) unsigned NOT NULL default '0',
  `url` text NOT NULL,
  PRIMARY KEY  (`session`),
  KEY `logTime` (`logTime`),
  KEY `ID_MEMBER` (`ID_MEMBER`)
) ENGINE=MyISAM ;

...but on a separate clean test install of SMF, I see that one line actually reads as...

`logTime` timestamp(14) NOT NULL,

Given that I've read the Joomla-SMF bridge alters nothing in tables, this makes me think I've got a problem with the 'logTime' table and need to alter that field in the SQL table...what do you think?

Phil

[phil_roy]

Just tried this locally and all jos_users and registerDate and lastvisitDate remain set at 0000-00-00 00:00:00 when I run the script unfortunately.

Phil

[Kindred]

hmmmmmmm... back to the drawing board....  I'll take another look

[phil_roy]

Not sure if this helps, but I chnaged to lines in the function (the old ones commented out) to...

//$mosregtime = strtotime($smf_row[4]);
$mosregtime = date("Y-m-d H:i:s",$smf_row[4]->dateRegistered);
//$moslogtime = strtotime($smf_row[5]);
$moslogtime = date("Y-m-d H:i:s",$smf_row[5]->lastLogin);

and ended up with all the dates going to 1970-01-01 12:00:00....which I found interesting but may in fact not be significant??

Phil