Home > Archive > PHP DB > April 2006 > Re: [PHP-DB] grabbing data and auto email set users
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Re: [PHP-DB] grabbing data and auto email set users
|
|
|
| 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 sur
e 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 t
hat 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 w s
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/
| |
|
| 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
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'.......
}
}
done!
--
Postgresql & php tutorials
http://www.designmagick.com/
| |
|
| 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 ...).
so replace it with mysql_query.
--
Postgresql & php tutorials
http://www.designmagick.com/
| |
|
| 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.
J
Chris <dmagick@gmail.com> 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 ...).
so replace it with mysql_query.
--
Postgresql & php tutorials
http://www.designmagick.com/
| |
|
| 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.
--
Postgresql & php tutorials
http://www.designmagick.com/
| |
|
| 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. (i
t's probably basic)
http://pastebin.com/650846
J
Chris <dmagick@gmail.com> 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.
--
Postgresql & php tutorials
http://www.designmagick.com/
| |
|
| 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)
--
Postgresql & php tutorials
http://www.designmagick.com/
| |
|
| 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.
--
Postgresql & php tutorials
http://www.designmagick.com/
| |
|
| Okay, but still not working...
is NULL the same as ' '? Or does NULL have to be the inserted value in the db?
J
Chris <dmagick@gmail.com> 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.
--
Postgresql & php tutorials
http://www.designmagick.com/
| |
|
| 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:
select username from round3 where misc=NULL;
?
--
Postgresql & php tutorials
http://www.designmagick.com/
| |
|
| Hi,
Ahh okay I thought NULL was blank. Okay I understand, therefore, hehe, I fixed it.
<snip>
$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 <dmagick@gmail.com> 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:
select username from round3 where misc=NULL;
?
--
Postgresql & php tutorials
http://www.designmagick.com/
| |
|
| JeRRy wrote:
> Hi,
>
> Ahh okay I thought NULL was blank. Okay I understand, therefore, hehe,
> I fixed it.
>
> <snip>
>
> $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 <mailto:username@servername.com> ) Is there a
> way to change that and the subject.
You set the subject:
mail($to, $subject, $message, $headers);
You can set the "From" in the headers:
$headers = "From: your_email_address\n";
$headers .= "Reply-To: your_email_address\n";
See http://www.php.net/mail for more info.
--
Postgresql & php tutorials
http://www.designmagick.com/
| |
|
| 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("
<br><input type=text name=username[$a] value=%s> <br>",
$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.");}
?>
<a href="admin.php">BACK</a>
<input type="submit" value="submit">
</form>
</p>
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 <dmagick@gmail.com> 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.
You set the subject:
mail($to, $subject, $message, $headers);
You can set the "From" in the headers:
$headers = "From: your_email_address\n";
$headers .= "Reply-To: your_email_address\n";
See http://www.php.net/mail for more info.
--
Postgresql & php tutorials
http://www.designmagick.com/
| |
| Dwight Altman 2006-04-10, 6:57 pm |
| 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 <dmagick@gmail.com> 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.
--
Postgresql & php tutorials
http://www.designmagick.com/
| |
|
| 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("
> <br><input type=text name=username[$a] value=%s> <br>",
> $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.");}
> ?>
> <a href="admin.php">BACK</a>
> <input type="submit" value="submit">
> </form>
> </p>
>
> 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
.... ?
--
Postgresql & php tutorials
http://www.designmagick.com/
|
|
|
|
|