IF statement in MySQL?

[Atli]

Hi.

I'm trying to create a query that would check a couple of fields, and depending on the results, insert a row into the table.

This is what I have so far:

SELECT IF(
  (SELECT count(IP) FROM tbl_CounterLog WHERE Date = NOW() AND IP = '172.0.0.1') > 0,
  2,
  (INSERT INTO tbl_Test(IP, Date) VALUES('172.0.0.1', NOW()))
) AS 'Result'
 

This keeps giving me an error at the 'INTO' in the insert statement, but if I replace it with a select statement if works fine.
Perhaps it's not possible to use INSERT as a sub-query.

Anybody know of a way to do this?

[write2ashokkumar]

You have to understand the difference between SELECT and INSERT statement.

[ronverdonk]

Apart from the SELECT, which you have to issue separately, why not use

INSERT INTO tbl_Test(IP, Date)
 SELECT tbl_CounterLog.IP, tbl_CounterLog.Date
  FROM tbl_CounterLog WHERE tbl_CounterLog.IP='172.0.0.1' AND tbl_CounterLog.Date=NOW();

Ronald :cool:

[Atli]

Thanks for the suggestion Ronald.

The two tables I used in my query tbl_Test and CounterLog are supposed to be the same table, my mistake. Tho it doesn't really matter in our queries .

The way you wrote it it duplicates the values in the table if the values already exists.
What I was trying to do is the exact opposite to that, that is write the values if they don't already exist.

However, thanks to you, I did manage to do that.
The Date field is a NOT NULL field, so I rewrote your query so it either inserts the row or triggers a NULL error.

INSERT INTO tbl_Test(IP, Date)
SELECT '172.0.0.1',  IF(
     (SELECT count(IP) FROM tbl_Test WHERE IP = '172.0.0.1' AND Date = NOW()) = 0,
     (SELECT NOW()),
      NULL
    );
 

So thanks again Ronald for pointing me in the right direction.

[ronverdonk]

That is what I am here for!

Ronald :cool:

[imran haq]

That is what I am here for!

Ronald :cool:

That is what I am here for!

Ronald :cool:

Hi Ronald;

How are you ?

I have 3 rather Long Questions that are causing alot of trouble:
I would appreciate your help since i have had a really hard time, and tried to use ur last post to atli to help but it did not help...

!) I have an events table, within the events table I have 7 columns,
Alias varchar (30)
Progress int(1)
Val double
Stamp timestamp
ID auto increment.
date date
time time

My problem is very similar to Atli, where he wanted to perform a mysql if statement on the result of a count function.
Basically, My progress column is intented to hold a '1' or a '0'

so here we go....

if the result of the count of occurance Alias = 'Test' modulus 2 (i.e. count(Alias) mod 2)
is equivelant to '0', then we wish to input a '1' into the 'progress' column of my 'events' table, if the result of count(Alias) mod 2 where Alias = 'Test' equals '1' then i would like to put in a '0' into my 'progress' column of my 'events' table.

This process must happen every time that a record is entered with the Alias = 'Test' - so that probably means that we need to put it in a trigger of some sort.

Also is there a way for replacing Alias = 'Test' for Alias = 'select distinct alias from events'????? so that we have something like this:

Select count(Alias) mod 2 from events where Alias = Select distinct Alias from events. and then enclose that in an if statement and then a trigger.

This 2nd query is ideally what we would like because my table could potentially hold 1000 records or even more.

thankyou

2) in the same 'events' table i have a stamp column, holding a timestamp that has been inserted into my table, what i need to do is, to get what ever is in the stamp column, and cut it into time and date, the way i thought would be good was to insert that value into the date and time columns that are in my events table????
and again this would have to be for every row in my table, and every newly inserted row, and would therefore require a trigger....coul d you help me out at all?
this has also given me a huge hadache.

one Last Question: will the trigger come into action every time the MYSQL server instance is initialised, you see everything is local at the moment on my laptop, and is configured so that the instance starts upon boot, will that mean that the triggers will also come into play asap?



Thankyou very much for your help in advance - this is very urgent and i have been bouncing my head of walls for days.
I really appreciate your time and effore
imran