Data import troble

khibinite · #1 (**permalink**) 11-21-03, 09:03

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.

ika · #2 (**permalink**) 11-22-03, 11:55

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.

khibinite · #3 (**permalink**) 11-22-03, 14:51

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech