Help wanted - Struggling with "updated"-NOW() field
Help wanted - Struggling with "updated"-NOW() field
am 17.11.2006 16:19:35 von Hans-Peter Sauer
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
am 17.11.2006 21:15:02 von zac.carey
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
am 17.11.2006 23:29:59 von Hans-Peter Sauer
"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
am 17.11.2006 23:35:47 von Hans-Peter Sauer
"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
am 18.11.2006 01:13:13 von zac.carey
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
am 18.11.2006 01:25:57 von gordonb.6ap7w
> 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
am 18.11.2006 10:38:23 von Hans-Peter Sauer
"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
am 18.11.2006 12:50:07 von Hans-Peter Sauer
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
am 19.11.2006 21:19:15 von Michael Austin
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