php function within a sql query (help)

arie

Can anyone shed some light on how to get this to work?

I have a field that is a Unix Timestamp and a field that is an integer. The integer relates to a day, so 1 = 1 day, 3 = 3 days. What I want to do is subtract the integer from the timestamp and compare the result with now().

This will subtract 1 day from my timestamp:
$result = strtotime("-1day",$timestamp);

Is it possible to work this into a mySql query statement?

$result = @mysql_result(mysql_query("SELECT SendID table1 WHERE strtotime("-integerfieldday",timestamp ) <= $now"),0,0);

Thanks!

nemonoman

If using MySQL, check the manual for DATE_ADD or DATE_SUBTRACT

I like the alternate form, using + and - signs

I can't really figure out what you're trying to do, so here's some basics:

SELECT NOW() + INTERVAL 1 DAY

will return this time tomorrow

Here's the sort of thing you're trying to do, I think

SELECT (IF(myTimestamp + INTERVAL myInterval DAY>NOW(), 'This record is less than myInterval old', 'This record is more than myInterval old')
FROM myTable

If you want to be more precise about what you mean for your query to do, I'll be more able to help you with a query.

arie

Ok, let me give an example. Hope this makes sense.

table:
unixstamp, day, color
1086887761, 2, blue

$now = now();

$result = mysql_result(mysql_query("SELECT color FROM table WHERE (unixstamp - day <= $now LIMIT 1"),0,0);

So, if timestamp - 2 days is less than or equal to $now, return value.

I don't know if you can do something like this with field values within it's own a sql query to get a true or false, for example.

mysql_query("SELECT color FROM table WHERE A+B = $C")
A & B being field values.

Thanks!

nemonoman

For clarity's sake, I'm changing the column you call day to myDay

day == the sql reservered word DAY and since I'm using DAY inthe the query below, it could be very difficult and confusing

SELECT color
FROM table
WHERE timestamp - myDay INTERVAL DAY <= NOW()
LIMIT 1

arie

Doesn't seem to return anything. Here's the usage:

$startq2 = @mysql_result(mysql_query("SELECT SendID FROM table WHERE QueDate - Que2 INTERVAL DAY <= now() LIMIT 1"),0,0);

CREATE TABLE table (
SendID int(11) NOT NULL auto_increment,
QueDate int(11) NOT NULL default '0',
Que2 tinyint(4) NOT NULL default '0'
)
INSERT INTO table VALUES (80, 1086854400, 1);

If everything is working right, it should return '80'.

Thanks.

tekky

I would try the simpler fetch methods....
(assuming you want the FIRST array value from the result)
worth a try to try a different method of getting the result ya want 😉

$startq2 = current(mysql_fetch_array(mysql_query("SELECT SendID FROM table WHERE QueDate - Que2 INTERVAL DAY <= now() LIMIT 1")));

Originally posted by arie
Doesn't seem to return anything. Here's the usage:

$startq2 = @mysql_result(mysql_query("SELECT SendID FROM table WHERE QueDate - Que2 INTERVAL DAY <= now() LIMIT 1"),0,0);

[edit: coincidently... Recommended high-performance alternatives : mysql_fetch_row(), mysql_fetch_array(), mysql_fetch_assoc() and mysql_fetch_object(). --]

nemonoman

2 problems:

FIRST: I didn't see that you were apparently populating the column you call a unix type date. Why on earth are you doing that? . These aren't formatted like regular sql dates. Is there any reason why you can't use a regular TIMESTAMP, DATE or DATETIME type column?

Anyway, you have deformat the unix timestamp date to a normal type date.

SECOND:
I got dyslexic and messed up the sql:

SELECT SendID
FROM table
WHERE FROM_UNIXTIME( QueDate) - INTERVAL Que2 DAY <= NOW()
LIMIT 1

arie

No, I just needed to know how to get the right result (true/false).

Some people hate using mySql time formats. I tried it and decided it would be better to use unixtimestamps and convert it to a string when needed.

I was on the verge of discovering the FROM_UNIXTIME() function but you beat me to it!

I do appreciate your help very much! I try to do my own discovery, but sometimes it just quicker to ask someone who already knows! 🙂

That did the trick!

-Arie

nemonoman

If your work is data intensive in anyway, I suggest using SQL rather than UNIX timestamps. It's just easier in the long run: no internal conversions needed.

See DATE_FORMAT for any kind of SQL date formatting you might want.

PS
Mark thread RESOLVED if your problem is solved.