Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    63

    Unanswered: Data import trouble

    Hello there!

    I am before a problem. I need to optimize a large table by separating data into 4 table and joining them by key fields. This table contain 63 fields. 3 dictionary tables are more small and import into them is not complicated. So, the data from main table also should be imported into small table. But fields are same with dictionary tables should be replaced to only one field value from dictionary. I wrote php-script to simplify this operation. But after long time server writes about 500 internal server error.

    There is a script code. Could you take some tips helping in my problem solving?

    Code:
    
    <?php
        $dbhost = "localhost";
        $db = "catalog";
        $dbu = "root";
        $dbp = "password";
    
        function check_assign ($source, &$res) {
    	$source = $res;
        	if (strlen(trim($source)) == 0) {
            	$res = "''";
            };
        };
    
        $link = mysql_connect($dbhost, $dbu, $dbp)
           or die("Could not connect: " . mysql_error());
    
    	$db_link = mysql_select_db($db, $link);
    
        $s = "select * from paddata_old
    		join programs on paddata_old.programname = programs.programname
    		join company on paddata_old.companyname = company.companyname
    		join author on paddata_old.authorfirstname = author.authorfirstname and paddata_old.authorlastname = author.authorlastname
    	";
        $q = mysql_query($s);
        $n = mysql_num_rows($q);
        $div = 40;
        $steps = floor($n/$div);
        $leav = fmod($n, $div);
        for ($i=0;$i<=$steps;$i++) {
    		$arr[] = $div*$i;
        };
        if ($leav > 0) {
        	$arr[] = $n;
        };
    
        for ($i=0;$i<count($arr);$i++) {
            if (isset($arr[$i+1])) {
    	        $v = $arr[$i] + 1;
    	        $s = "select * from paddata_old
    	            join programs on paddata_old.programname = programs.programname
    	            join company on paddata_old.companyname = company.companyname
    	            join author on paddata_old.authorfirstname = author.authorfirstname and paddata_old.authorlastname = author.authorlastname
    	            where paddata_old.id between ".$v." and ".$arr[$i+1];
    
    	        $q = mysql_query($s);
    	        while ($row = mysql_fetch_array($q)) {
    	            check_assign($row[59], $aspform);
    	            check_assign($row[60], $aspmember);
    	            check_assign($row[61], $aspmembernumber);
    
    	            $s = "insert into paddata (ASPFORM, ASPMember, ASPMemberNumber, ProgId, CompanyId, AuthId)
    	              ($aspform, $aspmember, $aspmembernumber,$row[63],$row[102],$row[121])";
    	            $q = mysql_query($s);
    	        };
            };
        };
    ?>
    
    Thanks in advance.
    Last edited by khibinite; 11-21-03 at 10:08.

  2. #2
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482
    At first here is my question: What webserver's error log says?

    Here is my advice:

    1. Don't use PHP running under Webserver for this operation.
    Will be better when you configure your PHP with --cli option and run as script from the shell (command line) or use another scripting language for example PERL.

    2. If you don't like the first option then try this:
    Save your select to a file CVS, or INSERT string format and then import to the database.

  3. #3
    Join Date
    Oct 2003
    Posts
    63
    Thanks for your answer!

    I solved my trouble via such method:

    1)I made the import via simple Delphi application (it has a lot of features to make it simple).

    2)Upload new table to the web-server.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •