Insert in blocks

[budimir]

Guys,

 

How can I insert data to DB in blocks? I have around 200.000 articles I'm calculating different things with it and inserting it in a DB when finished, but the problem is I'm runing out of memory.

 

I would like to insert 100 at a time in DB and free up some memory for the rest of the calculation.

 

Code I'm using:

while(get some data) {

Bunch of calculations

			//Create record inserts as array elements
			$values[] = sprintf("('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')",
								mysql_real_escape_string($id_nova_kalkulacija),
								mysql_real_escape_string($id_cjen),
								mysql_real_escape_string($kataloski_broj),
								mysql_real_escape_string($cijena_eurska),
								mysql_real_escape_string($cijena_KN),
								mysql_real_escape_string($carina_razlika),
								mysql_real_escape_string($spediter_razlika),
								mysql_real_escape_string($banka_razlika),
								mysql_real_escape_string($transport_razlika),
								mysql_real_escape_string($nabavna_cijena),
								mysql_real_escape_string($iznos_marze),
								mysql_real_escape_string($drezga_zarada),
								mysql_real_escape_string($vpc_drezga),
								mysql_real_escape_string($trosak_firme_f),
								mysql_real_escape_string($trosak_firme),
								mysql_real_escape_string($iznos_marze_diler),
								mysql_real_escape_string($preporucena_vpc),
								mysql_real_escape_string($preporucena_mpc),
								mysql_real_escape_string($zarada_diler_kn),
								mysql_real_escape_string($zarada_diler_p),
								mysql_real_escape_string($zarada_drezga_kn_netto),
								mysql_real_escape_string($zarada_drezga_p_netto),
								mysql_real_escape_string($zarada_drezga_kn_brutto),
								mysql_real_escape_string($zarada_drezga_p_brutto)
							  );

}

if (!empty($values)) {

		mysql_query ("ALTER TABLE kalkulacija_stavke DISABLE KEYS");

			$query = "INSERT INTO kalkulacija_stavke 
	(id_kalkulacija, id_cjenika, kataloski_broj, kategorija_artikla, grupa_proizvoda, podgrupa_proizvoda, cijena_EUR, cijena_KN, carina, spediter, banka, transport, nabavna_cijena, drezga_marza_po_grupi, drezga_zarada, neto_VPC, neto_MPC, trosak_firme, trosak_firme_p, diler_marza_po_grupi, preporucena_VPC, preporucena_MPC, zarada_diler_kn, zarada_diler_post, zarada_za_nas_kn, zarada_za_nas_post, brutto_zarada_za_nas_kn, brutto_zarada_za_nas_post) 
		VALUES" . implode(",",$values);
        echo "$query<br>";
			mysql_query($query) or die(mysql_error());
			
		mysql_query("ALTER TABLE kalkulacija_stavke ENABLE KEYS");

}

How could I achive that I do calculation for 100 articles, insert them in DB and than do next 100 and so on until finished?

[AbraCadaver]

Where are you running out of memory?  Building the $values array?

[scootstah]

You could possibly write to a temporary file, and then use the MySQL command-line utility to insert the file. You won't have memory issues this way, and can insert a very large amount of data.

[budimir]

scootstah

 

Thanks for the tip! That is the solution for me.