suprsnipes

??? I know this is a stupid question but how do I give it an alias?

This UPDATE statement is a real pain in the butt. SELECT t1.id1,t2.id1 FROM test2 t1 JOIN test2 t2 ON t1.id1 =t2.id1-1 WHERE t2.type1 = 'N' AND t1.type1 = 'a' AND t2.price1 >= t1.price1 Where the above code works but I can't seem to get the UPDATE statement below too. UPDATE (Select t1.id1,t2.id1 FROM test2 t1 JOIN test2 t2 ON t1.id1 =t2.id1-1) SET t2.type1 = buy WHERE t2.type1 = 'N' AND t1.type1 = 'a' AND t2.price1 >= t1.price1 I have also tried SET test2.type1 = buy. In both instances I'm getting the following error: #1248 - Every derived table must have its own alias

I'm trying to update the type 1 column, currently the working SQL is creating a virtual table from the `test2` table and joining itself WHERE type1 is = 'N' and previous (previous id) column's type 1 is equal to 'a'. As you can tell I'm comparing one entries data to the previous entries data using the id column which is set to auto_increment. SELECT * FROM test2 t1 INNER JOIN test2 t2 ON t1.id1 = t2.id1-1 WHERE t2.type1 = 'N' AND t1.type1 = 'a' AND t2.price1 >= t1.price1 What I want to do is when the data meets the above criteria I want the type1 column to be updated in the original table `test2` from 'N' to 'buy'. I can run an UPDATE query on an existing table no problem but I'm uncertain as to how I can do this in conjunction with a JOIN statement like the above where I'm using a Virtual Table.

My apologies, I'm sure it's because I'm a slow learner or that I just don't know what I am doing, or both but I've tried the following and there is an error in the SYNTAX. #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT * FROM test2 t1 INNER JOIN test2 t2 ON t1.id1 = t2.id1-1 WHERE t2.type1 ' at line 1 UPDATE test2 SET type1 = 'buy' (SELECT * FROM test2 t1 INNER JOIN test2 t2 ON t1.id1 = t2.id1-1 WHERE t2.type1 = 'N' AND t1.type1 = 'a' AND t2.price1 >= t1.price1)

Sorry. I want to 'modify' the working SQL SELECT query to an UPDATE query. The table I want to update is named `test2` and the field is named type1, the data to be updated in this case will simply = buy

Hi, I need a little bit of help. I'm want to update the data in field type1 based on the following working query and just can't seem to get it right. SELECT * FROM test2 t1 INNER JOIN test2 t2 ON t1.id1 = t2.id1-1 WHERE t2.type1 = 'N' AND t1.type1 = 'a' AND t2.price1 >= t1.price1 Appreciate the help.

Results as requested...it's only a small sample, there are around 2600 records in the table. 0 <-> 18 ID1 <-> 18 1 <-> A SYMBOL1 <-> A 2 <-> 27/05/2009 2:30:37 PM DATE1 <-> 27/05/2009 2:30:37 PM 3 <-> 1430 TIME1 <-> 1430 4 <-> b TYPE1 <-> b 5 <-> 3824 PRICE1 <-> 3824 6 <-> 8 SIZE1 <-> 8 7 <-> 19 ID <-> 19 8 <-> A SYMBOL <-> A 9 <-> 27/05/2009 2:30:37 PM DATE <-> 27/05/2009 2:30:37 PM 10 <-> 1430 TIME <-> 1430 11 <-> N TYPE <-> N 12 <-> 3825 PRICE <-> 3825 13 <-> 1 SIZE <-> 1 0 <-> 19 ID1 <-> 19 1 <-> A SYMBOL1 <-> A 2 <-> 27/05/2009 2:30:37 PM DATE1 <-> 27/05/2009 2:30:37 PM 3 <-> 1430 TIME1 <-> 1430 4 <-> N TYPE1 <-> N 5 <-> 3825 PRICE1 <-> 3825 6 <-> 1 SIZE1 <-> 1 7 <-> 20 ID <-> 20 8 <-> A SYMBOL <-> A 9 <-> 27/05/2009 2:30:37 PM DATE <-> 27/05/2009 2:30:37 PM 10 <-> 1430 TIME <-> 1430 11 <-> N TYPE <-> N 12 <-> 3825 PRICE <-> 3825 13 <-> 1 SIZE <-> 1 0 <-> 22 ID1 <-> 22 1 <-> A SYMBOL1 <-> A 2 <-> 27/05/2009 2:30:37 PM DATE1 <-> 27/05/2009 2:30:37 PM 3 <-> 1430 TIME1 <-> 1430 4 <-> b TYPE1 <-> b 5 <-> 3824 PRICE1 <-> 3824 6 <-> 10 SIZE1 <-> 10 7 <-> 23 ID <-> 23 8 <-> A SYMBOL <-> A 9 <-> 27/05/2009 2:30:38 PM DATE <-> 27/05/2009 2:30:38 PM 10 <-> 1430 TIME <-> 1430 11 <-> N TYPE <-> N 12 <-> 3825 PRICE <-> 3825 13 <-> 1 SIZE <-> 1 0 <-> 25 ID1 <-> 25 1 <-> A SYMBOL1 <-> A 2 <-> 27/05/2009 2:30:38 PM DATE1 <-> 27/05/2009 2:30:38 PM 3 <-> 1430 TIME1 <-> 1430 4 <-> a TYPE1 <-> a 5 <-> 3825 PRICE1 <-> 3825 6 <-> 3 SIZE1 <-> 3 7 <-> 26 ID <-> 26 8 <-> A SYMBOL <-> A 9 <-> 27/05/2009 2:30:38 PM DATE <-> 27/05/2009 2:30:38 PM 10 <-> 1430 TIME <-> 1430 11 <-> N TYPE <-> N 12 <-> 3825 PRICE <-> 3825 13 <-> 1 SIZE <-> 1

