2

I have a field (demo_field)(varchar) in mysql table. I want to increment this field value with a pre defined prefix. For example my field first value is demo001. Now when ever new values inserted I want to increment the numeric numbers like demo002, demo003. How can I do this with PHP.

Joel James
  • 1,315
  • 1
  • 20
  • 37

3 Answers3

3

try this - 

//fetch data from table
$sql = $mysqli->query('select count(demo_field) as total,demo_field from tablename limit 1');
$res = $sql->fetch_assoc();

//generate string from existing data
$str = substr($res['demo_field'], 0, 4);
$dig = str_replace($str, '', $res['demo_field']);
$dig = $dig+$res['total'];

//add padding if needed
$dig = str_pad($dig, 3, '0', STR_PAD_LEFT);

//concatenate string & digits
$newStr = $str.$dig;

var_dump($newStr);

Another way without count

$sql = $mysqli->query('select max(demo_field) as demo_field from demo');
$res = $sql->fetch_assoc();

$str = substr($res['demo_field'], 0, 4);
$dig = str_replace($str, '', $res['demo_field']);
$dig += 1;
$dig = str_pad($dig, 3, '0', STR_PAD_LEFT);
$newStr = $str.$dig;

var_dump($newStr);

hope this might solve the problem with count.

another solution with max count for alphanumeric string and without padding -

$sql = $mysqli->query('select max(cast(substring(demo_field, 5) as unsigned)) as digit,     demo_field from demo');
$res = $sql->fetch_assoc();

$str = substr($res['demo_field'], 0, 4);
$dig = $res['digit'] + 1;
$newStr = $str.$dig;

var_dump($newStr);
Sougata Bose
  • 31,517
  • 8
  • 49
  • 87
  • Yes. Currently I use this method. I just wanted to know whether can directly (some how) increment the value when inserting. Thanks @sgt – Joel James Nov 04 '14 at 06:32
  • 1
    how would you increment a string without string manipulation in php? – Sougata Bose Nov 04 '14 at 06:35
  • 1
    Also there is a small disadvantage on this. taking count for generating number is not a good idea. Because if we delete few values, then generating number according to count may create duplicate values – Joel James Nov 04 '14 at 06:37
  • yes that would be a problem..then you should prefer doing this with sql rather than php. – Sougata Bose Nov 04 '14 at 06:38
  • Ok. So no other way? Right. Thanks for your efforts @sgt – Joel James Nov 04 '14 at 06:41
  • updated my answer.check it out.but it will work only if the increment is in order and not updated manually as it will find max of string field. – Sougata Bose Nov 04 '14 at 06:47
  • Thanks @sgt. Just one doubt. Will max() work for values with alpha prefix. MAX() is for numeric values right? – Joel James Nov 04 '14 at 07:01
  • will work but for but it checks like letter by letter and compares them.for this `demo005` will be greater than `demo0010`. and one thing if you dont need padding then dont use it, it will create problem for larger numbers. – Sougata Bose Nov 04 '14 at 07:06
  • added another solution. – Sougata Bose Nov 04 '14 at 07:30
1
//use PHP not mysql
$pre = 'demo';
$num = 0;
define('MAX', 100);
for($num = 0; $num < MAX; $num++)
{
    $pre_str = $pre . sprintf("%03d", $num);
    //insert here
}
lkxiaolou
  • 11
  • 1
0

you have to use an INT field and translate it to whatever format you want at "select" time.

In MySQL we cannot use AutoIncrement for Varchar.

karthik kumar
  • 51
  • 1
  • 4