PHP + MySQL + Large Text File = Unknown result.
Moderator: General Moderators
-
Benwahballz
- Forum Commoner
- Posts: 25
- Joined: Mon Sep 21, 2009 12:54 pm
PHP + MySQL + Large Text File = Unknown result.
I am currently trying to read a number of files that are appx 35-40MB using fgets. for each line of each file, I am doing some checking against data that I have in a database (MySQL), and if the data doesnt already exist, then it adds the information to the database. Each line has between 7-11 queries sent to the database ( Between Select statements to see if the data exists, and then an insert if it doesnt exist, then another select to get the auto increment number that was created by the insert)
The problem I am having is that after appx 175000 lines of data from the first file, the browser seems to just stop, as well as any data processing or anything else that happens.
Any suggestions on what to do?
TIA
The problem I am having is that after appx 175000 lines of data from the first file, the browser seems to just stop, as well as any data processing or anything else that happens.
Any suggestions on what to do?
TIA
Re: PHP + MySQL + Large Text File = Unknown result.
Sounds like you should try running this from the command line. There's probably a timeout on your browser, apache, or both. PHP has a timeout as well....
So yeah, you could try running from the CLI. Or maybe writing it in something like C, which would probably be a lot more efficient. If so, you can look into the fstream class (C++) and the MySQL API. It'd probably end up quite similar to whatever code you have written in PHP.
So yeah, you could try running from the CLI. Or maybe writing it in something like C, which would probably be a lot more efficient. If so, you can look into the fstream class (C++) and the MySQL API. It'd probably end up quite similar to whatever code you have written in PHP.
-
Benwahballz
- Forum Commoner
- Posts: 25
- Joined: Mon Sep 21, 2009 12:54 pm
Re: PHP + MySQL + Large Text File = Unknown result.
PHP from command line sounds like it might not be a bad idea, although I have never used php from a command line.
The reason I am doing it in php is because I am populating a database with appx 2.5million rows (spread between 4 tables). The database is stored on our webserver which is not local to me. The webserver will be using these tables to display information to the users. Otherwise populating the database with another language would be a much better solution, I know.
FYI: I have also played around with things like:
ini_set("memory_limit","xxxM");
ignore_user_abort(1);
set_time_limit(xxx);
None of these seem to really change the outcome all that much. It seems to stop and random locations as well. Sometimes it will do 20000 lines from the read file, other times it can do as many as 200000 lines from the read file.
The reason I am doing it in php is because I am populating a database with appx 2.5million rows (spread between 4 tables). The database is stored on our webserver which is not local to me. The webserver will be using these tables to display information to the users. Otherwise populating the database with another language would be a much better solution, I know.
FYI: I have also played around with things like:
ini_set("memory_limit","xxxM");
ignore_user_abort(1);
set_time_limit(xxx);
None of these seem to really change the outcome all that much. It seems to stop and random locations as well. Sometimes it will do 20000 lines from the read file, other times it can do as many as 200000 lines from the read file.
Re: PHP + MySQL + Large Text File = Unknown result.
post your code, and we'll have a look
-
Benwahballz
- Forum Commoner
- Posts: 25
- Joined: Mon Sep 21, 2009 12:54 pm
Re: PHP + MySQL + Large Text File = Unknown result.
Here is the code:
And the result is as explained above. Random stoppages (is that a word?)
Code: Select all
<?php
.....
dbConnect();
if( $file = fopen($fileName, "r") )
{
while( !feof($file) )
{
$line = fgets($file, 512);
if( $line != "" )
{
list($modelName,$year,$modelNum,$assyDesc,$partNum,$qtyPer,
$posInImage,$imageName,$partName,$refNum,$note1,$flags,
$note2,$note3,$note4,$note5) = explode("\t",$line);
/***** MODEL *****/
$category = getCategory($modelName, $modelNum); //simple function to get category
$getModelQuery = "SELECT `pkModelID` FROM `Model`
WHERE `sModelName` = '" . mysql_real_escape_string($modelName) . "'
AND `sModelNum` = '" . mysql_real_escape_string($modelNum) . "' LIMIT 1";
if($result = mysql_query($getModelQuery))
{
if( mysql_num_rows($result) == 0 )
{
$addModelQuery = "INSERT INTO `Model` (`sModelCategory`,`dModelYear`,`sModelName`,`sModelNum`)
VALUES('" . mysql_real_escape_string($category) . "',
" . mysql_real_escape_string($year) . ",
'" . mysql_real_escape_string($modelName) . "',
'" . mysql_real_escape_string($modelNum) . "')";
$result = mysql_query($addModelQuery);
}
$result = mysql_query($getModelQuery);
}
$info = mysql_fetch_assoc($result);
$currModelID = $info['pkModelID'];
/***** Assembly *****/
$getAssyQuery = "SELECT `pkAssyID` FROM `Assy`
WHERE `sImageName` = '" . mysql_real_escape_string($imageName) . "'
AND `sAssyDesc` = '" . mysql_real_escape_string($assyDesc) . "' LIMIT 1";
if( $result = mysql_query($getAssyQuery) )
{
if( mysql_num_rows($result) == 0 )
{
$addAssyQuery = "INSERT INTO `Assy` (`sImageName`, `sAssyDesc`)
VALUES('" . mysql_real_escape_string($imageName) . "',
'" . mysql_real_escape_string($assyDesc) . "')";
$result = mysql_query($addAssyQuery);
}
$result = mysql_query($getAssyQuery);
}
$info = mysql_fetch_assoc($result);
$currAssyID = $info['pkAssyID'];
/***** Model/Assy *****/
$getModelAssyQuery = "SELECT `pkModelAssy` FROM `ModelAssy`
WHERE `fkModelID` = $currModelID
AND `fkAssyID` = $currAssyID LIMIT 1";
if( $result = mysql_query($getModelAssyQuery) )
{
if( mysql_num_rows($result) == 0 )
{
$addModelAssyQuery = "INSERT INTO `ModelAssy` (`fkModelID`, `fkAssyID`)
VALUES($currModelID, $currAssyID)";
$result = mysql_query($addModelAssyQuery);
}
$result = mysql_query($getModelAssyQuery);
}
/***** PartAsy *****/
$getPartAssyQuery = "SELECT `pkPartAssyID` FROM `PartAssy`
WHERE `sPartCode` = '" . mysql_real_escape_string($partNum) . "'
AND `fkAssyID` = $currAssyID
AND `partImagePos` = '" . mysql_real_escape_string($posInImage) ."'
AND `sDesc` = '" . mysql_real_escape_string($partName) . "'
AND `sNote` = '" . mysql_real_escape_string($note2) . "' LIMIT 1";
if( $result = mysql_query($getPartAssyQuery) )
{
if( mysql_num_rows($result) == 0 )
{
$addPartAssy = "INSERT INTO `PartAssy` (`sPartCode`, `fkAssyID`, `partImagePos`, `sDesc`, `sNote`)
VALUES('" . mysql_real_escape_string($partNum) . "',
'" . mysql_real_escape_string($currAssyID) . "',
'" . mysql_real_escape_string($posInImage) . "',
'" . mysql_real_escape_string($partName) . "',
'" . mysql_real_escape_string($note2) . "')";
$result = mysql_query($addPartAssy);
}
}
}
}
}
mysql_close();
?>
Last edited by Benwahballz on Mon Sep 21, 2009 3:25 pm, edited 1 time in total.
-
Benwahballz
- Forum Commoner
- Posts: 25
- Joined: Mon Sep 21, 2009 12:54 pm
Re: PHP + MySQL + Large Text File = Unknown result.
I may try make this into a perl scrip as well to load the data.
Re: PHP + MySQL + Large Text File = Unknown result.
learn to indent 'lol.
-
Benwahballz
- Forum Commoner
- Posts: 25
- Joined: Mon Sep 21, 2009 12:54 pm
Re: PHP + MySQL + Large Text File = Unknown result.
jackpf wrote:![]()
![]()
![]()
![]()
learn to indent 'lol.
Ya sorry for that, when I pasted the code here there was no indentation. I hate not having it indented either. I may spend the time and try indent it manually
Re: PHP + MySQL + Large Text File = Unknown result.
You know notepad++ (probably most IDEs as well) allows you to indent large blocks of code...if you highlight the lot and press tab. Just in case you didn't know 
Anyway,

