JeRRy wrote:
Hi,
No code here unless required.
I run this tipping site. All user details are in a table called tipping which stores their id, username, password, email address, score and others.
Than I have tables round 1 through to round 22. (all seperate tables)
This is where users tip to. I have a tipping deadline, however I run this from a javascript countdown code and not connected in any way to the db. (maybe I should for what I need)
in each round i have id, username, game1 through to game 8, score (for that round only) and a column called Misc. Misc is where I know if the user has tipped or not, on default it's NULL, if they tip it's y.
I want to run a PHP script generally 24 hours before the end of the round closing that sends an email ONLY to users that have NOT tipped to date on script running. The email will have details that they have not tipped and a web-url. Now I am not sure how to do this.
As mentioned below the email address for users are in a different table. Should I put the email value in the rounds table also? How would I go about getting a script to do this?
I know I could send a cronjob to execute it on a set say. And I could change this if the time needs changing in cronjobs. Just need help on the script.
So I need the script to read the desired round and look for the username and misc. If misc y than ignore it and the username. If NULL than grab the username and match it with the email address in the other table. Or I could add the email value in that table if it makes it easier that way.
Preffered email format is Plain-text. But any code that supports HTML is fine too. As long as it recognises what's HTML and what's not. (example if i press the ENTER key it must recognise this as a enter and not a space.)
Why did you put the rounds in separate tables?
without knowing the table structure(s) involved, this is just a guess so
it will need adjusting:
if you pre-fill the tables with the users info this will work:
select * from round_table r, users u where r.userid=u.userid and
r.misc=null;
if not something like this should do it:
select * from round_table r LEFT OUTER JOIN users u ON r.userid=u.userid
WHERE u.userid IS NULL;
that will give you all users who have not tipped.
then
$result = db_query($query);
while($row = db_fetch($result)) {
mail($row['email'], 'You need to tip', 'You need to tip in this weeks
competition');
.......
}
....
db_query will depend on your database (ie mysql_query or pg_query or
other ...).
if that query doesn't make sense (left outer join), do it in two steps
so you understand what's going on:
$query = "select * from users";
$result = mysql_query($query);
while($row = db_fetch($result)) {
$query_two = "select 1 from round_table where userid=" . $row['userid'];
$result_two = db_query($query_two);
$has_tipped = db_fetch($result_two);
// if has_tipped is empty, there have been no tips placed by this user.
if (empty($has_tipped)) {
mail($row['email'], 'you need to tip'.......
}
}
done!
--
Postgresql & php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
