Date difference

dcjones

Hi all,

I have 2 fields in a table

field 1 = accomarival TYPE timestamp
field 2 = accomdepart TYPE timestamp

I have a MySQL query:

SELECT * , DATE_FORMAT(departuredate, '%d/%m/%Y') as d_date, DATE_FORMAT(accomarival, '%d/%m/%Y') as a_date, DATE_FORMAT(accomdepartdate, '%d/%m/%Y') as ac_date, DATE_FORMAT(profiledate, '%d/%m/%Y') as p_date
FROM amember_baggagemembers
WHERE member_id = colname

What I am trying t do is run an if statement which will look at todays date, and if it is in between "a_date" and "ac_date" display X, and if not in between "a_date" and "ac_date", display Y.

This is what I have

if ($row_owners['a_date'] >= date("d, m, Y") && $row_owners['ac_date'] <= date("d, m, Y")) {
echo $row_owners['houseno'];
} 
else
{
echo $row_owners['accom1'];
 }

Am I doing this corectly.

bradgrafelman

Don't use "SELECT *" in your SQL queries - for optimization and scalability, you should instead list ONLY the specific columns that you plan on using.

Why not let SQL do the work for you?

SELECT CASE
    WHEN NOW() BETWEEN `a_date` AND `ac_date` THEN `houseno`
    ELSE `accom1`
END AS `myField`
FROM amember_baggagemembers 
WHERE member_id = 1

Then, just use 'myField' when retrieving the data in PHP.

dcjones

Hi bradgrafelman, and thanks for your reply.

I totally understand where your coming from by using the query t do the work for me but in this case the complete query as 45 variables and there is only 49 in the data table. I only posted a small part of the complete query.
But is it possible to use somethmg like:

if ($row_owners['a_date'] >= date("d, m, Y") && $row_owners['ac_date'] <= date("d, m, Y")) {
echo $row_owners['houseno'];
}
else
{
echo $row_owners['accom1'];
}

or is this not going to work.

dcjones

Hi all,

I am going around the twist trying to make this work.

I want to be able to look at 2 dates, lets call them away dates, "away_date1" and "away_date2"

If todays date is not equal to or between "away_date1" and "away_date2"
echo "this"

If todays date is equal to or between "away_date1" and "away_date2"
echo "that"

Is this possible. This is what I am working with:

if ($row_owners['away_date1'] <= date("d, m, Y") && $row_owners['away_date2'] >= date("d, m, Y")) {
echo "This";
} 
else
{
echo "That";
}

I have tried all combinations, but not the correct on.

Can anyone please advise.

bradgrafelman

It won't work in PHP because PHP can't compare timestamps (like MySQL can). This is why I suggested the DB method - because it would be easier.

dcjones

Hi bradgrafelman,

Thanks for your reply. I did not know that PHP could not perform a date difference, sorry.

I should now follow your recommendation, once I can find out how the "SELECT CASE" works.

I think a may still have a problem, depending on the content of "a_date! and "a_date" I want the value of 6 variables to change.

The background is this: if a person is away on holiday between x and y dates, then echo thier location address. So I have this:

Not on holiday:
house No = houseno
street = street
town = town
state = state
country = country
zip = zip

On holiday
house No = a_no
street = a_street
town = a_town
state = a_state
country = a_country
zip = a_zip

Then I turn to my query.

SELECT CASE
    WHEN NOW() BETWEEN `a_date` AND `ac_date` THEN `houseno`
    ELSE `accom1`
END AS `myField`
FROM amember_baggagemembers 
WHERE member_id = 1

How do I represent all my variable within this query.

I have never come across the "SELECT CASE, WHEN NOW() BETWEEN". Can you please help me through this. I know your time is valuable and it is much appreciated if you can. I am learning all the time.

bradgrafelman

EDIT: Since I'm no DB guru, I can't get this to work like it should. So, your best bet (as far as I can recommend) might be to use [man]strtotime[/man] in PHP to convert the SQL timestamp fields to a Unix timestamp and compare them that way.