phpbb3 and birthdays

Get help with installation and running phpBB 3.3.x here. Please do not post bug reports, feature requests, or extension related questions here.
Post Reply
Quiff Boy
Registered User
Posts: 37
Joined: Thu Mar 01, 2007 5:41 pm
Location: The Land of Lard
Contact:

phpbb3 and birthdays

Post by Quiff Boy »

I'm finally taking the plunge and upgrading my old and creaky phpBB v2 forum to phpBB 3.3 :lol: The main reason I held off for so long was because it was quite heavily modded. However, I'm comfortable I can now upgrade to v3.3 without losing too many useful features.

One question I have was around birthdays.

My old forum had a MOD that stored user birthdays as a number - I did a bit of digging at it looks like this number is actually "the number of days since Jan 1st 1970". For eg: a user who's birthday was 05 Dec 1979 would have that stored in the DB as 3625 (which I gather is apparently known as "unix epoch time"?)

I see phpBB3 now uses a more sensible varchar of " 5-12-1979" (including spaces to pad out each dd and mm part to be 2 digits)

I suspect I will need some kind of php script to run that copies all the old format birthday data from the old table into the new table and format, but that's a little beyond my abilities.

Any ideas?
User avatar
david63
Registered User
Posts: 17976
Joined: Thu Dec 19, 2002 8:08 am
Location: Lancashire, UK
Contact:

Re: phpbb3 and birthdays

Post by david63 »

It is a long time since I converted a board from phpBB 2 to phpBB 3 but I seem to recall that the conversion did in fact convert the "old" birthday into the "new" birthday
David
Remember: You only know what you know and - you don't know what you don't know!
My CDB Contributions | How to install an extension
I will not be accepting translations for any of my extensions in Github - please post any translations in the appropriate topic.
No support requests via PM or email as they will be ignored
Quiff Boy
Registered User
Posts: 37
Joined: Thu Mar 01, 2007 5:41 pm
Location: The Land of Lard
Contact:

Re: phpbb3 and birthdays

Post by Quiff Boy »

I tried the conversion on a local copy of the old site and it didn't, sadly.

The old birthday system was a MOD I added many years ago, so I'm not really surprised the convertor ignored it :D
User avatar
Mike-on-Tour
Registered User
Posts: 90
Joined: Wed Jan 15, 2020 3:51 pm
Location: Germany
Name: Michael

Re: phpbb3 and birthdays

Post by Mike-on-Tour »

My first idea would be to check how it is stored in the database. Contemporary phpbb stores it as an unsigned integer with the number of seconds since Jan 1, 1970, 00:00:00 hours. If this isn't the case just multiply it by 86400 (seconds per day) and you should be fine.
Your example Dec 5, 1979 would be something like 313200000.
Watz fo lunch?
rxu
Extensions Development Team
Posts: 3298
Joined: Wed Oct 25, 2006 12:46 pm
Location: Siberia, Russian Federation
Name: Ruslan
Contact:

Re: phpbb3 and birthdays

Post by rxu »

Quiff Boy wrote:
Mon Jun 29, 2020 10:55 am
The old birthday system was a MOD I added many years ago, so I'm not really surprised the convertor ignored it
Well, phpBB2 convertor should indeed convert 2 birthdays MODs: from TerraFrost and from Niels. From what you're describing it seems that you have the latter MOD installed.
https://github.com/phpbb/phpbb/blob/3.3 ... #L168-L179
https://github.com/phpbb/phpbb/blob/3.3 ... #L433-L469
Quiff Boy
Registered User
Posts: 37
Joined: Thu Mar 01, 2007 5:41 pm
Location: The Land of Lard
Contact:

Re: phpbb3 and birthdays

Post by Quiff Boy »

Strange. It didn't convert them though - just set everyone to

Code: Select all

0- 0-   0
However, I've done a bit of wrangling and this seems to convert from the old format to the new:

Code: Select all

<?php
$old_birthday = 3625;
$t = new DateTime('@' . $old_birthday * 86400);
echo 'new birthday '.$t->format('d-m-Y');
?>
so i could probably write a script that loops through the users in the old table, does the reformatting and updates the user in the new table
rxu
Extensions Development Team
Posts: 3298
Joined: Wed Oct 25, 2006 12:46 pm
Location: Siberia, Russian Federation
Name: Ruslan
Contact:

