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

Keith

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

strawberry

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

Keith

"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

Keith

"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

strawberry

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

Gordon Burditt

> 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

Keith

"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

Keith

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

Michael Austin

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