[RESOLVED] Change field variable between specified dates

thighmister

I have a table:

user | somefield
todd | A

I would like to have a form that allows the user to specify a date range in which somefield will be changed to B, after which somefield will change back to A.

Looking for direction on the simplest way to code that.

Thank you for reading.

bradgrafelman

Can you give us a little more information about why you're doing this? It's quite possible that a simpler solution wouldn't involve changing the data in the DB at all.

thighmister

Thank you for your fast reply!

Here is a more thorough description of my goal.

It is a game.

user_id|user|field1|group|
___||__||
1|todd|0|alpha|
2|john|1|alpha|
3|paul|0|alpha|
4|stan|0|beta|
__5|mike|1__|beta_|

John submits a form that changes his field1 to 0 and also changes the field1 of the next-highest id user in the same group, alpha, to 1 (in this case Paul). When there is no higher id in the group, it goes to the lowest id (so when Paul submits, Todd's field1 changes to 1).

The script also sends an email to the user whose field1 has been changed to 1.

I would like a user, say Paul, to be able to opt out of the game between dates he would specify through a form. My thought is to have that form change Paul's group name, alpha, to something else, like alpha1, between the specified dates. That would make it so that when John submits the form, Paul's field1 is unchanged and no email would be sent to him, and it would skip up to Todd.

If I am going about this the wrong way, I'm all ears!

Linux server, if that is relevant.

Thanks again for reading.

bradgrafelman

Rather than modifying the group name somehow (what if someone else had a group named "alpha1" ?), you might instead create a separate table with 3 columns; user_id, date_begin, and date_end. In this table, you'd insert date ranges for users who are to be excluded from this field1 routine you just described.

Can you show us your UPDATE query that does this field1 changing?

thighmister

I am open to the idea of a separate table, but just so you know, the group name is a random hash, so adding 1 at the end would be safe, I assume. Or perhaps it could just be changed to a whole other random hash.

Here is the field update query. Let me know if you need to see more. Thanks so much!

list($highest) = mysql_fetch_row(mysql_query("select id from users where group = '$group' and id >'$id'")); 

if($highest) {

mysql_query("update users set field1='1' where group = '$group' and id > '$id' LIMIT 1");

mysql_query("update users set field1='0' where id='$id'");

if(empty($highest)) {

mysql_query("update users set field1='0' where id='$id'");

    mysql_query("update users set field1='1' where group = '$group' and id > 0 LIMIT 1");

bradgrafelman

There's no need for so many separate queries (esp. the SELECT's) - you can use subqueries instead. For example, here's how you could UPDATE the next person's field1 to '0' assuming you have the second table I proposed above:

$sql = "
UPDATE users
SET field1 = '0'
WHERE
	id NOT IN (
		SELECT user_id
		FROM newTable n
				ON u.id = n.user_id
		WHERE CURDATE() BETWEEN date_begin AND date_end
	)
	AND id = (
		SELECT id
		FROM users
		WHERE id > $id OR id > 0
		ORDER BY id DESC
		LIMIT 1
	)
;";

The 'WHERE' conditions should be straight forward; the first makes sure that we don't update the id of someone when today's date falls within the range of dates they are to be excluded, and the second simply selects the larger of either the next id or the very first id.

thighmister

CURDATE! Excellent, thank you so much.

And thank you for id > $id OR id > 0
ORDER BY id DESC
LIMIT 1

The logic of this language is slowly starting to sink in. But sometimes I feel like the syntax will never come naturally.

Would it be advisable to include the date_begin and date_end fields in the same table (users)? If so, what might the syntax be for including all the conditions? I need it to say:

Update users field1 to 1
WHERE group is '$group'
AND WHERE
id > $id OR id > 0
ORDER BY id DESC
LIMIT 1
AND WHERE
CURDATE() BETWEEN date_begin AND date_end

I just know I would never get all the parentheses right.

Also, does anything need to go between the parens after CURDATE()<----[those parens]?

Thanks again for reading.

bradgrafelman

thighmister wrote:
Would it be advisable to include the date_begin and date_end fields in the same table (users)?

Can a user not create multiple date ranges during which they wish to opt out?

If so, then the answer is no, because you have a 1-to-many relationship, hence the extra table.

thighmister wrote:
Also, does anything need to go between the parens after CURDATE()<----[those parens]?

The MySQL manual's Date and Time Functions section can answer that question and any others about the CURDATE() function (or any other date/time function, of course) that you might have.

thighmister

Thank you for the sql manual link. CURDATE looks like it will be coming in pretty handy.

What if I limit the user to say 3 date ranges, and add date2_begin, date2_end, date3_begin, date3_end to the users table, and then query something like
WHERE
CURDATE() BETWEEN date_begin AND date_end
OR BETWEEN date2_begin AND date2_end
OR BETWEEN date3_begin AND date3_end

Again it's the syntax that is going to kill me here. Where/how is best to learn it?

bradgrafelman

Is there any reason you're trying to go down this theoretical path?

Based on the type of relationship, basic normalization techniques/rules say that the relationship should be made in a separate table... unless you've got some compelling reason why it shouldn't be?

thighmister

Thank you for your continued attention. I'm harping on this only because I don't yet understand what it is about the type of relationship that makes a second table better. But I totally defer to your expertise, of course, and I really appreciate all your help. Thanks so much for all the time you've given.

bradgrafelman

thighmister wrote:
But I totally defer to your expertise, of course, and I really appreciate all your help.

Oi - don't take my word for it, convince yourself first; Google around for "db normalization" (something you should read up on regardless of which way you go here) or "normalize one to many relationship" or something of that nature.

thighmister

Ok now one to many relationship is making sense. I didn't realize that was a thing, I thought you had just come up with the phrase! Thanks again for the link. One to many changes everything. Extremely cool.

bradgrafelman

Glad I could help! Don't forget to mark this thread resolved (if it is) using the link on the Thread Tools menu above.