Re: phpbb3 and birthdays

Post by rxu »

Code: Select all

$old_birthday = 3625;
$t = new DateTime('@' . $old_birthday * 86400);
list($bday_day, $bday_month, $bday_year) = explode('-',$t->format('j-n-Y'));
$new_birthday = sprintf('%2d-%2d-%4d', $bday_day, $bday_month, $bday_year);
rxu
Extensions Development Team
Posts: 3298
Joined: Wed Oct 25, 2006 12:46 pm
Location: Siberia, Russian Federation
Name: Ruslan
Contact:

Re: phpbb3 and birthdays

Post by rxu »

Quiff Boy wrote:
Mon Jun 29, 2020 11:41 am
Strange. It didn't convert them though
What is the name of the database column? It's supposed to be user_birthday in users database table.
Also, is there birthday_required config name in config table?
Quiff Boy
Registered User
Posts: 37
Joined: Thu Mar 01, 2007 5:41 pm
Location: The Land of Lard
Contact:

Re: phpbb3 and birthdays

Post by Quiff Boy »

The table is called user_birthday but I'm not honestly sure about the config question... If the original MOD added that then yes, it would be there, but it would have 15+ years ago I did it :lol:

However, i've managed to hand-crank a php script that does the conversion using the code rxu suggests and it works perfectly, thank you :)

It converts all ~1500 users who have entered a birthday

I copied the old users table into the new db and renamed it "phpbb_users_old" then ran this script:

Code: Select all

<?php
$db = mysqli_connect('host','username','password','tablename')
or die('Error connecting to MySQL server.');

$query = "SELECT * FROM phpbb_users_old WHERE user_birthday != 999999 ORDER BY user_birthday";
mysqli_query($db, $query) or die('Error querying database.');
$result = mysqli_query($db, $query);

$count = 0;

$row = mysqli_fetch_array($result);
while ($row = mysqli_fetch_array($result)) {

    $username = $row['username'];
    $user_email = $row['user_email'];
    $old_birthday = $row['user_birthday'];

    $t = new DateTime('@' . $old_birthday * 86400);
    list($bday_day, $bday_month, $bday_year) = explode('-',$t->format('j-n-Y'));
    $new_birthday = sprintf('%2d-%2d-%4d', $bday_day, $bday_month, $bday_year);

    $newsql = "UPDATE phpbb_users SET user_birthday = '" . $new_birthday . "' WHERE username = '" . mysqli_real_escape_string($username) . "' AND user_email = '" . $user_email . "'";

    if (mysqli_query($db, $newsql)) {
        //show the results if successful
        echo "<p>Changed " . $username . " DOB from " . $row['user_birthday'] . " to " . $new_birthday . "</p>";
        $count++;
    } else {
        //show error if failed
        echo "<p><b>Error: " . $newsql . ": " . mysqli_error($conn) . "</b></p>";
    }

    $count++;
}

echo "<h1>Updated " . $count . " rows!</h1>";

mysqli_close($db);
?>
Had to use username and email as an identifier for the UPDATE because the old table's user IDs don't correspond with the new ones.

But hey! It works! Thanks for all your help 8-)
rxu
Extensions Development Team
Posts: 3298
Joined: Wed Oct 25, 2006 12:46 pm
Location: Siberia, Russian Federation
Name: Ruslan
Contact:

Re: phpbb3 and birthdays

Post by rxu »

user_birthday apparently exists. What would be the query result

Code: Select all

SELECT config_name, config_value FROM phpbb_config WHERE config_name IN ('birthday_required', 'bday_require')
Quiff Boy
Registered User
Posts: 37
Joined: Thu Mar 01, 2007 5:41 pm
Location: The Land of Lard
Contact:

Re: phpbb3 and birthdays

Post by Quiff Boy »

Code: Select all

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0003 seconds.)
rxu
Extensions Development Team
Posts: 3298
Joined: Wed Oct 25, 2006 12:46 pm
Location: Siberia, Russian Federation
Name: Ruslan
Contact:

Re: phpbb3 and birthdays

Post by rxu »

I guess if you execute a query

Code: Select all

INSERT INTO phpbb_config (config_name, config_value) VALUES ('birthday_required', '0');
then phpBB convertor should do its job normal way.
Post Reply

Return to “[3.3.x] Support Forum”