update in Mysql table, PHP code not working

[natasha_sharma]

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);	
					
    }

[benanamen]

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

[natasha_sharma]

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? 

[ginerjm]

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.

[benanamen]

 

 

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.

[natasha_sharma]

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...

[natasha_sharma]

@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?

[benanamen]

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.

[natasha_sharma]

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 0

But I have no idea what this error means, how to fix it?

 

I am using XAMPP

[ginerjm]

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.

[natasha_sharma]

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?

[ginerjm]

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.

[natasha_sharma]

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.

[ginerjm]

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?

[benanamen]

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

[natasha_sharma]

 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.

[natasha_sharma]

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;

[benanamen]

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.