Page 1 of 3

PHP + MySQL + Large Text File = Unknown result.

Posted: Mon Sep 21, 2009 1:00 pm
by Benwahballz
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

Re: PHP + MySQL + Large Text File = Unknown result.

Posted: Mon Sep 21, 2009 1:02 pm
by jackpf
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.

Re: PHP + MySQL + Large Text File = Unknown result.

Posted: Mon Sep 21, 2009 2:14 pm
by Benwahballz
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.

Re: PHP + MySQL + Large Text File = Unknown result.

Posted: Mon Sep 21, 2009 2:22 pm
by Eran
post your code, and we'll have a look

Re: PHP + MySQL + Large Text File = Unknown result.

Posted: Mon Sep 21, 2009 2:34 pm
by Benwahballz
Here is the code:

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();
?>
 
And the result is as explained above. Random stoppages (is that a word?)

Re: PHP + MySQL + Large Text File = Unknown result.

Posted: Mon Sep 21, 2009 2:35 pm
by Benwahballz
I may try make this into a perl scrip as well to load the data.

Re: PHP + MySQL + Large Text File = Unknown result.

Posted: Mon Sep 21, 2009 3:02 pm
by jackpf
8O 8O 8O 8O

learn to indent 'lol.

Re: PHP + MySQL + Large Text File = Unknown result.

Posted: Mon Sep 21, 2009 3:10 pm
by Benwahballz
jackpf wrote:8O 8O 8O 8O

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.

Posted: Mon Sep 21, 2009 3:15 pm
by jackpf
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, :offtopic: :P

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.

Posted: Mon Sep 21, 2009 3:21 pm
by Mirge
+1 for CLI... much better suited for this type of script.

Re: PHP + MySQL + Large Text File = Unknown result.

Posted: Mon Sep 21, 2009 3:27 pm
by Benwahballz
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.

Re: PHP + MySQL + Large Text File = Unknown result.

Posted: Mon Sep 21, 2009 3:56 pm
by jackpf
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 :/

Re: PHP + MySQL + Large Text File = Unknown result.

Posted: Mon Sep 21, 2009 4:05 pm
by Benwahballz
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 :P :?

Re: PHP + MySQL + Large Text File = Unknown result.

Posted: Mon Sep 21, 2009 4:13 pm
by Mirge
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.

Posted: Mon Sep 21, 2009 4:18 pm
by jackpf
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:

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