Browser hanging with mysql query.

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
mattbranch12
Forum Newbie
Posts: 18
Joined: Thu Jul 09, 2009 3:14 pm
Location: Hagerstown, MD

Browser hanging with mysql query.

Post by mattbranch12 »

I am hoping some one else has ran into this issue. I have searched for hours and have not come up with a solution.
I have created a script that load and parses a txt file. The script works fine if run it from the local computer/server, but if i run the script for client machine the browser hangs.

Code: Select all

<?php 
include 'connect.php';

if ($_FILES["file"]["error"] > 0) 
{
  echo "Error: " . $_FILES["file"]["error"] . "<br />";
}
elseif($_FILES["file"]["name"]=="DATATOGO.txt" or $_FILES["file"]["name"]=="DATATOGO.TXT")
{
	echo 'Lanco Lab File';
	$fileopen = $_FILES["file"]["tmp_name"];
					$row = 1;
					if (($handle = fopen($fileopen, "r")) !== FALSE)
					{
       						$row++;
			 while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
       		 $num = count($data);
        		$row++;
					//sorts Dates into YYYY/MM/DD
		$new_dateorig2 = date("Y-m-d", strtotime($data[2] ));
		$new_dateorig3 = date("Y-m-d", strtotime($data[3] ));
		
							//changes Co-Op number in CSV to proper number
						if($data[13]==1)
						{
							$data[13]=100000;
							$sql="SELECT * FROM lanco_lab_quality_orig WHERE farm_id='$data[0]' AND pu_date='$new_dateorig2' AND tank_number='$data[1]' AND source='$data[17]'";
							$result=mysql_query($sql);
							$count=mysql_num_rows($result);

			
							
					if($count>=1)
								{
							$sql="UPDATE lanco_lab_quality_orig SET farm_id='$data[0]', tank_number='$data[1]', pu_date='$new_dateorig2', plate_date='$new_dateorig3', butterfat='$data[4]', protein='$data[5]', other_solids='$data[6]', scc='$data[7]', antibiotic='$data[8]', cryo='$data[9]', pi='$data[10]', temp='$data[11]', spc='$data[12]', coop_num='$data[13]', start_date='$data[14]', plate_loop='$data[15]', mun='$data[16]', status='new', source='$data[17]' WHERE farm_id='$data[0]' AND pu_date='$new_dateorig2' AND tank_number='$data[1]' AND source='$data[17]'; UPDATE lanco_lab_quality SET farm_id='$data[0]', tank_number='$data[1]', pu_date='$new_dateorig2', plate_date='$new_dateorig3', butterfat='$data[4]', protein='$data[5]', other_solids='$data[6]', scc='$data[7]', antibiotic='$data[8]', cryo='$data[9]', pi='$data[10]', temp='$data[11]', spc='$data[12]', coop_num='$data[13]', start_date='$data[14]', plate_loop='$data[15]', mun='$data[16]', status='new', source='$data[17]' WHERE farm_id='$data[0]' AND pu_date='$new_dateorig2' AND tank_number='$data[1]' AND source='$data[17]';";			
								}
								
							elseif($count==0)
								{
						$sql="INSERT into lanco_lab_quality_orig (farm_id, tank_number, pu_date, plate_date, butterfat, protein, other_solids, scc, antibiotic, cryo, pi, temp, spc, coop_num, start_date, plate_loop, mun, status, source) values('$data[0]','$data[1]','$new_dateorig2','$new_dateorig3','$data[4]','$data[5]','$data[6]','$data[7]','$data[8]','$data[9]','$data[10]','$data[11]','$data[12]','$data[13]','$data[14]','$data[15]','$data[16]', 'new', '$data[17]'); INSERT into lanco_lab_quality (farm_id, tank_number, pu_date, plate_date, butterfat, protein, other_solids, scc, antibiotic, cryo, pi, temp, spc, coop_num, start_date, plate_loop, mun, status, source) values('$data[0]','$data[1]','$new_dateorig2','$new_dateorig3','$data[4]','$data[5]','$data[6]','$data[7]','$data[8]','$data[9]','$data[10]','$data[11]','$data[12]','$data[13]','$data[14]','$data[15]','$data[16]', 'new', '$data[17]');";
								}
								else
								{
								echo $count;
								}							
								
								$queries = preg_split("/;+(?=([^'|^\\\']*['|\\\'][^'|^\\\']*['|\\\'])*[^'|^\\\']*[^'|^\\\']$)/", $sql);
							foreach ($queries as $query){
   							if (strlen(trim($query)) > 0) mysql_query($query);
								} 
						}
					}
					
				}
	
	
}
elseif($_FILES["file"]["name"]=="DATATOGOPIONLY.txt" or $_FILES["file"]["name"]=="DATATOGOPIONLY.TXT")
{
echo 'lanco PI File';
	$fileopen = $_FILES["file"]["tmp_name"];
					$row = 1;
					if (($handle = fopen($fileopen, "r")) !== FALSE)
					{
       						$row++;
			 while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
       		 $num = count($data);
        		$row++;
					//sorts Dates into YYYY/MM/DD
		$new_dateorig2 = date("Y-m-d", strtotime($data[2] ));
		
		
							$sql="SELECT * FROM lanco_lab_quality WHERE farm_id='$data[0]' AND pu_date='$new_dateorig2' AND tank_number='$data[1]' AND source='$data[4]'";
							$result=mysql_query($sql);
							$count=mysql_num_rows($result);
							
							if($count>=1)
								{
							
								$import="UPDATE lanco_lab_quality SET pi='$data[3]', status='new' WHERE farm_id='$data[0]' AND pu_date='$new_dateorig2' AND tank_number='$data[1]' AND source='$data[4]'";
								
								$import1="UPDATE lanco_lab_quality_orig SET pi='$data[3]', status='new' WHERE farm_id='$data[0]' AND pu_date='$new_dateorig2' AND tank_number='$data[1]' AND source='$data[4]'";
								}
							elseif($count==0)
								{
								
								$import="INSERT into lanco_lab_quality (pi) values('$data[3]')";
								$import1="INSERT into lanco_lab_quality_orig (pi) values('$data[3]')";
								}
								else{echo $count;}
								mysql_query($import) or die(mysql_error());
								mysql_query($import1) or die(mysql_error());
						
					}

					}

}
elseif($_FILES["file"]["name"]=="DATATOGOMUNtxt" or $_FILES["file"]["name"]=="DATATOGOMUN.TXT")
{
	echo 'lanco MUN File';
}
else
{
	echo 'This is not a lanco File.';
}


