List:General Discussion« Previous MessageNext Message »
From:Shawn Green Date:April 10 2006 8:37pm
Subject:Re: How to pass parameters in MYSQL
View as plain text  

--- Ravi Malghan <rmalghan@stripped> wrote:

> Hi: I have a statement such as follows
> 
> SELECT EVENT_DATA.dstport WHERE
> FROM_UNIXTIME(utime,'%Y-%m-%d') = (CURDATE() -
> INTERVAL 1 DAY) ORDER BY EVENT_DATA.utime;
> 
> Instead of using a value of 1 for interval, I want to
> pass parameters so it will ask for the value when the
> sql statement is run. I have tried searching in google
> and www.mysql.org and have not had any luck. Does
> mysql use different definitions? or can someone
            =====================

Different than.....(what)???

> provide some pointers on the web which explains how to
> pass parameters with SQL statement.
> 
> Thanks
> Ravi
> 

In order to have parameters, you have to have do things procedurally.
That means that to have replaceable values in your SQL (parameterized
queries and statements) you have to use a STORED PROCEDURE, a FUNCTION,
a prepared statement, or a user-defined function to encapsulate your
code. 

All of those features are new to MySQL as of v5.0.

Also, if your utime field is already a unix_timestamp value why are you
converting it to a date in order to compare it to the results of
another calculation?  In order to possibly use an index, you need to
leave it as a unix_timestamp value and convert your calculation to
match

...
WHERE utime >= UNIX_TIMESTAMP(CURRDATE() - interval 1 day)
...

That gets everything for the last 24 hours (exactly).  

The conversions all belong on one side of the comparison. Your fields
should not be part of a conversion or function if you ever hope to use
an index during the lookup phase.

http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 
Thread
How to pass parameters in MYSQLRavi Malghan10 Apr
  • Re: How to pass parameters in MYSQLShawn Green10 Apr
  • Re: How to pass parameters in MYSQLPeter Brawley10 Apr