Better type of query to use

[spinner0205]

I have the following MySQL query;

mysql_query("UPDATE thatsact_tag_clan.tag_user
					SET membergroupids='11'
					WHERE username='$vbid';");

 

And what I would like to do is change this to a better query that adds the number 11 to the existing list. At the moment it will take any numbers in this table field and overwrite them with 11. What I want it to do is add the number to the list. It is formatted as 1,2,3 and so on, so just have it add 11 to this list. What is a query I can use for this?

 

Thanks in advance.

[sunfighter]

What 'list' are talking bout Willis?

 

Mysql has databases that have tables that have rows and columns. Are talking about a column? If so your query is almost right - the problem is the table name

thatsact_tag_clan.tag_user  You can't use a dot in a table name.

 

Well maybe you can in ver 5 BUT just don't do it. 

 

Your query is UPDATING. You want to ADD. what do you want to add? By the way that's called INSERT, but we need to know what your going to add. Please tell us. And maybe name the columns in the table your adding to.

[spinner0205]

I guess I call it a list because its a list of numbers in the field that goes by 1,2,3,4,5 etc. And what I want the query to do is add the number 11 to this set of numbers with the comma and everything. Also the query works fine with the period in version 5, already tested it.

 

EDIT: Probably confused you because I pasted the entire code including PHP. Edited for just the MySQL part below.

 

UPDATE thatsact_tag_clan.tag_user

SET membergroupids='11'

WHERE username='$vbid'

 

thatsact_tag_clan is the database and tag_user is the table. The field is membergroupids.

[spinner0205]

The INSERT syntax would work but how can I retain the WHERE clause so it executes the query where it matches a certain field?

[crtreedude]

Unless I am confused this morning, an Insert never has a where because what you are inserting doesn't exist in the table yet, so why would you care about the where clause?

[spinner0205]

Exactly so that is why I am asking for a better type of query to use. 

[fugix]

yeah, the query you are using now is efficient for you needs

[PFMaBiSmAd]

For your existing scheme of storing a comma separated list in a field, you would use the mysql CONCAT() function in your query to concatenate the new value onto the end of the existing list.

 

SET membergroupids=CONCAT(membergroupids, ',11')

 

However, storing a comma separated list in a field is not efficient for finding information in and you should store this information using a table with a separate row for each value for each id that it belongs to.

 

user_id group_id
1          1
1          2
1          3
1          11

22       4
22      11

91      5
91      6

 

[spinner0205]

The table is part of a huge content system so cant really change it but I appreciate the advice. However the CONCAT function comes back with membergroupids as an unknown system variable. Am I missing something here?

[PFMaBiSmAd]

Let me guess, you replaced your whole query statement - UPDATE thatsact_tag_clan.tag_user SET membergroupids='11' WHERE username='$vbid'; with the short fragment I posted showing how you would use CONCAT to set the membergroupids value?

[spinner0205]

Yea I was at work doing multiple things so wasn't paying attention but now I added the whole thing and works great. However I have a new problem. How can I have it insert just the 11 rather than ,11 if there is no data in the membergroupids field yet?

 

If you cant find anything I could do I can just put a 0 in the field for everyone as the default.

[PFMaBiSmAd]

Assuming that the default for the column could be either a NULL or an empty string -

UPDATE thatsact_tag_clan.tag_user SET membergroupids=CONCAT(IFNULL(membergroupids,''),IF(LENGTH(IFNULL(membergroupids,''))>0,',',''),'11') WHERE username='$vbid'

[PFMaBiSmAd]

If you cant find anything ...

 

I write programs (queries) to do what they need to do, not find them... 

[spinner0205]

Well you are certainly good at, thanks a lot works wonders. Also not important but if possible some code for when the 11 or ,11 is already there then don't insert it? Sorry but MySQL is my down fall and that is why I come to this great site.