?>
User avatar
axcrow
Forum Newbie
Posts: 9
Joined: Mon Aug 09, 2010 12:22 pm

Re: Browser hanging with mysql query.

Post by axcrow »

the browser hangs = becomes unresponsive? or just gives a blank page?
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: Browser hanging with mysql query.

Post by JakeJ »

It could be a permissions error.

Also, is any data making it in to the database?

I doubt the query is causing your browser to hang, the query itself is just taking a long time for some reason so it doesn't return control of the browser back to you.
buckit
Forum Contributor
Posts: 169
Joined: Fri Jan 01, 2010 10:21 am

Re: Browser hanging with mysql query.

Post by buckit »

99% of the time that happens to me its because I have an endless loop... but thats me :)

the only part that really buggers me is this (mind you I didnt look too long at this)

Code: Select all

$queries = preg_split("/;+(?=([^'|^\\\']*['|\\\'][^'|^\\\']*['|\\\'])*[^'|^\\\']*[^'|^\\\']$)/", $sql);
	              	foreach ($queries as $query){
	                	if (strlen(trim($query)) > 0) mysql_query($query);
	                } 
and you also seem to be missing a } on one of your elseif statements in your second while.
mattbranch12
Forum Newbie
Posts: 18
Joined: Thu Jul 09, 2009 3:14 pm
Location: Hagerstown, MD

Re: Browser hanging with mysql query.

Post by mattbranch12 »

Thanks everyone for your responses.

To axcrow:
Its leaving a blank page and down at the bottom of the browser either IE or Firefox its stating that its waiting for the server, and keeps running.

To JakeJ:
The data is making it to the database. Using phpmyadmin I am able to watch all the processes go through. Each query takes one second it seems running it from a client machine. Some of them take two but I haven't notice any of them taking any longer. Though its weird that if I run the script on the machine locally it runs fine and the queries are done instantly and it returns back to the browser this is in both Windows and Linux environments.

To buckit:
The while loop seems to be fine, Thank you for looking through the script. I've been searching through other forums and people come up with have endless loops, but all the queries.
mattbranch12
Forum Newbie
Posts: 18
Joined: Thu Jul 09, 2009 3:14 pm
Location: Hagerstown, MD

Re: Browser hanging with mysql query.

Post by mattbranch12 »

I have got the script working but it takes a long time to run the queries. Takes about three to four minutes parsing the the file to the database (each query takes about 2 secs), once again if I run it locally on either on a Linux or Windows environment the queries are done instantly. Hoping one of you guys can explain to me why it takes so long when I run the script from a client machine, if it can be explain to me why this is happening maybe I can figure out a way to speed up the script. NOTE: that I still have to finish each if statement for the other files that will uploaded and parsed to the server.

Here is the code rewritten:

Code: Select all

<?php 
include 'connect.php';

