Jump to content


Photo

sql query


  • Please log in to reply
5 replies to this topic

#1 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 24 October 2006 - 02:49 PM

Hi all

I have a question about using a query in phpMyAdmin.

Currently I have a table called `myuserstable`, inside this table there is a field called `userscode`, if I want to update the field `userscode`, I use

UPDATE `myuserstable` SET `userscode`='12345';

Now my problem. basically I have loads of rows of data in my table `myuserstable`, for each row, there is a number inside the field `userscode`, a sample looks like

userscode        name
23                  D Halls
456                S Smalls
2312              H Wicks
6543              J Browns
23234            V Gail

the problem is that all the number sequences I have stored in `userscode` should be prefixed with as many 0's as possible until the total characters stored is 5, for example, the sample data above, should look like

userscode        name
00023                  D Halls
00456                S Smalls
02312              H Wicks
06543              J Browns
23234            V Gail

See all the 0's before the code, if a code is 5 characters long, then it doesnt need a 0.

Is there a query I can use, which will add 0's to the start of each code and keep adding 0's until there is a total of 5 numbers.

The problem is that I have 7,500 records, and it will take a huge amount of time to do all this by hand.

Any help of guidence would be great

Regards

Ed

#2 alpine

alpine
  • Members
  • PipPipPip
  • Advanced Member
  • 756 posts
  • LocationNorway

Posted 24 October 2006 - 03:30 PM

Study and try this, note that the provided SELECT and UPDATE not nessesarily fit your table construct.

AND remember to backup your table data FIRST  ;)


<?php

function number_length($number,$length)
{
$number = str_split($number);
$number_rev = array_reverse($number);
$i=0;
while($i < $length)
{
  if($number_rev[$i]==false)
  {
    $number_rev[$i] = 0;
  }
  $i++;
}
$number = array_reverse($number_rev);
return implode("",$number);
}


$select = mysql_query("select id,userscore from users");
while($row=mysql_fetch_array($select))
{
$id = $row["id"];
$userscore = $row["userscore"]; // example: value is 55

// start manipulating userscore using the function number_length
$new_userscore = number_length($userscore,5);

// done manipulating, update with new userscore 00055
$update = mysql_query("update users set userscore = '$new_userscore' where id = 'id'");
}

?>



#3 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 25 October 2006 - 09:52 AM

Thanks Alpine, the only problem is I get a

Fatal error: Call to undefined function: str_split() in /home/webs/public_html/controlpanel/dbchange.php on line 10

I dont think I have php5 installed on the server and str_split() isnt supported

Any help would be great

Thanks

Ed

#4 alpine

alpine
  • Members
  • PipPipPip
  • Advanced Member
  • 756 posts
  • LocationNorway

Posted 29 October 2006 - 06:30 AM

Sorry about that, i've added a string_split function to this - working in PHP4 --> now

<?php

function string_split($string)
{
$piece = array();
$length = strlen($string);
for($i=0;$i<$length;$i++) $piece[] = $string{$i};
return $piece;
}

function number_length($number,$length)
{
$number = string_split($number);
$number_rev = array_reverse($number);
$i=0;
while($i < $length)
{
  if($number_rev[$i]==false)
  {
    $number_rev[$i] = 0;
  }
  $i++;
}
$number = array_reverse($number_rev);
return implode("",$number);
}

$select = mysql_query("select id,userscore from users");
while($row=mysql_fetch_array($select))
{
$id = $row["id"];
$userscore = $row["userscore"]; // example: value is 55

// start manipulating userscore using the function number_length
$new_userscore = number_length($userscore,5);

// done manipulating, update with new userscore 00055
$update = mysql_query("update users set userscore = '$new_userscore' where id = 'id'");
}

?>



#5 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 29 October 2006 - 11:04 AM

Or you could define the usercode column in the mysql table as

usercode INT(5) ZEROFILL
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#6 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 29 October 2006 - 11:07 AM

Or format on output with

printf ('%05d', $usercode);
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users