Need to reformat the information in a column of a mysql table.

[danieliser]

Back Story:

 

Im using vbulletin and using a custom user field for each user to list there game id which should be formatted '123-123-123'. I was unable to incorporate a js function to format it automatically as they type. I got the function working but could not get it working inside the code on vbulletin admincp. I am looking into hardcoding the field into the register.php but havent got that worked out.. Problem is is users are constantly inputting the data as 123123123 or 123 123 123 or even 123~123~123. I was able to put together a regex to check the format. but i cant get that working in vbulletin either.. so until i do. I am gonna run a cron job every 24 hours using php and getting all results from the table within the last 24 hours and checking them using regex. Ive got that part working.

 

Current Issue:

For my else statement i need to figure out how to break down 123123123 and 123 123 123 and reformat them as 123-123-123 then update the database. here is what i have so far..

 

<?php

require('includes/db.php');

$sql = "SELECT userid, field5 FROM userfield";
$result = mysql_query($sql);
while ($myrow = mysql_fetch_array($result))
{
	$userid = $myrow['userid'];
	$field5 = $myrow['field5'];
	$i = 0;
	$e = 0;
	if (eregi('^[0-9]{3}-[0-9]{3}-[0-9]{3}$', $field5))
		{
			$e = ($e + 1);
		}
	else
		{
			reformat blah blah
			$newfield5 = 
			$update = "UPDATE userfield SET field5='$newfield5' WHERE userid='$userid'"
			$i = ($i + 1);
		};
}
	$total = ($e + $i);
	echo $total, ' NEW RECORDS TODAY!<br />';
	echo $e, 'USERS NOT CHANGED<br />';
	echo 'SUCCESSFULLY UPDATED ',$i,' USERS RECORDS';

?>

[Prismatic]

<?php
  $bad_tags = array("123123123", "123~123123", "123 123 123", "abc123-123~123hgt");
  
  foreach($bad_tags as $tag)
  {
      echo implode("-", str_split(preg_replace('/[^\d]/', "", $tag), 3)) ."\n";
  }
?>

 

Output:

123-123-123
123-123-123
123-123-123
123-123-123

[danieliser]

awesome.. i figured it was a function like explode.. ive used that before but wasnt sure how to work the code.. So i should just put that on for the else and make an array of the bad codes?

 

any chance you know how to modify and add java verification to vbulletin userfields huh?

[Prismatic]

awesome.. i figured it was a function like explode.. ive used that before but wasnt sure how to work the code.. So i should just put that on for the else and make an array of the bad codes?

 

any chance you know how to modify and add java verification to vbulletin userfields huh?

 

<?php

require('includes/db.php');

$sql = "SELECT userid, field5 FROM userfield";
$result = mysql_query($sql);

while ($myrow = mysql_fetch_array($result))
{
    $userid = $myrow['userid'];
    $field5 = $myrow['field5'];
    $i = 0;
    $e = 0;
    if(eregi('^[0-9]{3}-[0-9]{3}-[0-9]{3}$', $field5))
    {
        $e = ($e + 1);
    }
    else
    {
        $new_tag = implode("-", str_split(preg_replace('/[^\d]/', "", $field5), 3));
        $update = "UPDATE userfield SET field5='{$new_tag}' WHERE userid='{$userid}'";
        $i = ($i + 1);
    }
}

$total = ($e + $i);
echo $total, ' NEW RECORDS TODAY!<br />';
echo $e, 'USERS NOT CHANGED<br />';
echo 'SUCCESSFULLY UPDATED ',$i,' USERS RECORDS';

?>