if ($_FILES["file"]["error"] > 0) 
{
  echo "Error: " . $_FILES["file"]["error"] . "<br />";
}
elseif($_FILES["file"]["name"]=="DATATOGO.txt" or $_FILES["file"]["name"]=="DATATOGO.TXT")
{
	echo 'Lanco Lab File';
	$fileopen = $_FILES["file"]["tmp_name"];
					$row = 1;
					if (($handle = fopen($fileopen, "r")) !== FALSE)
					{
       						$row++;
			 while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
       		 $num = count($data);
        		$row++;
					//sorts Dates into YYYY/MM/DD
		$new_dateorig2 = date("Y-m-d", strtotime($data[2] ));
		$new_dateorig3 = date("Y-m-d", strtotime($data[3] ));
		
							//changes Co-Op number in CSV to proper number
						if($data[13]==1)
						{
							$data[13]=100000;
							$sql="SELECT * FROM lanco_lab_quality_orig WHERE farm_id='$data[0]' AND pu_date='$new_dateorig2' AND tank_number='$data[1]' AND source='$data[17]'";
							$result=mysql_query($sql);
							$count=mysql_num_rows($result);

			
							
					if($count>=1)
								{
							echo "<br>";
							echo "Updated Farm ". $data[0] ." on table lanco_lab_quality_orig and lanco_lab_quality";
							
							$sql="UPDATE lanco_lab_quality_orig SET farm_id='$data[0]', tank_number='$data[1]', pu_date='$new_dateorig2', plate_date='$new_dateorig3', butterfat='$data[4]', protein='$data[5]', other_solids='$data[6]', scc='$data[7]', antibiotic='$data[8]', cryo='$data[9]', pi='$data[10]', temp='$data[11]', spc='$data[12]', coop_num='$data[13]', start_date='$data[14]', plate_loop='$data[15]', mun='$data[16]', status='new', source='$data[17]' WHERE farm_id='$data[0]' AND pu_date='$new_dateorig2' AND tank_number='$data[1]' AND source='$data[17]';";
							
							$sqls="UPDATE lanco_lab_quality SET farm_id='$data[0]', tank_number='$data[1]', pu_date='$new_dateorig2', plate_date='$new_dateorig3', butterfat='$data[4]', protein='$data[5]', other_solids='$data[6]', scc='$data[7]', antibiotic='$data[8]', cryo='$data[9]', pi='$data[10]', temp='$data[11]', spc='$data[12]', coop_num='$data[13]', start_date='$data[14]', plate_loop='$data[15]', mun='$data[16]', status='new', source='$data[17]' WHERE farm_id='$data[0]' AND pu_date='$new_dateorig2' AND tank_number='$data[1]' AND source='$data[17]';";			
								}
								
							elseif($count==0)
								{							
							echo "<br>";
							echo "Inserted Farm ". $data[0] ." in table lanco_lab_quality_orig and lanco_lab_quality";
						$sql="INSERT into lanco_lab_quality_orig (farm_id, tank_number, pu_date, plate_date, butterfat, protein, other_solids, scc, antibiotic, cryo, pi, temp, spc, coop_num, start_date, plate_loop, mun, status, source) values('$data[0]','$data[1]','$new_dateorig2','$new_dateorig3','$data[4]','$data[5]','$data[6]','$data[7]','$data[8]','$data[9]','$data[10]','$data[11]','$data[12]','$data[13]','$data[14]','$data[15]','$data[16]', 'new', '$data[17]');";
						
						$sqls="INSERT into lanco_lab_quality (farm_id, tank_number, pu_date, plate_date, butterfat, protein, other_solids, scc, antibiotic, cryo, pi, temp, spc, coop_num, start_date, plate_loop, mun, status, source) values('$data[0]','$data[1]','$new_dateorig2','$new_dateorig3','$data[4]','$data[5]','$data[6]','$data[7]','$data[8]','$data[9]','$data[10]','$data[11]','$data[12]','$data[13]','$data[14]','$data[15]','$data[16]', 'new', '$data[17]');";
								}
								else
								{
								echo $count;
								}							
								
								$queries = preg_split("/;+(?=([^'|^\\\']*['|\\\'][^'|^\\\']*['|\\\'])*[^'|^\\\']*[^'|^\\\']$)/", $sql);
							foreach ($queries as $query){
   							if (strlen(trim($query)) > 0) mysql_query($query);
								} 
								
								$queriess = preg_split("/;+(?=([^'|^\\\']*['|\\\'][^'|^\\\']*['|\\\'])*[^'|^\\\']*[^'|^\\\']$)/", $sqls);
							foreach ($queriess as $querys){
   							if (strlen(trim($querys)) > 0) mysql_query($querys);
								} 
						}
					}
					
				}
	
	
}
elseif($_FILES["file"]["name"]=="DATATOGOPIONLY.txt" or $_FILES["file"]["name"]=="DATATOGOPIONLY.TXT")
{
echo 'lanco PI File';
	$fileopen = $_FILES["file"]["tmp_name"];
					$row = 1;
					if (($handle = fopen($fileopen, "r")) !== FALSE)
					{
       						$row++;
			 while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
       		 $num = count($data);
        		$row++;
					//sorts Dates into YYYY/MM/DD
		$new_dateorig2 = date("Y-m-d", strtotime($data[2] ));
		
		
							$sql="SELECT * FROM lanco_lab_quality WHERE farm_id='$data[0]' AND pu_date='$new_dateorig2' AND tank_number='$data[1]' AND source='$data[4]'";
							$result=mysql_query($sql);
							$count=mysql_num_rows($result);
							
							if($count>=1)
								{
							
								$import="UPDATE lanco_lab_quality SET pi='$data[3]', status='new' WHERE farm_id='$data[0]' AND pu_date='$new_dateorig2' AND tank_number='$data[1]' AND source='$data[4]'";
								
								$import1="UPDATE lanco_lab_quality_orig SET pi='$data[3]', status='new' WHERE farm_id='$data[0]' AND pu_date='$new_dateorig2' AND tank_number='$data[1]' AND source='$data[4]'";
								}
							elseif($count==0)
								{
								
								$import="INSERT into lanco_lab_quality (pi) values('$data[3]')";
								$import1="INSERT into lanco_lab_quality_orig (pi) values('$data[3]')";
								}
								else{echo $count;}
								mysql_query($import) or die(mysql_error());
								mysql_query($import1) or die(mysql_error());
						
					}

					}

}
elseif($_FILES["file"]["name"]=="DATATOGOMUN.txt" or $_FILES["file"]["name"]=="DATATOGOMUN.TXT")
{
	echo 'lanco MUN File';
}
else
{
	echo 'This is not a lanco File.';
}


