mysql: update field's set type values.

sijis

i have 2 tables, 1 table (Table1) has a field 'category' of type 'set'
which corresponds to table 2's(Table2) cat_id field.
Is there a way to automatically expand the 'set' values type in Table1 when i add a new category in Table2?

schema dump:

Table1

......
category set('1','2','3','4','5','6','7','8','9','10','11','12','13') NOT NULL default '',

Table2

.....
cat_id bigint(20) NOT NULL auto_increment,
PRIMARY KEY (cat_id)

summary
so when i add a new category to table2, it changes table1.category to:
category set('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15', and so on....) NOT NULL default '',

thanks.

sijis

this is what i have so far, but its not working correctly.

mysql_query("INSERT INTO Table2 (cat_name) VALUES('".$_POST['cat_name']."')",$dbServerConn);
$new_cat_id = mysql_insert_id();

mysql_query("ALTER TABLE Table1 CHANGE category category SET(category,',$new_cat_id') NOT NULL",$dbServerConn");

the problem is this part here in the second query:
SET(category,',$new_cat_id')

i want to get all the previous values and 'concat' the new ID to it... still not success.

sijis

here's my working hack

adding to a set field type:

$result = mysql_query("SELECT * FROM table",$dbServerConn);
while($row = mysql_fetch_array($result)){
	$tmp .= "'$row[id]',";
}
mysql_query("INSERT INTO table (name) VALUES('".$_POST['newName']."')",$dbServerConn);
$new_id = mysql_insert_id();
mysql_query("ALTER TABLE `Table` CHANGE `set_field` `set_field` SET($tmp'$new_id') NOT NULL",$dbServerConn);

to delete from set field type:
note it also removes any entries in the field that refer to the
just recently deleted value.

mysql_query("DELETE FROM table WHERE id = ".$_POST['cat_id']."",$dbServerConn);
$result = mysql_query("SELECT * FROM tblGenreList",$dbServerConn);
while($row = mysql_fetch_array($result)){
	$tmp .= "'$row[id]',";
}
$tmp = rtrim($tmp,",");
mysql_query("ALTER TABLE `Table` CHANGE `set_field` `set_field` SET($tmp) NOT NULL",$dbServerConn);

I know this is 'ugly' code, but it works. If anyone has a better way, i'd LOVE to know. This should be helpful to someone. 🙂