UBB.Developers Forums Community Development Programming MySQL and Server Configuration commands

Ok, I have a table for users. I want to change the field "user_level" in the table to have the value 0. Because of the port many users are at many different levels which determine admin/mod status, so I want to revert everyone to 0 which is standard user.

I know its probably simple, using UPDATE <table> blah blah.. but, I'm MySQL challenged

UPDATE Table SET user_level='0'

wow, so uh, yeah that was simple. All better Thanks Dave!

Ok, lets try ONE more thing. Everything is great except one field in the users table. With the old system the user_regdate was stored as a varchar(11) value, so the date was like "Nov. 10, 2001" or whatever. Well the new software stores the regdate as an int(8) and each date is stored as a string of number that make no sense. I can't see how they get a date from the number.

So, there are two things I'm considering. One, how could I convert all the varchar values into the new int values?

Second, how can I just insert the user_regdate field from the old table into the new one. Because after the port it reverted all the dates to 0, so all 2700 members have all registered the same day So if anything I'd like to just fill that field with the existing data, and work on getting it to display from there.

EDIT - I stumbled across this, for the import script doing the reverse of what I want:

code:
FROM_UNIXTIME(user_regdate,'%b %d, %Y') as user_regdate

---

So, since it used to be a string of numbers (ie. 10291023) I'm assuming unixtime can format dates into other formats, like what was shown, to make it Month. Day, Year as it is now. I think the string of numbers is the "seconds since 1/1/70" or something? I dunno

But if anyone is familiar with unixtime and how to convert back to other formats I'd love to hear it

The MySQL date and time functions, including FROM_UNIXTIME, are documented here.

Thanks Dave, I have been at that site all morning trying various commands with no luck. I was also talking to a friend of mine who does a lot of mysql/php work for his company, and he had some good ideas as well but nothing worked.

It would be fairly simple if the "'Month Day, year" data was stored in DATETIME format and not VARCHAR When we try to run the conversion it will format it, but gives all 0's. Stupid coders not doing things right

Anyway, since it seems impossible via a direct MySQL query, the only option is to create a script. So, my reasoning for a script is this.

Pull the current date format info out of the table as text. So it would just pull say "Jun 10, 2002". Pull these according to the 'username' field.

Then convert the text via the script to unix time.

Insert the new value into the appropriate username's datereg field.

The only problem would be finding the conversion code. I have seen websites that allow you to enter dates and have it convert for you that way, so I'm sure its out there, just a matter of finding it.

Let me know if that makes sense or I'm completely lost and this won't work at all (which is a possibility, lol)

"Nov. 10, 2001"
"Jun 10, 2002"

What's the exact format for the existing dates? Are all the months 3 characters? Is the "." present? Is the year always 4 digits?

Sorry, I was wrong, no . (period) in there at all. And yes, all 4-digit years. Attached is a screenshot of the format just to clarify.

I've attached a PHP function for doing the conversion.

I should charge for this.

Wow, that's awesome Dave! Seems like both Dave's on this site are amazing I will play with it and see if I can make an automated script to update all the entries. If it works, I owe you one big time!

Seems as if I'm close I have added the code to (in theory) make the changes to the correct tables/fields. Here is the code with my changes:

code:

error_reporting(E_ALL);

//Database Info
$dbhost = 'localhost';
$dbuser = 'xxxxx';
$dbpass = 'xxxxx';
$dbname = 'dbname';

// Open database connection
//
$link = mysql_connect($dbhost, $dbuser, $dbpass)
        or die("Could not connect");
        mysql_select_db($dbname) 
        or die("Could not select database");

$query = "select username,user_regdate from usersnuke";  
$result = mysql_query($query);  

while ($row = mysql_fetch_assoc($result))  
{  
     $curruser = $row["username"]; 
     $timestamp = date_str_to_unixtimestamp($row["user_regdate"], TRUE); 
     $query2="update phpbb_users set user_regdate = '$timestamp' WHERE username = '$curruser'"; 
     $update_result = mysql_query($query2); 
     echo "Ran update query on user: $curruser with result: $update_result"; 
  
}

// test-data
//$date_strings = array(
//	'Nov. 10, 2001', 'Jun 10, 2002', 'Foo 10, 1999', 'x', '', 'Jan 32, 1999', 'Jan 10, badyear', 'jan 31, 1995', 'may 07, 2005'
//);

// perform test and display results
//foreach($date_strings as $date_string) {

	// convert to timestamp
	//$timestamp = date_str_to_unixtimestamp($date_string, TRUE);

	// convert back to string (to make it easier to check result)
	//$date_string2 = date('F j, Y', $timestamp);

	//echo "'$date_string' => '$timestamp' => '$date_string2'\n";
//}

// Given date string 'MMM DD, YYYY', return unix timestamp.
//
// If date string is invalid:
//    If $debug is omitted or FALSE, return 0.
//    If $debug is present and TRUE, return 1 if syntax is bad, 2 if month name is bad, 3 if date is bad.

function date_str_to_unixtimestamp($date_string, $debug = FALSE) {

	if (!preg_match('/^([a-z]+)\W+(\d+)\W+(\d+)$/i', $date_string, $matches)) {
		return $debug ? 1 : 0;
	}

	$month = month_name_to_num($matches[1]);
	if ($month === FALSE) {
		return $debug ? 2 : 0;
	}

	$day  = $matches[2];
	$year = $matches[3];

	if (!checkdate($month, $day, $year)) {
		return $debug ? 3 : 0;
	}

	return mktime(12, 0, 0, $month, $day, $year);
}

// Given month name, return number 1..12.
// Return FALSE if name is not valid.

function month_name_to_num($month_name) {

	static $months = array(
		'jan' =>  1, 'feb' =>  2, 'mar' =>  3, 'apr' =>  4, 'may' =>  5, 'jun' =>  6,
		'jul' =>  7, 'aug' =>  8, 'sep' =>  9, 'oct' => 10, 'nov' => 11, 'dec' => 12
	);

	$num = @$months[strtolower($month_name)];
	return isset($num) ? $num : FALSE;
}

//Close Database connection
//
mysql_close($link);

---

When I run that I get this: Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in blah/blah/blah/ on line xx

I never said I was a php guru either Thanks again for all the help!

My mistake, I spelled one of the tables wrong. lol

BUT IT WORKED!!!!!!!!!!!!!!!!!!!!!!
Wow, I'm still in shock, I had deemed this impossible late last night. Thanks Dave for your awesome help. If you need anything, maybe I can hook you up

If you want to check whether any dates failed the conversion, this should work:
SELECT username,user_regdate FROM phpbb_users WHERE user_regdate < 4

Very nice, only two users failed the conversion, and they were users who had invalid usernames anyway, so POOF be gone

Now that I've learned so much about the phpbb system and its issues, hopefully I can make a conversion for threads, I feel I need to give something back

Don't hold me to that though, lol.