if statement (based on time)

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

if statement (based on time)

Post by malcolmboston »

i have built an app for a client that basically sends e-mails at a certain time that people have decided that they would like so for example

jason filled out the form and wants e-mails at 09:00, 18:00
nay filled out the form and wants e-mails at 07:00, 18:00
bech filled out the form and wants e-mails at 12:00, 23:00

now at the moment im looking into storing these into MySQL in the following fields:
- time1
- time2
- time3
- time4
- time5

now that isnt a problem at all, the problem i am having is that the script is called by a cron job at the hour, every hour. so i need to create an if statement that basically is:

Code: Select all

// pseudo
if (current time is 03:00);
{
//send mails to users with 03:00 in the database
}
now ive already built the query and resulting array, but cant seem to get around how i would create the if statement
i have something like this in mind:

Code: Select all

<?php
require("some_other_pages.php");
// first we need to get the time
// i will be only adding the leading char's
// so it easier for the script to work with
// and stop errors for eg 23 = 11pm
$time = time("G");
// lets start the query and data extraction
$query = "SELECT * FROM users WHERE time1 OR time2 OR time3 OR time4 OR time5 = $time";
// now actually run the query
$result = mysql_query($query) or die(mysql_error());
// now lets get the resulting data from the database (mysql)
$row1 = mysql_fetch_array($result, MYSQL_ASSOC);
// now lets get the num rows so we can create a more
// resource friendly script
$number = mysql_num_rows($result);
// ok we have the time now we should start making the
// if / elseif statements
if ($time == 1)
{
// $subject + $message, would be defined elsewhere in
// an inc page
mail("$row1[email]", "$subject", "$message");
// now we need to loop through the results in case
// theres more than one
if ($number >= 1)
{
while ($row2 = mysql_fetch_assoc($result))
mail("$row2[email]", "$subject", "$message");
}
}
// i would do the same for the other if statements
// if (time == 4) etc etc
would this work
and is there anyway i could improve teh script?
Illusionist
Forum Regular
Posts: 903
Joined: Mon Jan 12, 2004 9:32 pm

Post by Illusionist »

Woah! I totally dont understand why you are doing it this way:

Code: Select all

$row1 = mysql_fetch_array($result, MYSQL_ASSOC);
$number = mysql_num_rows($result);
if ($time == 1)
{
mail("$row1[email]", "$subject", "$message");
if ($number >= 1)
{
while ($row2 = mysql_fetch_assoc($result))
mail("$row2[email]", "$subject", "$message");
}
}
why do you have the if $time == 1?? whats that going to do for you? Then if and only if it was 1 it would mail. I dont thinkt ahts waht you want. Why not just do:

Code: Select all

while ($row = mysql_fetch_assoc($result)){
     mail("$row[email]", "$subject", "$message");
}


Oh wait i jsut saw you last comment( iw ould do the same for the other if statement $time == 4 ..... No you wouldn't have to do that. Your SELECT statement does that already. it will pull everyone out that wants to be emailed at the specific time of day, and then it will mail to all those users. You dont have to do any if statements.
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post by malcolmboston »

i wrote:

Code: Select all

if ($number >= 1) 
{ 
while ($row2 = mysql_fetch_assoc($result)) 
mail("$row2[email]", "$subject", "$message"); 
}
i did the $number if statement so it makes it slightly easier on PHP, and so it wont go through that bit of the script if not required
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post by malcolmboston »

ok, i completed the script last night and after debugging it, its all working cool (at least it will be when i change the mail() function.

now i have another "in theory" problem, which i am unsure how to address with my current knowledge on the subject.

ok as i earlier explained i hold the information in a database under the fields time1, time2, time3, time4, time5

now at registration the user will obviously select some and my insert command will do the rest of the work for me

now the problem i am having is:
say jason has registered and he has chosen times of
1) 05:00
2)14:00
3)21:00

ok thats time1, time2 and time3 taken up, say he wants to add a new time how do i get mysql to know to put it into time4?

hoping someone can enlighten me
Thanks
redmonkey
Forum Regular
Posts: 836
Joined: Thu Dec 18, 2003 3:58 pm

Post by redmonkey »

I would be tempted to just use a two column table containing 'user id' and 'time'. Then a simple select on the single 'time' colum would return everybody that requires a mail sent to them.
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post by malcolmboston »

yeah believe me red, thats what i want to do, unfortunately a user can select multiple times of the day so its not an option :(

unless you can think of a way around it?
redmonkey
Forum Regular
Posts: 836
Joined: Thu Dec 18, 2003 3:58 pm

Post by redmonkey »

Just add another entry into the database.

SO a table like....

Code: Select all

UID TIME

1     13:00
2     09:00
3     14:00
1     14:00
2     13:00
So selecting a time of 14:00 would return UIDs 1 and 3.
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post by malcolmboston »

sorry im not understanding exactly what your saying,

sounds intriguing though.............
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post by magicrobotmonkey »

you just have multiple entries in the database. To find out all the times for a certain user, you select by their user id. or to find out all the users that want mail at a certain time, you select by time.
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post by malcolmboston »

ah hang on a minute im not sure if this is what you mean but........

ok i have one table, it has groupname,username, password, email

should i be creating a seperate table with username as primary key with this table only holding times, sorry im a little slow today

if you could explain a lil' more :?
redmonkey
Forum Regular
Posts: 836
Joined: Thu Dec 18, 2003 3:58 pm

Post by redmonkey »

Your primary key would need to be time,username
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post by malcolmboston »

ah thanks

ok but how will that stop the need for multiple fields?

im confused :roll:
redmonkey
Forum Regular
Posts: 836
Joined: Thu Dec 18, 2003 3:58 pm

Post by redmonkey »

Not sure how to make it any clearer but......

You have your user table, you then create an EMAILTIME table which would look something like this....

Code: Select all

USERNAME   TIME

Jason      09:00
Jason      13:00
Malcolm    12:00
Jason      16:00
Malcom     13:00
Malcolm    16:00
Jason      19:00
You then have a select similar to....

Code: Select all

SELECT ET.USERNAME, U.EMAIL LEFT JOIN USER U ON (ET.USERNAME = U.USERNAME) FROM EMAILTIME ET WHERE TIME = 13:00
Which will return something like...

Code: Select all

USERNAME   EMAIL

Jason      jasons@mail.address.com
Malcom     malcolms@mail.address.com
The actual select syntax may not be 100% accurate but it should give you a general idea.
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post by malcolmboston »

ah i see what your getting at now, very nice idea

how do i link the 2 tables????? lol
redmonkey
Forum Regular
Posts: 836
Joined: Thu Dec 18, 2003 3:58 pm

Post by redmonkey »

Slight correction on the select...

Code: Select all

SELECT ET.USERNAME, U.EMAIL FROM EMAILTIME ET LEFT JOIN USER U ON (ET.USERNAME = U.USERNAME) WHERE TIME = 13:00
mySQL is a relational database, it's all about linking tables together, if you are unclear as to how to accomplish this then I would suggest you go have a good google round for some basic tutorials and also consult the manual.
Post Reply