Page 1 of 1

auto update

Posted: Tue Jun 07, 2005 9:51 am
by maori
Hi guys

is there a way i can check 2 tables in my db ie users and awards

in the users table it should check the user_id, regdate

then in the awards table user_id, award_name

what i would like is for it to update the awards for the first 3,6,9,12 mths
each of thes mts has a diffrent image
and if no award assigned for each if applicable it will insert into the awards table the user_id, award_name, reason.

but if award_name allready there for the user_id it wont do anything ie wont add 2 off the same awards

i would like this done just running a script if possible

TIA

Posted: Tue Jun 07, 2005 9:56 am
by pickle
What have you got so far?

Posted: Tue Jun 07, 2005 11:20 am
by maori
hiya


to be honest nothing i was trying to think of a way to do it i'am pretty new at this ive been searching though the mysql site and other forums but havent really made any sense to me

Posted: Tue Jun 07, 2005 2:32 pm
by pickle
What have you got for table structure? Examining that can help you decide what needs to be in a query and what needs to be in PHP.

Posted: Tue Jun 07, 2005 3:05 pm
by maori
hiya pickle

in the medals table i have

Code: Select all

medal_id` int(11) NOT NULL auto_increment,
  `user_id` int(4) NOT NULL default '0',
  `medal_name` varchar(255) NOT NULL default '',
  `medal_date` date NOT NULL default '0000-00-00',
  `medal_imag` varchar(255) NOT NULL default '',
  `reason` varchar(200) NOT NULL default '',
and in the users table

Code: Select all

`user_id` int(11) NOT NULL auto_increment,
         `user_regdate` varchar(20) NOT NULL default '',

feyd | fixed bbcoding.. :?

Posted: Tue Jun 07, 2005 4:30 pm
by pickle
Could you explain your problem again? What, in the grand scheme, are you trying to do here? Your earlier explanation seemed to be mixed with what you thought would be a solution (which is great), and I didn't get a clear picture. Thanks.

Posted: Tue Jun 07, 2005 4:52 pm
by maori
heya Pickle

Sure lemme try and explain it better here

say once a month i would like to run a script that will check the users table for users that have been with me for 3,6,9 and 12 mths and if a match is found it will assign them a medal so in the medals table it will add into these fields

user_id, `medal_name` , `medal_date` , `medal_imag` , `reason`

where medal_name medal_img and reason will i hopefully come from a preset array or something

But if a match is found for the medal_id in the medals table it wont add a new one ie no two instances for these medals i have others that are manually assigned which they can have multiples for

hope this helps

TIA

Posted: Tue Jun 07, 2005 5:10 pm
by aybra

Code: Select all

SELECT * FROM table1, table2
WHERE `user_id.table1` LIKE `user_id.table2`
AND `$medal_name` LIKE `medal_name.table2`;
Should tell you if they already have the medal in there profile. Not sure on the rest, except to pull that search and exclude those members from your insert. Possably adding a field on the medal table to decide wich level of medal they have as well,

Code: Select all

if ($medal = 1){

give 3 month medal to user
}
UPDATE $medal+1
something to that very ruff affect.

Posted: Tue Jun 07, 2005 6:13 pm
by pickle
Ok, so you want to check if they've been with you for a certain time and if they have, you give them a medal - EXCEPT if they already have that particular medal.

Well, first I'd do a query to find all users that have been with me for those times. Probably your best bet would be to do 4 queries - one to find who's been with you over 11 months and don't already have a particular medal, one for those who're over 8 months and don't have a particular medal, and so on. For each of those people you find, do another query and add the appropriate medal.

Does that make sense?

Posted: Wed Jun 08, 2005 4:22 am
by timvw
Another option is to place a unique constraint on the medal, person combination.
This way, you can insert them, and if they exist already, your dbms will do nothing.

Query would look like (untested)

Code: Select all

INSERT INTO medals (medal, player_id)
SELECT '12month_medal', player_id
FROM players
WHERE active > DATE_ADD(NOW(), INTERVAL +12 month)

Posted: Wed Jun 08, 2005 11:40 am
by maori
Guys Thanks

alot youve certainly pointed me in a great direction to be honest alot has gone over my head at the moment :oops:

ill be looking over the code over the next few days and try to put it all together..

thanks for all the code examples that helps me a lot to understand whats going on and how to talk to seperate table's

Really appreciate the help youve all given me

hehe expect some more dumb questions soon :oops:

Regards