natasha_sharma Posted March 16, 2016 Share Posted March 16, 2016 Hi, I am just not able to figure out what is wrong with below code, its just not working: If i run the MYSQL commands indivisually on mYSQL for testing they work fine. Also, if i do a print_r($rows) then there is no output. So looks like the pointer is not even going to the loop. What is the error? if (isset($_POST['fundamentals'])) { if (!isset($_POST['tickers'])) { $queray = "CREATE INDEX st_hist_index ON stock_history (ticker, date_dt, new_record_flag)"; mysql_query($queray); } $query = "select ticker, date_dt, spill_over, net_sales, interest, net_operating_profit, reserves_surp, equity, debt, capex, operating_assets from `fundamentals` order by ticker, date_dt"; $rows = sqlfetch($query); if ($rows != "") { foreach ($rows as $row) { $ticker = $row['ticker']; $date_dt = $row['date_dt']; $spill_over = $row['spill_over']; $net_sales_dis = $row['net_sales']; $interest_dis = $row['interest']; $net_operating_profit_dis = $row['net_operating_profit']; $reserves_surp_dis = $row['reserves_surp']; $equity_dis = $row['equity']; $debt_dis = $row['debt']; $capex_dis = $row['capex']; $operating_assets_dis = $row['operating_assets']; if ($_POST['sex'] == 'new') { $query = "UPDATE stock_history SET spill_over='$spill_over', category='Qtr Result', category_id=1, net_sales_dis = '$net_sales_dis', interest_dis = '$interest_dis', net_operating_profit_dis = '$net_operating_profit_dis', reserves_surp_dis = '$reserves_surp_dis', equity_dis = '$equity_dis', debt_dis = '$debt_dis', capex_dis = '$capex_dis', operating_assets_dis = '$operating_assets_dis' WHERE new_record_flag=1 AND ticker='$ticker' AND date_dt='$date_dt'"; } else { $query = "UPDATE stock_history SET spill_over='$spill_over' ,category='Qtr Result', category_id=1, net_sales_dis = '$net_sales_dis', interest_dis = '$interest_dis', net_operating_profit_dis = '$net_operating_profit_dis', reserves_surp_dis = '$reserves_surp_dis', equity_dis = '$equity_dis', debt_dis = '$debt_dis', capex_dis = '$capex_dis', operating_assets_dis = '$operating_assets_dis' WHERE ticker='$ticker' AND date_dt='$date_dt'"; } mysql_query($query); } } $queray = "ALTER table stock_history DROP index st_hist_index"; mysql_query($queray); } Quote Link to comment Share on other sites More sharing options...
benanamen Posted March 16, 2016 Share Posted March 16, 2016 (edited) You are using obsolete code that has been completely removed from Php. You need to be using PDO with prepared statements. https://phpdelusions.net/pdo * Your adding and dropping indexes is just ridiculous and there is no sqlfetch in Mysql. Edited March 16, 2016 by benanamen Quote Link to comment Share on other sites More sharing options...
natasha_sharma Posted March 16, 2016 Author Share Posted March 16, 2016 You are using obsolete code that has been completely removed from Php. You need to be using PDO with prepared statements. https://phpdelusions.net/pdo * Your adding and dropping indexes is just ridiculous and there is no sqlfetch in Mysql. About the indexes, can you please explain why they seem ridiculous? Quote Link to comment Share on other sites More sharing options...
ginerjm Posted March 16, 2016 Share Posted March 16, 2016 Why would one add an index everytime a script is run? If you WANT an index, then create it by hand and stop worrying about it. As for other problems - turn on php error checking, add your own error checking (like test if your query runs before moving on) and see what pops up. Quote Link to comment Share on other sites More sharing options...
benanamen Posted March 16, 2016 Share Posted March 16, 2016 About the indexes, can you please explain why they seem ridiculous? It's not the index index itself, it is the continual adding and removing of it every time the script runs. Quote Link to comment Share on other sites More sharing options...
natasha_sharma Posted March 16, 2016 Author Share Posted March 16, 2016 The reason why i am adding-removing index on every run is, my master table (table1) is being updated (joined) by table2, table 3, table 4, table5 all in same code with different keys. If i make one time index then i need almost 6-7 variables in index in TABLE1. Also, this code UPDATES TABLE1 and if the index has that many variables then they get refreshed and slowdown the update process. That's my understanding... Quote Link to comment Share on other sites More sharing options...
natasha_sharma Posted March 16, 2016 Author Share Posted March 16, 2016 @ginerjm: Thanks for your tip on checking php error. Need you guidance: 1) I am using XAMPP local server. So, what should i do to see the PHP run error? 2) Also, i saw your signature says error_reporting(E_ALL); ini_set('display_errors', '1'); So, i just need to add at top of my .php script and it will output the error if any? Quote Link to comment Share on other sites More sharing options...
benanamen Posted March 16, 2016 Share Posted March 16, 2016 The reason why i am adding-removing index on every run is, my master table (table1) is being updated (joined) by table2, table 3, table 4, table5 all in same code with different keys. If i make one time index then i need almost 6-7 variables in index in TABLE1. Also, this code UPDATES TABLE1 and if the index has that many variables then they get refreshed and slowdown the update process. That's my understanding... The code you posted has no joins. I suspect you have a bad database design. Post an SQL dump of your DB. Quote Link to comment Share on other sites More sharing options...
natasha_sharma Posted March 16, 2016 Author Share Posted March 16, 2016 Ok so finally I can see what the error message is: Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 2 bytes) in C:\xampp\htdocs\sdbm_1\santo\functions.php on line 0But I have no idea what this error means, how to fix it? I am using XAMPP Quote Link to comment Share on other sites More sharing options...
ginerjm Posted March 17, 2016 Share Posted March 17, 2016 Somewhere in your code you are probably in a loop creating new vars or arrays and trying to allocate too much memory. PS - I don't understand why ANYTHING you do to your data tables would necessitate having to rebuild ANY index. Quote Link to comment Share on other sites More sharing options...
natasha_sharma Posted March 17, 2016 Author Share Posted March 17, 2016 The reason why i am adding-removing index on every run is, my master table (table1) is being updated (joined) by table2, table 3, table 4, table5 all in same code with different keys. If i make one time index then i need almost 6-7 variables in index in TABLE1. Also, this code UPDATES TABLE1 and if the index has that many variables then they get refreshed and slowdown the update process. That's my understanding... Is this wrong understanding? Quote Link to comment Share on other sites More sharing options...
ginerjm Posted March 17, 2016 Share Posted March 17, 2016 If you have established indices that are maintained (and why wouldn't they be?) then there (As I Already Said) is no need to re-create them. When a table is updated, so is its index. Simple Database Knowledge. Quote Link to comment Share on other sites More sharing options...
natasha_sharma Posted March 17, 2016 Author Share Posted March 17, 2016 Correct, Index will get updated, which i am well aware. But my contention is Performance and not index getting updated automatically. Having many indexes on a table will slowdown the UPDATE & INSERT into the table. (if you re-read my posts above, i have explained that my main table is being updated with 4 other tables using different column combinations. Also the main table gets updated with at least 5000 New records every day. So there is UPDATE and INSERT happening everyday. Quote Link to comment Share on other sites More sharing options...
ginerjm Posted March 17, 2016 Share Posted March 17, 2016 You're worried about an insert having to update multiple indices and you have a lot of inserts going on. What about the simple fact that your script wants rebuild THE ENTIRE set of indices multiple times per day? Quote Link to comment Share on other sites More sharing options...
benanamen Posted March 17, 2016 Share Posted March 17, 2016 (edited) main table is being updated with 4 other tables using different column combinations. This smells of a bad DB design which I believe is really your biggest problem that needs to be fixed FIRST. Post your DB schema. You also dont seem to have any concern that you are using obsolete mysql code. Edited March 17, 2016 by benanamen Quote Link to comment Share on other sites More sharing options...
natasha_sharma Posted March 17, 2016 Author Share Posted March 17, 2016 This smells of a bad DB design which I believe is really your biggest problem that needs to be fixed FIRST. Post your DB schema. You also dont seem to have any concern that you are using obsolete mysql code. Okey I will post Schema today. Also, I am not a coder, i hired someone at freelancer. I have basic understanding of DB design, but you guys are expert so I think i wll have to revisit the design now. Quote Link to comment Share on other sites More sharing options...
natasha_sharma Posted March 17, 2016 Author Share Posted March 17, 2016 Bytheway, disabling keys and enabling post update/inset seem sensible solution? ALTER TABLE table_name DISABLE KEYS; BEGIN; ... inserting data with INSERT or LOAD DATA .... COMMIT; ALTER TABLE table_name ENABLE KEYS; Quote Link to comment Share on other sites More sharing options...
benanamen Posted March 17, 2016 Share Posted March 17, 2016 Natasha, lets start at the beginning, your DB design. Post an SQL dump of your DB. Your trying to fix problems from a problem which is a problem. 1 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.