create alert script?

[AshleyByrom]

Well here is my plan...

 

I have a database with a table named 'users', a column on the table is called admin, by default this is set to 0 (meaning they are not an admin) but for a few entries admin is set to 1.

 

I would like it so an admin can create an alert. When the admin creates the alert, whenever any user logs in (or loads a page) I would like a little alert box to pop up ONCE. So each user sees the alert ONCE.

 

This could be useful for lots of reasons I am just a bit stuck because i dont know how I could do it.

 

I could create an extra column on the table named 'seenAlert'. by default this would be 1 (meaning true, meaning yes). Then whenever an admin creates an alert I would set ALL entries underneath seenAlert to 0 (meaning false, meaning no). Then a script such as

 

<?PHP

 

if ($r["seenAlert"]==0) {

echo "<SCRIPT LANGUAGE='JavaScript'>"

echo "<!-- Hide from older browsers"

echo "alert('" . $alert . "');"

echo "// end hiding -->"

echo "</SCRIPT>"

}

[MatthewJ]

Ok

[AshleyByrom]

haha, i was kind of looking for help? tips? has anybody else done it? is my idea possible?

[rhodesa]

You will need a separate table to track if the alert has been seen. It will need two columns, user_id and alert_id.

 

To get alerts a user hasn't seen...i think this will work

SELECT * FROM alerts a LEFT JOIN seenAlerts s ON a.alert_id = s.alert_id AND s.user_id = '$user_id' WHERE s.user_id IS NULL

otherwise the slower was that i know will work is:

SELECT * FROM alerts WHERE alert_id NOT IN (SELECT alert_id FROM seenAlerts WHERE user_id = '$user_id')

[ignace]

messages
---------
id
message
display_type ENUM('once', ..) NOT NULL DEFAULT once

users
-----
id
username

users_messages
---------------
users_id
messages_id
read BOOLEAN NOT NULL DEFAULT FALSE

 

SELECT m.*
FROM messages m
JOIN users_messages um ON m.id = um.messages_id
WHERE um.users_id = % AND um.read = FALSE AND m.display_type = 'once'

 

UPDATE users_messages SET read = TRUE WHERE messages_id = % AND users_id = %

[ignace]

AND s.user_id = '$user_id' WHERE s.user_id IS NULL

 

Rhodesa can you explain this to me, as I don't exactly understand what you are doing here?

[rhodesa]

the first part:

SELECT * FROM alerts a LEFT JOIN seen_alerts s ON a.alert_id = s.alert_id AND s.user_id = '2'

will make a result set with every alert as a row. there will also be an extra column (from seen_alerts) with the user's id in it, if it's in seen_alerts. if there is no matching record in seen_alerts, the field will be null. that is where the second part comes in, we drop any records with a user_id to only leave null values left

[ignace]

if there is no matching record in seen_alerts, the field will be null.

 

Not entirely sure that I get it but you select all alerts for a specific user and all alerts that have not been assigned to users, is this correct?

[rhodesa]

it makes sense in my head, but i'm having trouble putting it into words...i'm going to assume the structures of the tables makes sense...now let's see some data:

users

| user_id | user_name |

|---------|-----------|

| 1      | John      |

| 2      | Jill      |

| 3      | Sally    |

 

alerts

| alert_id | alert_text      |

|----------|-----------------|

| 1        | This is alert 1 |

| 2        | This is alert 2 |

 

seen_alerts

| user_id | alert_id |

|---------|----------|

| 2      | 1        |

| 3      | 1        |

 

now, first, let's do the first part:

SELECT * FROM alerts a LEFT JOIN seen_alerts s ON a.alert_id = s.alert_id AND s.user_id = '2'

produces:

| alert_id | alert_text      | user_id |

|----------|-----------------|---------|

| 1        | This is alert 1 | 1      |

| 2        | This is alert 2 | NULL    |

so that shows us all the alerts and which ones a particular user has seen. now, let's get just the ones that user hasn't seen:

SELECT * FROM alerts a LEFT JOIN seen_alerts s ON a.alert_id = s.alert_id AND s.user_id = '2' WHERE s.user_id IS NULL

| alert_id | alert_text      | user_id |

|----------|-----------------|---------|

| 2        | This is alert 2 | NULL    |

 

[AshleyByrom]

wow, thanks for all the replies!

 

I tried my idea and it worked successfully but all of your options seem to be a lot more 'secure' so i will definitely try them out!!

 

The alert text is simply saved in a alert.txt file. When a new alert is sent out, alert.txt is cleared and wrote over.