suprsnipes

I'm using MySQL. A stored procedure sounds like a good idea but I need some help writing the query in the first place...

Help with SQL query I have a table with the following columns. ID1 TYPE1 PRICE1 SIZE1 I need to put together a query that calculates the following in the one step...previously I was calculating this in Excel. 1. IF(AND(TYPE1='N', PREVIOUS_ID1='a', PRICE1>=PREVIOUS_PRICE1),THEN INSERT INTO new column SUM(+SIZE1) 2. IF(AND(TYPE1='N', PREVIOUS_ID1='b', PRICE1<=PREVIOUS_PRICE1),THEN INSERT INTO new column SUM(-SIZE1) The SIZE1 column is shown as a number, for example 002, 011, 001 etc. If the above 1. statement is true then insert into new column the sum of size1 as a plus, if 2. statement is true insert into new column the sum of size1 as a negative for further analysis.

Need help with PHP translation from Excel to PHP. I can classify almost 80% of the data using queries but where I am running into problems is as you can tell from the formula I am comparing the current row with the previous row of data and the previous rows needs to be updated correctly before moving to the next. Here is the statement in Excel: =IF(AND(E2="trd",F3<>"",H2<=F3),"Sell",IF(AND(E2="trd",I3="Buy",H2<H3),"Sell ",IF(AND(E2="trd",I3="Sell",H2<=H3),"Sell",IF(AND(E2="trd",I3="Sell",H2>H3), "Buy",IF(AND(E2="trd",H2>=G3),"Buy",IF(AND(E2="trd",I3="Buy",H2>=H3),"Buy",I F(AND(E2="trd",H2>3000,F3>3000,H2>F3),"Buy","")))))))&IF(AND(E2="trd",G3<>"" ,H2<G3),"Sell","") E2 = Trade F3 = Previous BP H2 = TP H3 = Previous TP I3 = Previous Class G3 = Previous AP Translating into business logic: IF(AND(TRADE="trd",PREVIOUS_BP<>"",TP<=PREVIOUS_BP),"Sell", IF(AND(TRADE="trd",PREVIOUS_CLASS="Buy",TP<PREVIOUS_TP),"Sell", IF(AND(TRADE="trd",PREVIOUS_CLASS="Sell",TP<=PREVIOUS_TP),"Sell", IF(AND(TRADE="trd",PREVIOUS_CLASS="Sell",TP>PREVIOUS_TP),"Buy", IF(AND(TRADE="trd",TP>=PREVIOUS_AP),"Buy", IF(AND(TRADE="trd",PREVIOUS_CLASS="Buy",TP>=PREVIOUS_TP),"Buy", IF(AND(TRADE="trd",TP>3000,PREVIOUS_BP>3000,TP>PREVIOUS_BP),"Buy","") ) ) ) ) ) ) &IF(AND(TRADE="trd",PREVIOUS_AP<>"",TP<PREVIOUS_AP),"Sell","") If any member is willing to help me out here I would be very appreciative.

I'm getting an notice: Undefined variable:id on line 9 and line 11. 9. $result = mysql_query('Select `type` FROM `t2` WHERE `id`="'. $id . '" LIMIT 1'); 10. $row = mysql_fetch_assoc($result); 11. mysql_query('UPDATE `t1` SET type="' . $row['type'] . '" WHERE id="' . $id . '"');

Thanks for the reply Michdd. Sorry my definitions of the columns on t2 were slightly incorrect could you please update your suggested code for me. table 1 = t1 table 2 = t2 t1 column 1 = id t1 column 2 = type t2 column 1 = id1 t2 column 2 = type1

In basic terms I want to UPDATE table 1 column 2 with the data from table 2 column 2 WHERE table 1 column 1 = table 2 column 1 After spending 2 days on this and after trying all various methods I'm stumped., it just won't work. table 1 = t1 table 2 = t2 t1 column 1 = id t1 column 2 = type t2 column 1 = id t2 column 2 = type Can anyone help me?

21 views and not one reply...bit harsh

This is the SQL query. I'm not having a problem with this, but it's the steps afterwards, firstly let me explain what I am trying to do. CREATE TABLE t3 AS (SELECT * FROM t1 INNER JOIN t2 on t1.id=t2.id-1) id-1 represents the previous row (entry), id is set to auto_increment. What you can see from this is I that am joining these two tables together so that I can compare the row of data prior. Whilst this query works this is my problem. I would like to know if there is a better way to do this because I may need to run a query from id to perhaps id-20. otherwise I need to perform the query above many, many times. By the way once I have joined the tables the query I need to run is as follows; SELECT * FROM t3 WHERE column1=a AND column2<=column3 Appreciate the help.

This is what I have tried so far but just can't seem to get it right. SELECT `id` FROM `trades2` WHERE `type1` = 'b' AND `price`<=`price1` SELECT `day` `class` INSERT INTO `day` `class` VALUES 's'

Hi, I'm trying to perform the following SQL which is based on the following query that works fine; SELECT * FROM `trades2` WHERE `type1` = 'b' AND `price`<=`price1` What I am trying to do which I am stuck on is I want to label the result as an 's' and insert the result into another table where the column 'id' is the same, in saying that how do I allow for the 'id' column? Basically I want to INSERT INTO `day` (`class`) VALUE ('s') in conjunction with the above SQL query... But perhaps I need to include the id so they match, for example id=id

I have placed an attachment asking for a little help with putting this query together. ID is assigned auto-increment, the other columns are Type, Price and Size. Please forgive my explanation but I was previously calculating this in Excel and need to move to MySQL database. I want to do the following; If Type = 'S' I want to compare it to the previous rows data and insert a new column in the existing database, let's call the new column 'nc'; If the previous rows type = b & the price of the current column is equal to or below then insert in 'nc' the word 'sell' If the previous rows type = b & the price of the current column is above then insert in 'nc' the word 'buy' If the previous rows type = a & the price of the current column is equal to or above then insert in 'nc' the word 'buy' If the previous rows type = a & the price of the current column is below then insert in 'nc' the word 'sell' Once I get a kick start I'll be ok. I really appreciate the help. [attachment deleted by admin]

Hi, I've only learned about MySQL and PHP this week and have a basic grasp of running an SQL query after reading some tutorials. I have data in a MySQL database and I want to classify each row of data based on meeting certain criteria and to do this I need to be able to refer to the previous rows data. How would I go about doing this? Thank you in advance, suprsnipes

Thanks. I have managed to get a connection to the database. Basically to get the ball rolling I want to query the database to return the value in the price field if the value in the type field is equal to "N". How would I go about this?

Hi, This is my first post on this forum. I have been using PHP for about a week. I recently set up a database in MySQL and I'm trying to learn the basics. I was hoping that a member of this forum could give me a friendly nudge in the 'right' direction. I need to make a connection to MySQL database using PHP. I want to know how to run a basic 'if' statement and display the result on the screen for each row of data that meets the criteria... Is this possible?