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.)
Could anyone please help me here?
Thanks for your time.
J
--0-1005873128-1144638874=:33684--
Re: grabbing data and auto email set users
am 10.04.2006 05:57:05 von Chris
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'.......
}
}
Fatal error: Call to undefined function: db_fetch() in /home/tassie/public_html/tipping/admin/check.php on line 7
Any help please?
J
$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'.......
}
}
JeRRy wrote:
>
> Hi,
>
> I used the following but got this error:
>
> *Fatal error*: Call to undefined function: db_fetch() in
> */home/tassie/public_html/tipping/admin/check.php* on line *7*
As I said:
db_query will depend on your database (ie mysql_query or pg_query or
other ...).
I changed it to "mysql_query" and no errors produce however no emails are sent either. After cross-checking back and fourth with my db the entries are correct.
A connection is made and establlished. maybe I need to place some eror recording to see where it's failing.
I can't seem to get it to work, changed a heap of code but nothing budges.
I can send the code I have and table structure if you want.
J
Chris wrote:
JeRRy wrote:
>
> Hi,
>
> I used the following but got this error:
>
> *Fatal error*: Call to undefined function: db_fetch() in
> */home/tassie/public_html/tipping/admin/check.php* on line *7*
As I said:
db_query will depend on your database (ie mysql_query or pg_query or
other ...).
JeRRy wrote:
> Hi,
>
> Okay I must be missing something here.
>
> I changed it to "mysql_query" and no errors produce however no emails
> are sent either. After cross-checking back and fourth with my db the
> entries are correct.
>
> A connection is made and establlished. maybe I need to place some eror
> recording to see where it's failing.
>
> I can't seem to get it to work, changed a heap of code but nothing budges.
>
> I can send the code I have and table structure if you want.
Post it on http://www.pastebin.com and send us the url.
Okay I played with the code a bit and ended up with this, it does NOT produce errors but it's not doing anything. Connects to the db but does not do the mail out. Maybe I missed something said before, but here you go, the link below with the code. (it's probably basic)
http://pastebin.com/650846
J
Chris wrote:
JeRRy wrote:
> Hi,
>
> Okay I must be missing something here.
>
> I changed it to "mysql_query" and no errors produce however no emails
> are sent either. After cross-checking back and fourth with my db the
> entries are correct.
>
> A connection is made and establlished. maybe I need to place some eror
> recording to see where it's failing.
>
> I can't seem to get it to work, changed a heap of code but nothing budges.
>
> I can send the code I have and table structure if you want.
Post it on http://www.pastebin.com and send us the url.
JeRRy wrote:
> Hi,
>
> Okay I played with the code a bit and ended up with this, it does NOT
> produce errors but it's not doing anything. Connects to the db but does
> not do the mail out. Maybe I missed something said before, but here you
> go, the link below with the code. (it's probably basic)
You're mixing up your ideas.
Try this.
http://pastebin.com/650869
(the Mail function call was completely wrong - read the manual -
http://www.php.net/mail)
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: grabbing data and auto email set users
am 10.04.2006 09:57:35 von Chris
JeRRy wrote:
> Hi,
>
> Okay I played with the code a bit and ended up with this, it does NOT
> produce errors but it's not doing anything. Connects to the db but does
> not do the mail out. Maybe I missed something said before, but here you
> go, the link below with the code. (it's probably basic)
Actually try this one:
http://pastebin.com/650871
You had an extra loop just for 'tippings' but never used it anywhere.
is NULL the same as ' '? Or does NULL have to be the inserted value in the db?
J
Chris wrote:
JeRRy wrote:
> Hi,
>
> Okay I played with the code a bit and ended up with this, it does NOT
> produce errors but it's not doing anything. Connects to the db but does
> not do the mail out. Maybe I missed something said before, but here you
> go, the link below with the code. (it's probably basic)
Actually try this one:
http://pastebin.com/650871
You had an extra loop just for 'tippings' but never used it anywhere.
Ahh okay I thought NULL was blank. Okay I understand, therefore, hehe, I fixed it.
$query = "select username from round3 where misc=''";
fixed it, the email got sent.
Had to change a few strings also to match my database which I was not aware of. Forgot it actually.
Now the only thing I need to know is how do I change the sender email is, currently it sets to the server default username. (e.g. username@servername.com) Is there a way to change that and the subject.
Thanks for your time. I have learned a little bit off this also. So thankyou.
J
Chris wrote:
JeRRy wrote:
> Okay, but still not working...
>
> is NULL the same as ' '? Or does NULL have to be the inserted value in
> the db?
Null means unknown - so completely different to ' ' or ''.
How was your table created?
What do you get if you run that query through phpmyadmin:
JeRRy wrote:
> Hi,
>
> Ahh okay I thought NULL was blank. Okay I understand, therefore, hehe,
> I fixed it.
>
>
>
> $query = "select username from round3 where misc=''";
>
> fixed it, the email got sent.
>
> Had to change a few strings also to match my database which I was not
> aware of. Forgot it actually.
>
> Now the only thing I need to know is how do I change the sender email
> is, currently it sets to the server default username. (e.g.
> username@servername.com ) Is there a
> way to change that and the subject.
Okay I have the emails to work now, thanks so much Chris. But now I have encountered another problem, I hope you don't mind helping, I am obviously missing again something basic. Basic than before.
I have this code:
$result = mysql_query("SELECT `nickname` FROM tipping
WHERE 1
ORDER BY `score`
DESC LIMIT 0, 30",$db);
while ($myrow = mysql_fetch_row($result)) {
printf("
",
$myrow[0]);
}
?>
which displays all the users names.
than I want to push it to another page that tells the db to update, one username per record. Code below:
$query = "insert into round3 SET username='$username[$a]'";
mysql_query("$query");
echo(" ");
}
else {echo("Ooops, something bad happened! Please try again shortly.");}
?>
But when I check the db this is what I get.
A new record created but username is blank.
So it's creating a new record but leaving it blank, why?
Now at times the record will insert more than one record, so I need it able to update more than one record at a time.
Any help?
J
Chris wrote:
JeRRy wrote:
> Hi,
>
> Ahh okay I thought NULL was blank. Okay I understand, therefore, hehe,
> I fixed it.
>
>
>
> $query = "select username from round3 where misc=''";
>
> fixed it, the email got sent.
>
> Had to change a few strings also to match my database which I was not
> aware of. Forgot it actually.
>
> Now the only thing I need to know is how do I change the sender email
> is, currently it sets to the server default username. (e.g.
> username@servername.com ) Is there a
> way to change that and the subject.
How about:
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result)) {
instead of your line 6 and 7?
And why are you going back to db_query on line 9 instead of staying with
mysql_query?
Also, you might want to check $num_rows = mysql_num_rows($result);
instead of
line 10 $has_tipped = mysql_query($result_two);
and line 12 if (empty($has_tipped)) {
or do a while ($row = mysql_fetch_assoc($result_two)) {
(notice $result_two here) enclosing the mail command.
To recap:
Get a $result set from the mysql_query()
Get a $row from a while ($row = mysql_fetch_assoc($result)) {
-----Original Message-----
From: JeRRy [mailto:jusa_98@yahoo.com]
Sent: Monday, April 10, 2006 2:37 AM
To: Chris
Cc: php-db@lists.php.net
Subject: Re: [PHP-DB] grabbing data and auto email set users
Hi,
Okay I played with the code a bit and ended up with this, it does NOT
produce errors but it's not doing anything. Connects to the db but does not
do the mail out. Maybe I missed something said before, but here you go, the
link below with the code. (it's probably basic)
http://pastebin.com/650846
J
Chris wrote:
JeRRy wrote:
> Hi,
>
> Okay I must be missing something here.
>
> I changed it to "mysql_query" and no errors produce however no emails
> are sent either. After cross-checking back and fourth with my db the
> entries are correct.
>
> A connection is made and establlished. maybe I need to place some eror
> recording to see where it's failing.
>
> I can't seem to get it to work, changed a heap of code but nothing budges.
>
> I can send the code I have and table structure if you want.
Post it on http://www.pastebin.com and send us the url.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: grabbing data and auto email set users
am 11.04.2006 02:20:30 von Chris
JeRRy wrote:
> Okay I have the emails to work now, thanks so much Chris. But now I
> have encountered another problem, I hope you don't mind helping, I am
> obviously missing again something basic. Basic than before.
>
> I have this code:
>
> $result = mysql_query("SELECT `nickname` FROM tipping
> WHERE 1
> ORDER BY `score`
> DESC LIMIT 0, 30",$db);
> while ($myrow = mysql_fetch_row($result)) {
> printf("
>
",
> $myrow[0]);
> }
> ?>
>
> which displays all the users names.
>
> than I want to push it to another page that tells the db to update, one
> username per record. Code below:
>
> $query = "insert into round3 SET username='$username[$a]'";
> mysql_query("$query");
> echo(" ");
> }
> else {echo("Ooops, something bad happened! Please try again
> shortly.");}
> ?>
>
>
>
>
>
> But when I check the db this is what I get.
>
> A new record created but username is blank.
Where is $username[$a] meant to come from? You don't show enough code
about this to work out why.
Are you posting the results? Are you getting it from a select box? other
.... ?
Okay here is the full code. Now I got this code off a friend! In this first section to display the data that is grabbed from the db.
----
$db = mysql_connect("localhost", "user", "pass");
mysql_select_db("db_select",$db);
$result = mysql_query("SELECT `nickname` FROM tipping
WHERE 1
ORDER BY `score`
DESC LIMIT 0, 30",$db);
while ($myrow = mysql_fetch_row($result)) {
printf("
",
$myrow[0]);
}
?>
----
It displays the data fine. Please note in the above there is more code and I doubt the issue is in there as the data fetching shows fine, I believe the error lies somewhere below.
Than the form submit is pushed to another page with this code:
// NOTE that form fields automatically become variables
$query = "insert into round3 SET username='$username[$a]'";
mysql_query("$query");
echo(" ");
}
else {echo("Ooops, something bad happened! Please try again shortly.");}
?>
---
But when I check the db only ONE entry is added and it's blank. But on the the page that displays the data shows 12 records. So it's not updating for some reason? Any help?
J
--0-2101542476-1144740140=:77091--
Re: grabbing data and auto email set users
am 12.04.2006 02:18:54 von Chris
JeRRy wrote:
> Okay here is the full code. Now I got this code off a friend! In this first section to display the data that is grabbed from the db.
>
> ----
>
> $db = mysql_connect("localhost", "user", "pass");
> mysql_select_db("db_select",$db);
> $result = mysql_query("SELECT `nickname` FROM tipping
> WHERE 1
> ORDER BY `score`
> DESC LIMIT 0, 30",$db);
> while ($myrow = mysql_fetch_row($result)) {
> printf("
>
",
> $myrow[0]);
> }
> ?>
>
>
>
> ----
>
> It displays the data fine. Please note in the above there is more code and I doubt the issue is in there as the data fetching shows fine, I believe the error lies somewhere below.
>
> Than the form submit is pushed to another page with this code:
>
> ---
>
>
> include('../conf.inc.php');
> if ($REQUEST_METHOD == "POST") {
> $usr = "user";
> $pwd = "pass";
> $db = "db_select";
> $host = "localhost";
> $cid = mysql_connect($host,$usr,$pwd);
> mysql_select_db($db);
>
> // NOTE that form fields automatically become variables
Re: this comment - they only do if you have register_globals turned on.
If you have it turned off (very very good idea!), you have to:
$username = $_POST['username']['$a']
Then you can use that in your query instead of $username[$a]
Note that the quotes are important - otherwise it will look for variable
$a which doesn't exist (at least in this code).
I don't know why you have the input area named like this:
but that's up to you (me, I'd just call it 'username').