Please help I have been stuck on this for weeks... I can run other queries without too much of a problem, but an if statement I'm finding much much more difficult...I don't know how to go about it, can it even be done? I want to run my SELECT query first and from the result of the query run the if statement. But where do I put the if statement? And how do I have the results displayed on the screen? I'm so frustrated... :'(

I have also tried the following; The only error I get at the moment is as follows; Parse error: parse error in C:\Program Files\EasyPHP 3.0\www\Needs work\Classification_CodeTest1.php on line 31 <?php // DATABASE CONNECTIVITY //----------------------------------------------------------------------------------------------------------- $dbserver = ""; $dbusername = ""; $dbpassword = ""; $dbname ="spi"; $link=mysql_connect ($dbserver,$dbusername,$dbpassword) or die ('DB ERROR -> ' . mysql_error()); mysql_select_db ($dbname); //--------------------------------------------------------------------------------------------------------- $sql = mysql_query("SELECT * FROM `trades2`"); while ($result = mysql_fetch_array($sql)) { switch($result['TYPE']) { case 'trd': if ('TYPE'=='N'){ if ('TYPE1' =='a' && 'PRICE' >= 'PRICE1'){ echo "buy"; break; } else if ('TYPE1' =='b' && 'PRICE' <= 'PRICE1'){ echo "sell"; break; } } } ?> I'm not quite sure how to move forward with this. I've tried to cut it down to the bare minumum and I still can't make any progress. Basically my thought process is I'm trying to run the sql query to get the data first and then I want to run an if statement on the query and display the output of the result to screen. Where am I going wrong? This is the SQL query; SELECT * FROM `trades2` I want to run the following if statements on the data from the above SQL query and have the result output to screen... if ('TYPE'=='N'){ if ('TYPE1' =='a' && 'PRICE' >= 'PRICE1'){ echo "buy"; break; } else if ('TYPE1' =='b' && 'PRICE' <= 'PRICE1'){ echo "sell"; break; } This has been driving me banana's I've been able to do almost everything else I this is one of the last bits of code I need...

How do you mean? I thought both of those statements were treated equally? If not I am mistaken. The table that I have selected (trades2) has data that meets both criteria. All type columns data is equal to 'N' and I'm trying to classify the data with the code as described and I thought the echo would show a result on the screen with a "buy" or "sell" where trades meet the criteria... Am I on the wrong track here?

My apologies to the more experienced members but I am learning slowly but surely. I have noted next to the code below what results I'm trying to echo...I'm getting a blank screen with this code at the moment. <?php // DATABASE CONNECTIVITY //----------------------------------------------------------------------------------------------------------- $dbserver = ""; $dbusername = ""; $dbpassword = ""; $dbname ="spi"; $link=mysql_connect ($dbserver,$dbusername,$dbpassword) or die ('DB ERROR -> ' . mysql_error()); mysql_select_db ($dbname); //--------------------------------------------------------------------------------------------------------- $sql = "SELECT * FROM `trades2`"; $result2 = mysql_db_query($dbname,$sql,$link) or die("Invalid Query<br>". mysql_error()); while ($result = mysql_fetch_array($result2)) { switch($result['TYPE']) { case 'trd': if(strtoupper($result['TYPE'])=='N') { if(strtolower($result['TYPE1']) == 'a' && $result['PRICE'] >= $result['PRICE1']){echo "buy";} if(strtolower($result['TYPE1']) == 'b' && $result['PRICE'] <= $result['PRICE1']){echo "sell";} } } } ?> if(strtoupper($result['TYPE'])=='N') { if(strtolower($result['TYPE1']) == 'a' && $result['PRICE'] >= $result['PRICE1']){echo "buy";} I want to echo this result if(strtolower($result['TYPE1']) == 'b' && $result['PRICE'] <= $result['PRICE1']){echo "sell";} & this result

This was the problem I have attached the updated code The name of the field is `TYPE`. Now it appears that the code works as there are no error messages...what steps would I need to display the results? <?php // DATABASE CONNECTIVITY //----------------------------------------------------------------------------------------------------------- $dbserver = ""; $dbusername = ""; $dbpassword = ""; $dbname ="spi"; $link=mysql_connect ($dbserver,$dbusername,$dbpassword) or die ('DB ERROR -> ' . mysql_error()); mysql_select_db ($dbname); //--------------------------------------------------------------------------------------------------------- $sql = "SELECT * FROM `trades2`"; $result2 = mysql_db_query($dbname,$sql,$link) or die("Invalid Query<br>". mysql_error()); while ($result = mysql_fetch_array($result2)) { switch($result['TYPE']) { case 'trd': if(strtoupper($result['TYPE'])=='N') { if(strtolower($result['TYPE1']) == 'a' && $result['PRICE'] >= $result['PRICE1']){echo "buy";} if(strtolower($result['TYPE1']) == 'b' && $result['PRICE'] <= $result['PRICE1']){echo "sell";} } } } ?>

Ok, I've updated the code but I still get this error message; Notice: Undefined index: trade in C:\......\CodeTest.php on line 18 <?php // DATABASE CONNECTIVITY //----------------------------------------------------------------------------------------------------------- $dbserver = ""; $dbusername = ""; $dbpassword = ""; $dbname ="spi"; $link=mysql_connect ($dbserver,$dbusername,$dbpassword) or die ('DB ERROR -> ' . mysql_error()); mysql_select_db ($dbname); //--------------------------------------------------------------------------------------------------------- ?> <?php $sql = "SELECT * FROM `trades2`"; $result2 = mysql_db_query($dbname,$sql,$link) or die("Invalid Query<br>". mysql_error()); while ($result = mysql_fetch_array($result2)) { switch($result['trade']) { case 'trd': if(strtoupper($result['type'])=='N') { if(strtolower($result['type1']) == 'a' && $result['price'] >= $result['price1']){echo "Buy";} if(strtolower($result['type1']) == 'b' && $result['price'] <= $result['price1']){echo "Sell";} } } } ?>

Does anyone know what thorpe means by the following with regards to my code?

Excuse my ignorance thorpe I'm sure it's my lack of knowledge but I'm not exactly sure what to do from here... ???

Ok, now I'm only getting the one Notice: Undefined index: trade in \www\Classification_CodeTest.php on line 18

Hi dawsba, Just run the suggested code and I'm getting the following error message; Notice: Use of undefined constant trade - assumed 'trade' in www\Classification_CodeTest.php on line 18 Notice: Undefined index: trade in \www\Classification_CodeTest.php on line 18 Thanks for the reply by the way.

Could someone be so kind as to help me get this bit of code working? My PHP experience is only a couple of weeks old. <?php // Connect to MySQL $con = mysql_connect("","",""); if (!$con) { die('Could not connect: ' . mysql_error()); } // Select database mysql_select_db("spi", $con); // query 1 $sql="SELECT * FROM trades2"; if ($result=mysql_query($sql)) { // Classification of trades switch($result) { case 'trd': if ($type == "N") { if ($type1 == "a" and $price >= $price1) { echo 'buy'; } else if ($type1 == "b" and $price <= $price1) { echo 'sell'; } else { echo ''; } ?>

The query joins the tables with the intention of referring to the previous ID's data using an IF statement. Using the IF statement I want to classify the trades (N) as a buy or a sell, but I'm not sure where I place this in my PHP code...I'm also uncertain as to whether or not I update the data in the database or I do this in some other manner as I have read that you should not put calculated values into a database...

This is what I want to do. This code should be ok, a friend has helped me with it, I've just updated it now best I could for the moment. Should make things a little clearer. action == "buy" or "sell", not sure what to do with this. At this stage I don't know if I should use the below if/else statement to UPDATE the type1 column in the database or if there is another option as I have read that it's not ideal to put calculated values into a database... <?php // Classification of trades if (type == "N") { if (type1 != null) { if (type1 == "buy") { if (price >= price1) { action == "buy"; } else { action == "sell"; } } else if (type1 == "sell") { if (price <= price1) { action == "sell"; } else { action == "buy"; } } } else { if (type1 == "a") { if (price >= price1) { action == "buy"; } else { action == "sell"; } } if (type1 == "b") { if (price > price1) { action == "buy"; } else { action == "sell"; } } } } ?>

I'm really struggling and need some help with PHP code. I have created a connection to the database and can run various queries. As you can see from the basic code below I can connect and display the result of a query but I would like to run an if/else statement on the data prior to echoing (the layout would be the same) and would like to know how to go about this, perhaps an example of some kind would help. <?php $con = mysql_connect("","",""); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("day", $con); $result = mysql_query("SELECT * FROM test INNER JOIN trades ON test.id1=trades.id-1"); echo "<table border='1'> <tr> <th>ID1</th> <th>SYMBOL1</th> <th>DATE1</th> <th>TIME1</th> <th>TYPE1</th> <th>PRICE1</th> <th>SIZE1</th> <th>ID</th> <th>SYMBOL</th> <th>DATE</th> <th>TIME</th> <th>TYPE</th> <th>PRICE</th> <th>SIZE</th> </tr>"; while($row = mysql_fetch_array($result)) { echo "<tr>"; echo "<td>" . $row['ID1'] . "</td>"; echo "<td>" . $row['SYMBOL1'] . "</td>"; echo "<td>" . $row['DATE1'] . "</td>"; echo "<td>" . $row['TIME1'] . "</td>"; echo "<td>" . $row['TYPE1'] . "</td>"; echo "<td>" . $row['PRICE1'] . "</td>"; echo "<td>" . $row['SIZE1'] . "</td>"; echo "<td>" . $row['ID'] . "</td>"; echo "<td>" . $row['SYMBOL'] . "</td>"; echo "<td>" . $row['DATE'] . "</td>"; echo "<td>" . $row['TIME'] . "</td>"; echo "<td>" . $row['TYPE'] . "</td>"; echo "<td>" . $row['PRICE'] . "</td>"; echo "<td>" . $row['SIZE'] . "</td>"; echo "</tr>"; } echo "</table>"; mysql_close($con); ?> Regards, suprsnipes

Yes Mark you have. So are you saying that I can do what I want to do by using PHPExcel? If you think I can I will look into it now. Please respond so I know...

Anyone willing to offer advice on how I could translate an Excel formula to PHP. I want to achieve the same outcome the formula below would produce but on data that is stored in a MySQL database with auto_increment ID. The Excel formula in the classification column is designed to do the following; When there is a trade(N) event it classifies the trade according to several conditions and will either use the previous bid(b) or ask(a) quote in conjunction with the current trade price in relation to the previous bid or ask price. OR/ If the previous trade has already been classified as a "Buy" or "Sell" (in the case of consecutive trades(N)), whereas there will be no bid or ask price to refer to. In this case it will use the previous Buy or previous Sell classification in relation to the current trade price v's the previous trade price. =IF(AND(D2="N",D1="a",E2>=E1),"Buy",IF(AND(D2="N",D1="b",E2<=E1),"Sell",IF(AND(D2="N",D1="a",E2<E1),"Sell",IF(AND(D2="N",D1="b",E2>E1),"Buy",IF(AND(D2="N",G1="Buy",E2>=E1),"Buy",IF(AND(D2="N",G1="Sell",E2<=E1),"Sell",IF(AND(D2="N",G1="Buy",E2<E1),"Sell","")))))))&IF(AND(D2="N",G1="Sell",E2>E1),"Buy","") I can post a data sample if someone wishes. A friend of mine come up with this, I know that it still refers to 'previous' but that is the problem. I don't know how to do that besides using an SQL INNER JOIN but that only solves part of the problem, for example somehow I need to use the results from the initial INNER JOIN, update the trades(N) to a "buy" or "sell" and then run the query again and again, which is something I don't know how to do... if (type == "N") { > > If (previous_classification != null) { > > if (previous_classification == "buy") { > > if (price >= previous_price) { > action == "buy"; > } > else { > action == "sell"; > } > } > else if (previous_classification == "sell") { > > if (price <= previous_price) { > action == "sell"; > } > else { > action == "buy"; > } > } > } > else { > > if (previous_type == "a") { > > if (price >= previous_price) { > action == "buy"; > } > else { > action == "sell"; > } > } > if (previous_type == "b") { > > if (price > previous_price) { > action == "buy"; > } > else { > action == "sell"; > } > } > } > } >

In saying that do you have any suggestions as to how I could go about this?