I'm not entirely sure why your code is failing if you've set them ini values. Have you tried changing them in php.ini itself?
Also, have you tried the CLI yet?
Anyway,
I'm not entirely sure why your code is failing if you've set them ini values. Have you tried changing them in php.ini itself?
Also, have you tried the CLI yet?
Re: PHP + MySQL + Large Text File = Unknown result.
+1 for CLI... much better suited for this type of script.
-
Benwahballz
- Forum Commoner
- Posts: 25
- Joined: Mon Sep 21, 2009 12:54 pm
Re: PHP + MySQL + Large Text File = Unknown result.
There I changed the tabbing on the code, much easier to see. It was just the editor that I pasted from that caused the issue as it replaces tabs with spaces (hapedit). I have notepad++ which I pasted from now and it looks much better.
It doesnt appear that I can run php from CLI.....as far as I can tell at least....which sucks.
It doesnt appear that I can run php from CLI.....as far as I can tell at least....which sucks.
Re: PHP + MySQL + Large Text File = Unknown result.
You should be able to do it with shell_exec() (or backticks `). Unless...you're host doesn't allow you to execute shell commands?
I think most hosts do :/
I think most hosts do :/
-
Benwahballz
- Forum Commoner
- Posts: 25
- Joined: Mon Sep 21, 2009 12:54 pm
Re: PHP + MySQL + Large Text File = Unknown result.
So, are you suggesting that I send the mysql commands via the shell_exec (or backticks) rather than directly from php? doesnt PHP still need to read the entire file though?
Ive never had to do anything with PHP via CLI or shell commands, so sorry for sounding a dumb

Ive never had to do anything with PHP via CLI or shell commands, so sorry for sounding a dumb
Re: PHP + MySQL + Large Text File = Unknown result.
Do you have SSH access? Log in via SSH if you can, and run the PHP script from there.
Re: PHP + MySQL + Large Text File = Unknown result.
Ahh no problem. We all have to start somewhere.
If you have a PHP script that is executed by the client....so replace the current script with this:
If you have a PHP script that is executed by the client....so replace the current script with this:
Code: Select all
echo 'some message';
//execute the script
//if I remember correctly, this is how you execute php from CLI on linux
`php -f the_otherscript.php`;
//if not, just google it and I'm sure you'll find something
Last edited by jackpf on Mon Sep 21, 2009 5:37 pm, edited 1 time in total.