Chris__T
Posts: 339
|
| Posted: 04/13/2007, 11:35 AM |
|
MySQL database. Stores the date as a timestamp. (field is otime)
CodeCharge search page has the search field for "otime" as a regular date input (mm/dd/yyyy)
So when I press the search button, the search parameter variable is s_otime. It searches the database based on this field.....but it's in mm/dd/yyyy and it's searching a timestamp column in the database, so naturally nothing is returned.
I need to be able (on search click), to:
1) pull s_otime from url. I assume it's CCGetParam() (i've had no problem with this)
2) convert this into a unix timestamp (again this looks like unix_timestamp() )
3) put this new converted date back into s_otime, back into url parameter list, and search the database with this new unix timestamp against what is in the database.
I've looked all over but can't seem to figure out how to go about this. Any thoughts/suggestions would be appreciated.
|
 |
|
Benjamin Krajmalnik
|
| Posted: 04/13/2007, 2:36 PM |
|
If this is on a grid, then instead of filtering on the URL value, filter on
an expression and set your expression to the onverted value.
"Chris__T" <Chris__T@forum.codecharge> wrote in message
news:6461fcd8be59a8@news.codecharge.com...
> MySQL database. Stores the date as a timestamp. (field is otime)
>
> CodeCharge search page has the search field for "otime" as a regular date
> input
> (mm/dd/yyyy)
>
> So when I press the search button, the search parameter variable is
> s_otime.
> It searches the database based on this field.....but it's in mm/dd/yyyy
> and it's
> searching a timestamp column in the database, so naturally nothing is
> returned.
>
> I need to be able (on search click), to:
>
> 1) pull s_otime from url. I assume it's CCGetParam() (i've had no
> problem
> with this)
>
> 2) convert this into a unix timestamp (again this looks like
> unix_timestamp() )
>
> 3) put this new converted date back into s_otime, back into url parameter
> list,
> and search the database with this new unix timestamp against what is in
> the
> database.
>
> I've looked all over but can't seem to figure out how to go about this.
> Any
> thoughts/suggestions would be appreciated.
>
>
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 04/14/2007, 4:45 AM |
|
Check this :
http://nl3.php.net/manual/en/function.strtotime.php
Basically you are entering a mm/dd/yyyy into s_otime.
Upon Submit of the searchform s_otime is put on the url as as.
I would not attempt to do any conversion like you describe in your step 2 as this would mess things up in your search form.
Alternative is to (in the resulting grid's) modify the SQL search parameter in the BeforeSelect.
Your grid will have some sql in which the s_otime is used in the where clause.
You could easily use the strtotime there to convert it just before teh SQL is executed.
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
wkempees
|
| Posted: 04/14/2007, 5:02 AM |
|
Sorry, ASP <> PhP, my mistake.
"wkempees" <wkempees@forum.codecharge> schreef in bericht
news:64620bee55c44c@news.codecharge.com...
> Check this :
> http://nl3.php.net/manual/en/function.strtotime.php
>
> Basically you are entering a mm/dd/yyyy into s_otime.
> Upon Submit of the searchform s_otime is put on the url as as.
> I would not attempt to do any conversion like you describe in your step 2
> as
> this would mess things up in your search form.
> Alternative is to (in the resulting grid's) modify the SQL search
> parameter in
> the BeforeSelect.
>
> Your grid will have some sql in which the s_otime is used in the where
> clause.
> You could easily use the strtotime there to convert it just before teh SQL
> is
> executed.
>
> Walter
>
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
Chris__T
Posts: 339
|
| Posted: 04/16/2007, 10:03 AM |
|
Thanks Walter! I will have to try that out and report back.
|
|
|
|
Chris__T
Posts: 339
|
| Posted: 04/16/2007, 10:56 AM |
|
Ok, so this is new to me, modifying SQL in a BeforeSelect. I looked in the help, but it's a bit confusing.
in the function tickets_BeforeSelect(Sender), I would put:
tickets.datasource.where = tickets.datasource.where & otime = " strtotime("s_otime")
?
At first I modified this line in visual query builder with strtotime() like this :
otime = strtime("s_otime") in the WHERE section, but it didn't like it.
|
|
|
|
Benjamin Krajmalnik
|
| Posted: 04/16/2007, 12:43 PM |
|
Chris,
In ASP you need to use the BeforeBuildSelect event.
Then you can simply add the following snippet:
if EventCaller.Where <> "" then
EventCaller.Where = EventCaller.Where & " AND"
end if
EventCaller.Where = EventCaller.Where & " otime = " &
CCToSQL(yourfunction(),"Text")
Now, strtotime is a php function, not an asp function, so you will get an
error.
"Chris__T" <Chris__T@forum.codecharge> wrote in message
news:64623b8df99d50@news.codecharge.com...
> Ok, so this is new to me, modifying SQL in a BeforeSelect. I looked in
> the
> help, but it's a bit confusing.
>
> in the function tickets_BeforeSelect(Sender), I would put:
>
> tickets.datasource.where = tickets.datasource.where & otime = "
> strtotime("s_otime")
>
> ?
>
> At first I modified this line in visual query builder with strtotime()
> like
> this :
> otime = strtime("s_otime") in the WHERE section, but it didn't like it.
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 04/16/2007, 3:38 PM |
|
I have to pass on the ASP syntax, sorry.
My original reply was well ment, but I overlooked the fact that you are using MySQL but not PhP.
But your SQL could look like this:
Select * from table where otime = UNIX_TIMESTAMP( {s_otime} );
SO my guess is you need to (in VQB) set the where for this part to expression.
Benjamin?
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 04/16/2007, 3:39 PM |
|
read and weap:
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-fu...n_from-unixtime
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
Chris__T
Posts: 339
|
| Posted: 04/17/2007, 9:20 AM |
|
Yeah, I tried that in VQB but it didn't work. I guess it doesn't like code in the VQB.
I went to your link for the from_Unixtime(). Hmm, it's making it more confusing! LOL. I'm determined to get this to work though.
Thanks for the help Walter. If anything else comes to your mind, don't hesitate to jot it down on here :)
|
|
|
|
Chris__T
Posts: 339
|
| Posted: 04/23/2007, 9:16 AM |
|
Odd, for some reason I just received Benjamin's replies, almost a week after he sent them. Weird. I will have to try out Ben's advice.
|
|
|
|
Chris__T
Posts: 339
|
| Posted: 04/23/2007, 9:23 AM |
|
Quote Benjamin Krajmalnik:
Chris,
In ASP you need to use the BeforeBuildSelect event.
Then you can simply add the following snippet:
if EventCaller.Where <> "" then
EventCaller.Where = EventCaller.Where & " AND"
end if
EventCaller.Where = EventCaller.Where & " otime = " &
CCToSQL(yourfunction(),"Text")
So in place of "yourfunction()" i'd put form.field.value ?
Like CCToSQL(tickets.otime.value, "text") ?
|
|
|
|
Chris__T
Posts: 339
|
| Posted: 04/23/2007, 11:09 AM |
|
Or some kind of conversion function?
|
|
|
|
Benjamin Krajmalnik
|
| Posted: 04/24/2007, 4:12 PM |
|
Not necessarily.
If the value is already in the format you need, then yes. If not, you need
to format it properly.
"Chris__T" <Chris__T@forum.codecharge> wrote in message
news:6462cdd9880f9b@news.codecharge.com...
> Quote Benjamin Krajmalnik:
> Chris,
>
> In ASP you need to use the BeforeBuildSelect event.
> Then you can simply add the following snippet:
>
> if EventCaller.Where <> "" then
> EventCaller.Where = EventCaller.Where & " AND"
> end if
> EventCaller.Where = EventCaller.Where & " otime = " &
> CCToSQL(yourfunction(),"Text")
>
>
>
> So in place of "yourfunction()" i'd put form.field.value ?
>
> Like CCToSQL(tickets.otime.value, "text") ?
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
Benjamin Krajmalnik
|
| Posted: 04/24/2007, 4:14 PM |
|
Correct.
Seacrh tjhe forum for my posts.
I haev already placed a function to convert unixtime to a datetime in
another post, but do not remember which one 
"Chris__T" <Chris__T@forum.codecharge> wrote in message
news:6462cf65706e8f@news.codecharge.com...
> Or some kind of conversion function?
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
Chris__T
Posts: 339
|
| Posted: 04/25/2007, 11:50 AM |
|
Thanks for the help Benjamin. I need to convert from date time to unixtime though.
I found your post but it was converting from unix to date. 
Quote Benjamin Krajmalnik:
Correct.
Seacrh tjhe forum for my posts.
I haev already placed a function to convert unixtime to a datetime in
another post, but do not remember which one
"Chris__T" <Chris__T@forum.codecharge> wrote in message
news:6462cf65706e8f@news.codecharge.com...
> Or some kind of conversion function?
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
Wkempees
|
| Posted: 04/25/2007, 12:30 PM |
|
Chris,
We had some trouble with the forum not syn chrnizing.
Did these help at all:
Quote :
I have to pass on the ASP syntax, sorry.
My original reply was well ment, but I overlooked the fact that you are
using
MySQL but not PhP.
But your SQL could look like this:
Select * from table where otime = UNIX_TIMESTAMP( {s_otime} );
SO my guess is you need to (in VQB) set the where for this part to
expression.
Quote :
Walter
|
|
|
|
Chris__T
Posts: 339
|
| Posted: 04/25/2007, 12:34 PM |
|
Walter,
No they didn't. I would get an error saying something like " invalid because object is closed." or something to that effect.
Chris
Quote Wkempees:
Chris,
We had some trouble with the forum not syn chrnizing.
Did these help at all:
Quote :
I have to pass on the ASP syntax, sorry.
My original reply was well ment, but I overlooked the fact that you are
using
MySQL but not PhP.
But your SQL could look like this:
Select * from table where otime = UNIX_TIMESTAMP( {s_otime} );
SO my guess is you need to (in VQB) set the where for this part to
expression.
Quote :
Walter
|
|
|
|
Benjamin Krajmalnik
|
| Posted: 04/26/2007, 12:02 PM |
|
http://www.ilovejackdaniels.com/asp/vbscript-date-format-functions/
function UDate(oldDate)
UDate = DateDiff("s", "01/01/1970 00:00:00", oldDate)
end function
Just remember that unixtime is is the number of seconds since 1/1/1970.
What this mens is that if you are also storing the time (and not only the
date), and you are searching for a date, you are going to have to create a
unix timestamp for your date at time 00:00:00 and also 23:59:59 and use a
whereclause such as:
yoursqlunixtimefield between value1 and value2
To find the above site with the additional functions I simply googled "asp
date functions".
In my case, I already kne that this site had what I needed so I went
directly to it, but it was on the first page, so it would not have taken
long to find it from scratch.
|
|
|
|
Chris__T
Posts: 339
|
| Posted: 04/26/2007, 12:27 PM |
|
Thanks Benjamin. I must have had a brain cloud or something, that UDate function is sitting printed on my desk right in front of me. Doh!
Yeah, when I was planning this out initially, I realized that you won't be able to search on a specific date, since when otime is captured at time of creating the ticket, it is date and time. And when you search, you will be searching date with time of 00:00:00. It will have to be through BETWEEN like you said. THanks for the heads up.
Chris
Quote Benjamin Krajmalnik:http://www.ilovejackdaniels.com/asp/vbscript-date-format-functions/
function UDate(oldDate)
UDate = DateDiff("s", "01/01/1970 00:00:00", oldDate)
end function
Just remember that unixtime is is the number of seconds since 1/1/1970.
What this mens is that if you are also storing the time (and not only the
date), and you are searching for a date, you are going to have to create a
unix timestamp for your date at time 00:00:00 and also 23:59:59 and use a
whereclause such as:
yoursqlunixtimefield between value1 and value2
To find the above site with the additional functions I simply googled "asp
date functions".
In my case, I already kne that this site had what I needed so I went
directly to it, but it was on the first page, so it would not have taken
long to find it from scratch.
|
|
|
|
Chris__T
Posts: 339
|
| Posted: 04/26/2007, 12:41 PM |
|
I guess I will have to modify that code snippet Benjamin gave me:
if EventCaller.Where <> "" then
EventCaller.Where = EventCaller.Where & " AND"
end if
EventCaller.Where = EventCaller.Where & " otime = " &
CCToSQL(yourfunction(),"Text")
to deal with the BETWEEN issue.
like..... EventCaller.Where = EventCaller.Where & " otime between " & CCToSQL(Udate(s_otime),"Text") & "AND" & CCToSQL(Udate(s_ctime),"Text")
where s_otime would be value1 in Benjamin's above reply and s_ctime would be value2.
s_otime, and s_ctime are the search text boxes where we get the date from the user, of course.
|
|
|
|
Chris__T
Posts: 339
|
| Posted: 05/01/2007, 11:22 AM |
|
tried with the above code.
if EventCaller.Where <> "" then
EventCaller.Where = EventCaller.Where & " AND"
end if
EventCaller.Where = EventCaller.Where & " otime = " & CCToSQL( UDate(zentrack_ticketsSearch.s_otime), "Text")
"Object doesn't support this property or method"
and microsoft script debugger points to the Udate function
(zentrack_ticketsSearch is the search record that s_otime is in)
I don't know. Udate() looks find to me.
|
|
|
|
|
 |
|
|
|
