in mysql, have 2 tables:
category
---------------------------------------------- | category_name | category_code | ---------------------------------------------- | suit | su | | western | | ---------------------------------------------- product
--------------------------------------------- | name | category | code | --------------------------------------------- | xyz1 | suit | | | abc1 | suit | | | abc2 | western | | --------------------------------------------- i want update code in product, after updation will:
product
--------------------------------------------- | name | category | code | --------------------------------------------- | xyz1 | suit | su/0001 | | abc1 | suit | su/0002 | | abc2 | western | we/0001 | --------------------------------------------- thanks in advance
honestly, looks need restructure database. each table should have auto-incrementing "id" field. allow "category" field reference id instead of duplicate of name (in case ever decide change spelling or name of category). down naming - if field in category table, doesn't seem necessary have "category_" before "code" field name...etc
beyond that, you're talking php script (or similar) - allow repeat through each item , keep track of how many suit codes you've added can increment "code" field (i assume that's 0001 0002 doing.
off top of head, it'd (i'm sure more streamlined/better - gives idea of way it:
$numsu = 0; $numwe = 0; $products = mysql_fetch_array(mysql_query("select * product")); foreach($products $p) { $codequery = mysql_query("select category_code category category_name='".$p['category']."'"); $code = mysql_result($codequery,0,'category_code'); if($code == "su") { $numsu++; $numitems = $numsu; } else if($code == "we") { $numwe++; $numitems = $numwe; } $update = mysql_query("update product set code='".$code."/".$numitems."'"); } 
Comments
Post a Comment