467,135 Members | 1,195 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,135 developers. It's quick & easy.

Help wanted - Struggling with 'updated'-NOW() field

I try to learn SQL by figuring out things.

I want to make a listing of all records that were changed in the
last... 1, 6, 12 hours/days.

I have a field called 'updated' managed like: UPDATE tablename SET
updated = NOW(), .... WHERE....

I created a query like this: "SELECT * FROM tablename WHERE 'updated'
'$startstring' ORDER BY 'updated'"
// $startstring holds the date-time string exactly like the 'updated'
field should look.

When in php i do: if($record[updated] $startstring) ..show record..;
it works, but then I must pull the entire database.

In the much more efficient WHERE clause it does not work.

Where am i going wrong?
--
/Kees
Nov 17 '06 #1
  • viewed: 2339
Share:
8 Replies

Keith wrote:
I try to learn SQL by figuring out things.

I want to make a listing of all records that were changed in the
last... 1, 6, 12 hours/days.

I have a field called 'updated' managed like: UPDATE tablename SET
updated = NOW(), .... WHERE....

I created a query like this: "SELECT * FROM tablename WHERE 'updated'
'$startstring' ORDER BY 'updated'"

// $startstring holds the date-time string exactly like the 'updated'
field should look.

When in php i do: if($record[updated] $startstring) ..show record..;
it works, but then I must pull the entire database.

In the much more efficient WHERE clause it does not work.

Where am i going wrong?
--
/Kees
try echoing the query

Nov 17 '06 #2
"strawberry" <za*******@gmail.comwrote:
>
Keith wrote:
I try to learn SQL by figuring out things.

I want to make a listing of all records that were changed in the
last... 1, 6, 12 hours/days.

I have a field called 'updated' managed like: UPDATE tablename SET
updated = NOW(), .... WHERE....

I created a query like this: "SELECT * FROM tablename WHERE 'updated'
'$startstring' ORDER BY 'updated'"
// $startstring holds the date-time string exactly like the 'updated'
field should look.

When in php i do: if($record[updated] $startstring) ..show record..;
it works, but then I must pull the entire database.

In the much more efficient WHERE clause it does not work.

Where am i going wrong?
--
/Kees

try echoing the query
Sorry its standard testing for me, but that shows exactly what i want
it to show:

SELECT * FROM tablename HAVING 'updated' '2006-11-16 23:25:22' ORDER
by 'updated'

--
/Keith
Nov 17 '06 #3
"strawberry" <za*******@gmail.comwrote:
>
Keith wrote:
I try to learn SQL by figuring out things.

I want to make a listing of all records that were changed in the
last... 1, 6, 12 hours/days.

I have a field called 'updated' managed like: UPDATE tablename SET
updated = NOW(), .... WHERE....

I created a query like this: "SELECT * FROM tablename WHERE 'updated'
'$startstring' ORDER BY 'updated'"
// $startstring holds the date-time string exactly like the 'updated'
field should look.

When in php i do: if($record[updated] $startstring) ..show record..;
it works, but then I must pull the entire database.

In the much more efficient WHERE clause it does not work.

Where am i going wrong?
--
/Kees

try echoing the query
Just realise: it does work, but it does not select so te whole
database is pulled, so the WHERE clause does not do it's job.
--
/Keith
Nov 17 '06 #4

Keith wrote:
"strawberry" <za*******@gmail.comwrote:

Keith wrote:
I try to learn SQL by figuring out things.
>
I want to make a listing of all records that were changed in the
last... 1, 6, 12 hours/days.
>
I have a field called 'updated' managed like: UPDATE tablename SET
updated = NOW(), .... WHERE....
>
I created a query like this: "SELECT * FROM tablename WHERE 'updated'
'$startstring' ORDER BY 'updated'"
>
// $startstring holds the date-time string exactly like the 'updated'
field should look.
>
When in php i do: if($record[updated] $startstring) ..show record..;
it works, but then I must pull the entire database.
>
In the much more efficient WHERE clause it does not work.
>
Where am i going wrong?
--
/Kees
try echoing the query

Just realise: it does work, but it does not select so te whole
database is pulled, so the WHERE clause does not do it's job.
--
/Keith
eh?

Nov 17 '06 #5
> I created a query like this: "SELECT * FROM tablename WHERE 'updated'
> '$startstring' ORDER BY 'updated'"
Note that this query does not reference the updated field at all,
except for the "select *" part.
>// $startstring holds the date-time string exactly like the 'updated'
field should look.
'updated' is a string, not a field name.
>In the much more efficient WHERE clause it does not work.

Where am i going wrong?
`updated` is a field name. 'updated' is a string.
Nov 18 '06 #6
"strawberry" <za*******@gmail.comwrote:
>
Keith wrote:
"strawberry" <za*******@gmail.comwrote:
>
Keith wrote:
>
I try to learn SQL by figuring out things.

I want to make a listing of all records that were changed in the
last... 1, 6, 12 hours/days.

I have a field called 'updated' managed like: UPDATE tablename SET
updated = NOW(), .... WHERE....

I created a query like this: "SELECT * FROM tablename WHERE 'updated'
'$startstring' ORDER BY 'updated'"

// $startstring holds the date-time string exactly like the 'updated'
field should look.

When in php i do: if($record[updated] $startstring) ..show record..;
it works, but then I must pull the entire database.

In the much more efficient WHERE clause it does not work.

Where am i going wrong?
--
/Kees
>
try echoing the query
Just realise: it does work, but it does not select so te whole
database is pulled, so the WHERE clause does not do it's job.
--
/Keith

eh?
SELECT * FROM tablename HAVING 'updated' '2006-11-16 23:25:22' ORDER
by 'updated'
returns all records from the database not only those updated after
2006-11-16 23:25:22

Nov 18 '06 #7
go***********@burditt.org (Gordon Burditt) wrote:
I created a query like this: "SELECT * FROM tablename WHERE 'updated'
'$startstring' ORDER BY 'updated'"

Note that this query does not reference the updated field at all,
except for the "select *" part.
// $startstring holds the date-time string exactly like the 'updated'
field should look.

'updated' is a string, not a field name.
In the much more efficient WHERE clause it does not work.

Where am i going wrong?

`updated` is a field name. 'updated' is a string.
Wow, never saw that those back-ticks were so important, I always used
normal ticks and it worked, but not now.

THANK YOU
Nov 18 '06 #8
Keith wrote:
go***********@burditt.org (Gordon Burditt) wrote:

>>> I created a query like this: "SELECT * FROM tablename WHERE 'updated'
'$startstring' ORDER BY 'updated'"

Note that this query does not reference the updated field at all,
except for the "select *" part.

>>>// $startstring holds the date-time string exactly like the 'updated'
field should look.

'updated' is a string, not a field name.

>>>In the much more efficient WHERE clause it does not work.

Where am i going wrong?

`updated` is a field name. 'updated' is a string.

Wow, never saw that those back-ticks were so important, I always used
normal ticks and it worked, but not now.

THANK YOU
if updated is a column name then the query should look like:

SELECT * FROM tablename HAVING updated '2006-11-16 23:25:22' ORDER
by updated;

Depending on your datefield you may need to "convert" the time to something the
database can actually use.

It is always a good idea to test your select statements interactively before
moving it to your applciation.
--
Michael Austin.
DBA Consultant
Nov 19 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by phillip.s.powell@gmail.com | last post: by
1 post views Thread by rdraider | last post: by
23 posts views Thread by casper christensen | last post: by
reply views Thread by jon@starvingwebmasters.com | last post: by
4 posts views Thread by Paul David Buchan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.