?>
User avatar
axcrow
Forum Newbie
Posts: 9
Joined: Mon Aug 09, 2010 12:22 pm

Re: Browser hanging with mysql query.

Post by axcrow »

The script works fine if run it from the local computer/server, but if i run the script for client machine the browser hangs.
Hm, you mean that the client machine - actually executes the script which connects to a remote db server? If so - that is a different issue =) .. But if the script and the db are on the same machine/network the only bottleneck that can be here is the uploading part, and it doesnt really make sense - since the file you have in $_FILES is already uploaded and resides in some temp folder, which you read or open like any other file in the filesystem.. :banghead:
mattbranch12
Forum Newbie
Posts: 18
Joined: Thu Jul 09, 2009 3:14 pm
Location: Hagerstown, MD

Re: Browser hanging with mysql query.

Post by mattbranch12 »

But if the script and the db are on the same machine/network the only bottleneck that can be here is the uploading part, and it doesnt really make sense - since the file you have in $_FILES is already uploaded and resides in some temp folder, which you read or open like any other file in the filesystem..
Axcrow: As noted to my previous post everything is working but when I have the use a browser on a client machine the script for some reason runs extremely slow. A query taking 2 secs is way to long. But if open a browser on the server it self the script runs fine. The server isn't located on our network. This is being done over the internet. But doesn't make much since. We have a T1 line here which is more than capable of sending a small text file. Have any other ideas where the bottle neck may be.

I may try to run the script on my home server which is running Debian squeeze. There server that is running the actual web page is being hosted by one and one, which we are leasing a virtual private server from them. Should have enough power to run the script we have a 2 core processor, and 6 gigs or ram and enough hard drive space to run us a life time. I'll just set here and :banghead: against the wall until figure this out.
mattbranch12
Forum Newbie
Posts: 18
Joined: Thu Jul 09, 2009 3:14 pm
Location: Hagerstown, MD

Re: Browser hanging with mysql query.

Post by mattbranch12 »

I uploaded my PHP script to another Linux box, that was outside of our network. The script ran beautifully. This narrows it down that there is something wrong with our development box. Does anyone have any ideas?
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: Browser hanging with mysql query.

Post by JakeJ »

Difficult to say...

It only happens when you run it from a browser NOT on that box?

Rather than writing to the database, echo your info out to the browser. If that goes fast, then the problem is your database. But, just to be thorough, try writing to the screen and the database both.

When writing to the screen, make sure you have some sort of visible incrementing going on so you can see how many records are being output.
mattbranch12
Forum Newbie
Posts: 18
Joined: Thu Jul 09, 2009 3:14 pm
Location: Hagerstown, MD

Re: Browser hanging with mysql query.

Post by mattbranch12 »

Ok, I have been changing a lot of the code so that it will count how many records have been modified. So once I get that done I'll run the script with just echoing the information back to the screen. I really hope its not the database though I don't see how it would be as the second box that I have ran it one used a backup of the development servers database.
Post Reply