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

Re: Help wanted - Struggling with "updated"-NOW() field

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

Re: Help wanted - Struggling with "updated"-NOW() field

"strawberry" wrote:

>
> 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

Re: Help wanted - Struggling with "updated"-NOW() field

"strawberry" wrote:

>
> 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

Re: Help wanted - Struggling with "updated"-NOW() field

Keith wrote:
> "strawberry" wrote:
>
> >
> > 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?

Re: Help wanted - Struggling with "updated"-NOW() field

> 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.

Re: Help wanted - Struggling with "updated"-NOW() field

"strawberry" wrote:

>
> Keith wrote:
> > "strawberry" wrote:
> >
> > >
> > > 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

Re: Help wanted - Struggling with "updated"-NOW() field

gordonb.6ap7w@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

Re: Help wanted - Struggling with "updated"-NOW() field

Keith wrote:

> gordonb.6ap7w@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