budimir

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!

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.

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?

Did you mange to fix you're problem? You should try with this: extension=php_sqlsrv_53_ts_vc9.dll

Maybe, this could help you... http://php.net/manual/en/function.base64-encode.php

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???

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?

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.

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?

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>";

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

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());

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());

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?

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

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

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

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?

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, "," , "") ';

OK, got it. Thanks for the help! This is reallllyyy fast...

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());

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?

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

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!!!

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?