PHP + MySQL + Large Text File = Unknown result.

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

Benwahballz
Forum Commoner
Posts: 25
Joined: Mon Sep 21, 2009 12:54 pm

PHP + MySQL + Large Text File = Unknown result.

Post 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
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

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

Post 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.
Benwahballz
Forum Commoner
Posts: 25
Joined: Mon Sep 21, 2009 12:54 pm

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

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

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

Post by Eran »

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.

Post 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?)
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.

Post by Benwahballz »

I may try make this into a perl scrip as well to load the data.
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

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

Post by jackpf »

8O 8O 8O 8O

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.

Post 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
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

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

Post 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? :)
User avatar
Mirge
Forum Contributor
Posts: 298
Joined: Thu Sep 03, 2009 11:39 pm

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

Post by Mirge »

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

Post 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.
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

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

Post 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 :/
Benwahballz
Forum Commoner
Posts: 25
Joined: Mon Sep 21, 2009 12:54 pm

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

Post 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 :?
User avatar
Mirge
Forum Contributor
Posts: 298
Joined: Thu Sep 03, 2009 11:39 pm

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

Post by Mirge »

Do you have SSH access? Log in via SSH if you can, and run the PHP script from there.
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

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

Post 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
Last edited by jackpf on Mon Sep 21, 2009 5:37 pm, edited 1 time in total.
Post Reply