budimir

May 5, 2013

OK, I found a problem... Of course it's stupid me... I have limited my query to only two columns while I was testing, and didn't realise it when I wanted to see everything.

After I selected columns I needed, error was gone! Magic!

Sorry to disturbe you!

May 5, 2013

There is a lot of information in $row, around 15 variables in total. VARCHAR, DECIMAL, INT...

Everything is working fine for me, until I have to read a column that has a space in the name. There it fails. Tried to test it on test databse and I managed to simulate my problem, but I can't change working db.

May 5, 2013

I have to connect to mssql database and everything is working fine until I reach db filed which has whitespace in a name. Is there any workaround for this?

code example

while($row = sqlsrv_fetch_array( $rezultat_ms, SQLSRV_FETCH_ASSOC)){
    $kataloski_broj = $row["No_"];
    $naziv = $row["Description"];
    $kategorija = $row["Item Category Code"];
    $grupa_proizvoda = $row["Product Group Code"];

Error

Notice: Undefined index: Item Category Code in C:\wampserver\www\erp\katalog_pribora\sync\sync_item.php on line 28

First two variables are OK, but second two are failing beacuse of a space in a name. I can not change db field names.

Any workarounds?

May 5, 2013

Did you mange to fix you're problem?

You should try with this:

extension=php_sqlsrv_53_ts_vc9.dll

April 30, 2013

Maybe, this could help you...

http://php.net/manual/en/function.base64-encode.php

April 19, 2013

I found the problem!

it was a problem with setting the field in db. Value was to high for the field in the databse and it couldn't update the field... Strange thing is it didn't send any error???

April 19, 2013

Guys,

I'm trying to update a row in db, but nothing happens..

This is my query.

$upitak = "UPDATE kalkulacija_stavke SET vrijeme = '$vrijeme', kataloski_broj = '$kataloski_broj', kategorija_artikla = '$kategorija_artikla',
grupa_proizvoda = '$grupa_proizvoda', podgrupa_proizvoda = '$podgrupa_proizvoda', cijena_EUR = $text, cijena_KN = $hq_cijena_kn, carina = $carina_razlika, spediter = $spediter_razlika, 
banka = $banka_razlika, transport = $transport_razlika, nabavna_cijena = $nabavna_cijena_nova, drezga_marza_po_grupi = $drezga_marza_nova, drezga_zarada = $drezga_zarada_nova, neto_VPC = $vpc_drezga_nova,
neto_MPC = $mpc_drezga_nova, trosak_firme = $trosak_firme_nova, trosak_firme_p = $trosak_firme_p_nova, diler_marza_po_grupi = $diler_marza_nova, preporucena_VPC = $preporucena_vpc_nova1,
preporucena_MPC = $preporucena_mpc_nova, zarada_diler_kn = $zarada_diler_kn_nova, zarada_diler_post = $zarada_diler_p_nova, zarada_za_nas_kn = $zarada_drezga_kn_netto_nova, zarada_za_nas_post = $zarada_drezga_p_netto_nova,
brutto_zarada_za_nas_kn = $zarada_drezga_kn_brutto_nova, brutto_zarada_za_nas_post = $zarada_drezga_p_brutto_nova WHERE id = $id_redka AND id_kalkulacija = $id_kalkulacije ";		
echo "$upitak";
$rezultat = mysql_query($upitak,$veza) or die (mysql_error());

When I echo the query, this is the result:

UPDATE kalkulacija_stavke SET vrijeme = '2013-04-19 11:25:31', kataloski_broj = '967 18 85-01', kategorija_artikla = '', grupa_proizvoda = '', podgrupa_proizvoda = '', cijena_EUR = 1250, cijena_KN = 9625, carina = 481.25, spediter = 48.13, banka = 14.44, transport = 0, nabavna_cijena = 10168.82, drezga_marza_po_grupi = 52, drezga_zarada = 5287.7864, neto_VPC = 15456.6064, neto_MPC = 19320.758, trosak_firme = 2936.76, trosak_firme_p = 19, diler_marza_po_grupi = 40, preporucena_VPC = 21639.25, preporucena_MPC = 27049.06, zarada_diler_kn = 6182.64, zarada_diler_post = 40, zarada_za_nas_kn = 2351.03, zarada_za_nas_post = 15.21, brutto_zarada_za_nas_kn = 5287.79, brutto_zarada_za_nas_post = 34.21 WHERE id = 161241 AND id_kalkulacija = 2

Everything is OK as I can see it, but it won't update. I tried with mysql_affected_rows and it's not doing anything.

Can you help me?

April 15, 2013

Try to use:

$id=$_REQUEST['id'];

It will probably work. It's hard to say without seeing the code for form. I suggest using POST or GET. For REQUEST you should plan you're code.

April 6, 2013

I did store it in Session, but it doesn't work for some reason which I can't figure out. I do a session_start() in one of the include files I'm using, if you thought I didn't start the session.

Is it a problem with if (isset(submit)) that's not set when I click a link for next page on paging part??? If that is the problem how can I go around it?

April 6, 2013

Guys,

How could I keep values when isset() is clicked and after that a link is clicked which does something. I have paging inside if (isset(submit)) and when clicked next on paging everything goes back to previous state it doesn't keep values for filtering.

Below is code, I'm using. Please, help...

Form

		<form method="GET" action="<?php echo $_SERVER['PHP_SELF']; ?>">
		<input type="hidden" name="id" value="<?php echo "$id_kalkulacije"; ?>">
		   Upišite podgrupu proizvoda: <input type="text" name="podgrupa_proz">
		   <input type="submit" name="submit1" value="Traži">
		</form>

Php code

else if (isset($_GET["submit1"])) {

//pretraživanje po kategoriji
$id_kalkulacije = $_GET["id"];
$podgrupa_proz = $_GET["podgrupa_proz"];
$_SESSION["podgrupa_proz"] = $podgrupa_proz;

//Početak paginga
		if (isset($_GET['pageno'])) {
   			$pageno = $_GET['pageno'];
		} else {
   			$pageno = 1;
		} // if
		
		$upit11 = mysql_query("SELECT id FROM kalkulacija_stavke WHERE id_kalkulacija = '$id_kalkulacije' AND podgrupa_proizvoda LIKE '%".$_SESSION["podgrupa_proz"]."%'") or die (mysql_error());
		$brojcanik = mysql_num_rows($upit11);
		$rows_per_page = 100;
		$lastpage = ceil($brojcanik/$rows_per_page);
		$pageno = (int)$pageno;

		if ($pageno > $lastpage) {
		   $pageno = $lastpage;
		} // if
		
		if ($pageno < 1) {
		   $pageno = 1;
		} // if
		
		$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;


SOME QUERY WITH RESULT DISPLAY HERE......


	if ($pageno == 1) {
   		echo " <font color='#990000'>Početak</font> || Natrag ";
	} else {
   		echo " <a href='{$_SERVER['PHP_SELF']}?pageno=1&id=$id_kalkulacije&podgrupa_proz=$podgrupa_proz'>Prva</a> | ";
	    $prevpage = $pageno-1;
	    echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$prevpage&id=$id_kalkulacije&podgrupa_proz=$podgrupa_proz'>Natrag</a> ";
	} // if
	
		echo " ( <font color='grey'>Stranica - <b>$pageno</b> od <b>$lastpage</b></font> ) ";

	if ($pageno == $lastpage) {
   		echo " Naprijed || <font color='#990000'>Kraj</font> ";
	} else {
	   $nextpage = $pageno+1;
   	   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$nextpage&id=$id_kalkulacije&podgrupa_proz=$podgrupa_proz'>Naprijed</a> | ";
       echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$lastpage&id=$id_kalkulacije&podgrupa_proz=$podgrupa_proz'>Posljednja</a> ";
	} // if
       echo " <br />Rezultata: ".$brojcanik." </p>";

March 7, 2013

Barand,

I got it. Tried to experiment a bit, and this is the code that is working for me.

	$q_load =   "LOAD DATA LOCAL INFILE 'upload/".$_FILES['filename']['name']."'
				INTO TABLE kalkulacija_import_povijest_stavke
				FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY ''
				LINES TERMINATED BY '\r\n'
				IGNORE 1 LINES
				(kataloski_broj, @prosjecna_nc, @vpc, @mpc, @pvpc, @pmpc)
				SET 
				id_povijest = ".$id_cjenika." , 
				godina_pov = ".$godina." , 
				prosjecna_nc = CONVERT(REPLACE(REPLACE(@prosjecna_nc, '.', ''), ',', '.'), DECIMAL(10,2)) ,
				vpc = CONVERT(REPLACE(REPLACE(@vpc, '.', ''), ',', '.'), DECIMAL(10,2)) ,
				mpc = CONVERT(REPLACE(REPLACE(@mpc, '.', ''), ',', '.'), DECIMAL(10,2)) ,
				pvpc = CONVERT(REPLACE(REPLACE(@pvpc, '.', ''), ',', '.'), DECIMAL(10,2)) ,
				pmpc = CONVERT(REPLACE(REPLACE(@pmpc, '.', ''), ',', '.'), DECIMAL(10,2))";			
				
	mysql_query($q_load) or die (mysql_error());

Thank you very much for you're help!!! You really are saving my life...

March 7, 2013

This is the code I use, but it doesn't work. It inserts 0 to fileds.

	$q_load =   "LOAD DATA LOCAL INFILE 'upload/".$_FILES['filename']['name']."'
				INTO TABLE kalkulacija_import_povijest_stavke
				FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY ''
				LINES TERMINATED BY '\r\n'
				IGNORE 1 LINES
				(kataloski_broj, @prosjecna_nc, @vpc, @mpc, @pvpc, @pmpc)
				SET 
				id_povijest = ".$id_cjenika." , 
				godina_pov = ".$godina." , 
				prosjecna_nc = CONVERT(REPLACE(REPLACE('@prosjecna_nc', '.', ''), ',', '.'), DECIMAL(10,2)) ,
				vpc = CONVERT(REPLACE(REPLACE('@vpc', '.', ''), ',', '.'), DECIMAL(10,2)) ,
				mpc = CONVERT(REPLACE(REPLACE('@mpc', '.', ''), ',', '.'), DECIMAL(10,2)) ,
				pvpc = CONVERT(REPLACE(REPLACE('@pvpc', '.', ''), ',', '.'), DECIMAL(10,2)) ,
				pmpc = CONVERT(REPLACE(REPLACE('@pmpc', '.', ''), ',', '.'), DECIMAL(10,2))";			
				
	mysql_query($q_load) or die (mysql_error());

March 7, 2013

I can't implement it in the code below. It displays error in mysql syntax. Can you show me how to use that function in this code?

	$q_load =   'LOAD DATA LOCAL INFILE "upload/'.$_FILES['filename']['name'].'"
				INTO TABLE kalkulacija_import_povijest_stavke
				FIELDS TERMINATED BY ";" OPTIONALLY ENCLOSED BY ""
				LINES TERMINATED BY "\r\n"
				IGNORE 1 LINES
				(kataloski_broj, prosjecna_nc, vpc, mpc, pvpc, pmpc)
				SET id_povijest = '.$id_cjenika.' , godina_pov = '.$godina.'';			
				
	mysql_query($q_load) or die (mysql_error());

March 6, 2013

I'm completly lost now... Tried some conversions with decimal holders changing from . to , and other way. No success. Numbers which don't have comma in it are imported properly, but numbers that have comma in it get rounded to a very small number like 1.678,99 gets 1.68???

I don't get it. Why is it doing that?

March 6, 2013

I tried to format cells in CSV as text instead as number and than is importing OK. The problem there is I don't get decimal, only a round number.

So I guess it is a problem with number formating, but I don't know how to deal with it...

March 6, 2013

Here it is.

CREATE TABLE `kalkulacija_import_povijest_stavke` (  
`id` int(255) NOT NULL AUTO_INCREMENT,  
`id_povijest` int(255) NOT NULL,  
`vrijeme` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,  
`godina_pov` year(4) NOT NULL,  
`kataloski_broj` varchar(255) NOT NULL,  
`prosjecna_nc` decimal(10,2) NOT NULL,  
`vpc` decimal(10,2) NOT NULL,  
`mpc` decimal(10,2) NOT NULL,  
`pvpc` decimal(10,2) NOT NULL,  
`pmpc` decimal(10,2) NOT NULL,  
PRIMARY KEY (`id`)) 
ENGINE=MyISAM 
AUTO_INCREMENT=47838 
DEFAULT CHARSET=latin1

March 6, 2013

Barand,

I don't get any result from "SHOW CREATE TABLE kalkulacija_import_povijest_stavke". I have attached a CSV I'm trying to import so you can take a look and maybe see what is the problem...

NC i PC 2009.zip

March 6, 2013

Barand,

I'm using the same code on some other CSV files and is working properly, but on this CSV where I have different number formating is not working. The code is working, but number formating is not good. I'm getting article number and one price column in, but the second column is getting strange number and after that nothing.

I don't know why number formating is wrong? Do you have any suggestions how could I test what is failing?

March 6, 2013

I'm using the code below to load data from CSV to mysql, but the numbers imported are getting all messed up. I have mysql fields formated as decimal (10,2). Also, tried real, double, float, but nothing is helping.

For example, line I'm importing from CSV is like this:

967 54 71-66 872,64 1.679,20

2.099

Data I get in mysql is like this:

967 54 71-66 87264.00 1.68 kn 0.00 kn 0.00 kn 0.00 kn

How can I format the numbers to get them in mysql in exact shape like they are in CSV file?

	$q_load =   'LOAD DATA LOCAL INFILE "upload/'.$_FILES['filename']['name'].'"
				INTO TABLE kalkulacija_import_povijest_stavke
				FIELDS TERMINATED BY ";" OPTIONALLY ENCLOSED BY ""
				LINES TERMINATED BY "\r"
				IGNORE 1 LINES
				(kataloski_broj, @prosjecna_nc, @vpc, @mpc, @pvpc, @pmpc)
				SET 
				id_povijest = '.$id_cjenika.' , 
				godina_pov = '.$godina.' ,
				prosjecna_nc = REPLACE (@prosjecna_nc, "," , "") ,
				vpc = REPLACE(@vpc, "," , "") ,
				mpc = REPLACE(@mps, "," , "") ,
				pvpc = REPLACE(@pvpc, "," , "") ,
				pmpc = REPLACE(@pmpc, "," , "")
				';

March 5, 2013

OK, got it.

Thanks for the help!

This is reallllyyy fast...

March 5, 2013

Barand,

I managed to get it. but it seems I have a problem with inserting datetime. Is there any problems with inserting datetime?

This is my code, now...

$vrijeme = date("Y.m.d H:s");

$q_load =   'LOAD DATA LOCAL INFILE "upload/'.$_FILES['filename']['name'].'"
				INTO TABLE kalkulacija_import_cjenik_stavke
				FIELDS TERMINATED BY ";" OPTIONALLY ENCLOSED BY ""
				LINES TERMINATED BY "\r"
				IGNORE 1 LINES
				(kataloski_broj, naziv, cijena_EUR, valuta, cijena_DD, valuta_DD)
				SET id_cjenika = '.$id_cjenika.' , vrijeme = '.$vrijeme.' , godina = '.$godina.'';
				
	mysql_query($q_load) or die (mysql_error());

March 5, 2013

Barand,

That is very helpful, but I have one more question regardingyou're advice. I can't seem to find a proper answer so I wonder is it possible. In my code below, I'm able to insert all the lines from CSV to the database, but I would like to join some variables to that insert like date, time, id of linked table... Is it possible to do that?

	$q_load =   'LOAD DATA LOCAL INFILE "upload/'.$_FILES['filename']['name'].'"
				INTO TABLE kalkulacija_import_cjenik_stavke
				FIELDS TERMINATED BY ";" OPTIONALLY ENCLOSED BY ""
				LINES TERMINATED BY "\r"
				IGNORE 1 LINES
				(kataloski_broj, naziv, cijena_EUR, valuta, cijena_DD, valuta_DD)';
				
	mysql_query($q_load) or die (mysql_error());

Can I insert variables that don't belong to CSV file?

March 5, 2013

Guys,

I'm using the code below, but it timesout or crashes my browser. I guess the query that is being built gets to big and spends all memory. I'm inserting about 160.000 lines. How could I insert the by blocks of 2000 lines at a time? I guess that wouldn't crash the browser.

	//Import uploaded file to Database
	$handle = fopen($_FILES['filename']['tmp_name'], "r");
	
	$values = array();	
	while (($data = fgetcsv($handle, 300000, ";")) !== FALSE) {
	
			//Create record inserts as array elements
			$values[] = sprintf("('%s','%s','%s','%s','%s','%s','%s','%s','%s')",
								mysql_real_escape_string($id_cjenika),
								mysql_real_escape_string($vrijeme),
								mysql_real_escape_string($godina),
								mysql_real_escape_string($data[0]),
								mysql_real_escape_string($data[1]),
								mysql_real_escape_string($data[2]),
								mysql_real_escape_string($data[3]),
								mysql_real_escape_string($data[4]),
								mysql_real_escape_string($data[5])
							  );
	
	}
	
		$query="INSERT INTO kalkulacija_import_cjenik_stavke (id_cjenika, vrijeme, godina, kataloski_broj, naziv, cijena_EUR, valuta, cijena_DD, valuta_dd) 
		VALUES " . implode(",\n", $values);
		//echo "<pre>{$query}</pre><br>";
		mysql_query($query) or die (mysql_error());

	fclose($handle);

Any help would be appreciated...

March 2, 2013

Guys, thank you so much for the help!!! You saved me...

Psycho, you are the man. You gave a such good and simple example. I tried to implement something like that a few times, but I couldn't get to it work. This has pointed out what I have been doing wrong! I'm going to rewrite the rest of my code where I have been using while loops for insert query.

Once again, thank you so much!!!

I love this forum!!!

March 1, 2013

Never ever run a query in loop, it's a very bad practice!

Anyway, try this:

$import  = spintf("INSERT DELAYED INTO kalkulacija_import_kategorija (uvezao, vrijeme,kat_br,naziv_artikla,kategorija_artikla,grupa_proizvoda,podgrupa_proizvoda) VALUES (
        '%s','%s','%s','%s','%s','%s','%s')",
 mysql_real_escape_string($napravio),
 mysql_real_escape_string($vrijeme),
 mysql_real_escape_string($data[0]),
 mysql_real_escape_string($data[1]),
 mysql_real_escape_string($data[2]),
 mysql_real_escape_string($data[3]),
 mysql_real_escape_string($data[4]));

jazzman1, thank you so much! That worked like a charm...

When you say, to put a query outside of a loop... Do you have example how to do that?

Sign In

budimir

Posts

Joined

Last visited

Content Type

Profiles

Forums

Posts posted by budimir

Retriveing column name for db with whitespace

Retriveing column name for db with whitespace

Retriveing column name for db with whitespace

Connecting to MS SQL

PHP Quiz Validation

PHP -> MySQL update not working

PHP -> MySQL update not working

i can't get a value from a form

keep values after isset() is clicked and refreshed

keep values after isset() is clicked and refreshed

Load data infile messing up the numbers

Load data infile messing up the numbers

Load data infile messing up the numbers

Load data infile messing up the numbers

Load data infile messing up the numbers

Load data infile messing up the numbers

Load data infile messing up the numbers

Load data infile messing up the numbers

Load data infile messing up the numbers

Inserting from CSV to MySQL timesout

Inserting from CSV to MySQL timesout

Inserting from CSV to MySQL timesout

Inserting from CSV to MySQL timesout

Escape special characters

Escape special characters

Browse

Activity

Important Information