shashib
-
Posts
45 -
Joined
-
Last visited
Content Type
Profiles
Forums
Events
Posts posted by shashib
-
-
I need to check if value inserting in database while **import excel file** , if it has already value in database then it should get update.
Below is producttab table value in database
prdid | prdname 00A | prd1 00B | prd2 00C | prd3 00D | prd4Below is EXCEL FILE data
prdid | prdname 00A | prdnew 00B | prd2new 00E | prd8 00H | prd9So if i upload above excel file then ,
00A , 00B should get UPDATE IN producttab table as they are already present there... but 00E,00H should get insert
below is what i have tried, value is getting insert properly
only UPDATE IS NOT HAPPENINGif(isset($_POST["Upload"])) { $fileinfo = pathinfo($_FILES["uploadFile"]["name"]); $filetype = $_FILES["uploadFile"]["type"]; $remark = NULL; //Validate File Type if(strtolower(trim($fileinfo["extension"])) != "csv") { $_SESSION['msg_r'] = "Please select CSV file"; header("location:importfile.php"); exit; } else { $file_path = $_FILES["uploadFile"]["tmp_name"]; } $row = 0; $tempFileName = time().".csv"; if ( is_uploaded_file( $file_path ) ) { $fileCopied = copy( $file_path , $tempFileName); if (($handle = fopen($tempFileName, "r")) !== FALSE) { fgetcsv($handle); while (($data = fgetcsv($handle, 6000, ",")) !== FALSE) { $num = count($data); for ($c=0; $c < $num; $c++) { $col[$c] = $data[$c]; } $col1 = $col[0]; // prdid $col2 = $col[1]; // prdname $sql = "SELECT prdid FROM producttab WHERE prdid = '".$col1."' "; $query = db_query($sql); $pfetech = db_fetch($query); if($col1 == $pfetech['prdid']){ $sqlup = "UPDATE producttab SET prdid = ".$pfetech['prdid'].", prdname = ".$col2." "; $sqlup .= " WHERE prdid = ".$pfetech['prdid']." "; $resultsqlupdate = mysql_query($sqlup); }else{ $query = "INSERT INTO producttab(prdid,prdname) VALUES('".$col1."','".$col2.")"; $s = mysql_query($query); } } fclose($handle); } echo "<center>File data imported to database!!</center>"; } } } -
Its PHP MYSQL :
I have a table prodt , in which i first INSERT a value and with its LAST INSERT ID i do update for MAX + 1 as below , BUT I AM GETTING ERROR
You can't specify target table 'prodt' for update in FROM clause$a = db_insert_id(); $sqllast = "UPDATE prodt SET pdname= ((SELECT pdname FROM ( SELECT MAX( pdname ) AS pdname FROM prodt WHERE oid = ".db_escape($oid)." ) AS pdname ) + 1 ), pcyn = ".db_escape(0)." WHERE id = ".db_escape($a)." AND oid= ".db_escape($oid)." "; $resultsqllast = db_query($sqllast); if((!$resultsqllast) || (db_mysql_affected_rows($db) <= 0)) { throw new Exception('Wrong SQL UPDATE' . $sqllast . ' Error: '.db_error_msg($db) . db_error_no()); }
After research i tried below :$sqllast = "UPDATE prodt SET pdname= ((SELECT pdname FROM ( SELECT MAX( pdname ) AS pdname FROM ( SELECT * FROM prodt WHERE oid = ".db_escape($oid)." )AS pdname ) AS pdname ) + 1 ), pcyn = ".db_escape(0)." WHERE id = ".db_escape($a)." AND oid= ".db_escape($oid)." "; $resultsqllast = db_query($sqllast); if((!$resultsqllast) || (db_mysql_affected_rows($db) <= 0)) { throw new Exception('Wrong SQL UPDATE' . $sqllast . ' Error: '.db_error_msg($db) . db_error_no()); }
But still its not working, getting same error message ...
Thanks -
hey dude,
And what if i need as below, as i tried to build in your above query ...but i failed .....
id | mid | wgh | remark| remkok | name | 1 3 1.5 r3ok 1 name1 2 2 1.5 0 name3 3 2 0.6 nice 0 name3 4 1 1.2 okh 0 name4 5 4 1.5 bye 0 name2 6 4 2.4 okby 0 name2 7 3 3.0 oknice 1 name1mid wgh 3 1.5 3.0 remarks : NAME1 - r3ok, oknice 4 1.5 2.4 remarks : NAME2 - bye, okby 2 1.5 0.6 remarks : NAME3 - , nice 1 1.2 remarks : NAME4 - okh -
hey @dsonesuk, thanks you very much , have tried your above code ....and its working ... will implement it fully with my all conditions ....
thanks once again
-
hey @dsonesuk , thanks for reply ... please help me out what you have in mind ...might it can help me ...basically that remarks row will be seen only when my Column remkok = 1 ... but to explain each point was making my question confusing ..hence i wrote it simple....
please help me with your answer
Thanks
-
i have modified my question ..please see
-
any one here ..please help me
...any other solution with other table tr format will help me tooo -
Below is products table :
id | mid | wgh | remark| remkok | 1 3 1.5 r3ok 1 2 2 1.5 0 3 2 0.6 nice 0 4 1 1.2 okh 0 5 4 1.5 bye 0 6 4 2.4 okby 0 7 3 3.0 oknice 1I want to display remark below tr of group by mid ....like below
mid wgh 3 1.5 3.0 remarks : r3ok, oknice 4 1.5 2.4 remarks : bye, okby 2 1.5 0.6 remarks : , nice 1 1.2 remarks : okh**What i have tried as below :**
$pid= null; while($row = mysql_fetch_array($result)) { $rowpkts = $row['mid']; echo "<tr class=\"undercl\">"; if($rowpkts != $pid){ echo'<td align="center" valign="top">'.$row["mid"].'</td>'; }else{ echo'<td align="center" valign="top"></td>'; } echo'<td align="center" valign="top">'.$row["wgh"].'</td>'; echo "</tr>"; // what i tried to build for remarks as below $remsql = "SELECT mid as onu , GROUP_CONCAT(`remark` ORDER BY `id` ASC SEPARATOR ', ') AS plrmks FROM products WHERE remkok= 1 GROUP BY `mid`"; $fetchremk = mysql_query($remsql); $rowresults = mysql_fetch_array($fetchremk); if($rowresults['onu'] == $pid ){ echo"<tr style='border-style:underline;'>"; echo'<td align="center" align="top">'.$rowresults["plrmks"].'</td>'; echo"</tr>"; } } $pid = $rowpkts; }But remarks is not coming proper ...i means its not display below mid=3 or mid=1.....
-
hey thanks for concepts ...
NO range is also in system ...please SEE UPDATE QUESTION :
but the thing is that value 26,25.30 is in excel file .... let say i make tree node for AB and XE and others as you said ....but what about value ...how i will call that from excel file ...how i can differentiate that 26 belong to which tree node from excel file.
As said early that excel file has many boxes ( as per your logic : many others node and its value ) combinations , above is just sample ....
i think node logic is nice ... but after that which value belongs to which node ...how i can get that from excel file
I have to read each row ,column of excel file as per node ????... how i can do that ...
As main node AB is can be done ...but XE node ??...and after XE ...i have another BH....main node ...then how i can differentiate that in excel ...
if its was in database it would be easy ...but it impossible for me to insert each combination in database ...
Thanks once again ....
-
Above Code box is data which is in excel in same way as i have shown ...
in PHP page i have below drop-down value
1 } 0.45-0.90 ( range ) : thats 0.45,0.55,0.69,..0.90 2 } AB , XE 3 } A,B,C,D 4 } b1,b2,b3,X1,X2,X3
When any one select
0.45 - AB - A - b1 :: then its value should come from excel or any other matter(mysql etc) : 23
i cant make each combination and store in database, as there are thousands of combination , so is there any easy way that excel format get store in mysql or any coding etc...
i haven't tried anything ..as i am not getting how to start an what to start ...as this value is in excel and its like martix ...
-
Any with solution .....
-
any one here ?????
-
------------------------------|------------------ AB | XE ------------------------------|------------------ | b1 | b2 | b3 | X1 | X2 | X3 ------------------------------|------------------ | A | 23 | 34 | 45 | 20 | 19 | 80 0.45 - 0.90| B | 20 | 31 | 85 | 23 | 23 | 67 | C | 23 | 32 | 23 | 10 | 12 | 23 | D | 10 | 12 | 23 | 56 | 45 | 23-----------------------------|------------------ AB | XE ------------------------------|------------------ | b1 | b2 | b3 | X1 | X2 | X3 ------------------------------|------------------ | A | 33 | 34 | 65 | 40 | 19 | 90 1.00 - 2.90| B | 80 | 91 | 85 | 23 | 23 | 47 | C | 63 | 82 | 63 | 90 | 82 | 83 | D | 20 | 82 | 23 | 56 | 65 | 26Above date is in excel
note : 0.45-0.90 is range from 0.45 to 0.90
IN PHP page I have value in drop down for below :
1 } 0.45-0.90 ( range ) : thats 0.45,0.55,0.69,..0.90 2 } AB , XE 3 } A,B,C,D 4 } b1,b2,b3,X1,X2,X3
that numbers : 23,34,45,..etc is output, when any user select above drop-down data :
Like : example as below : when any one select FIRST RANGE ( 0.45 - 0.90 ) :
0.45 - AB - A - b1 : then he will get output in textbox as ***23*** 0.45 - AB - A - b2 : then he will get output in textbox as ***34*** 0.55 - AB - A - b2 : then he will get output in textbox as ***34*** 0.69 - AB - B - b2 : then he will get output in textbox as ***31*** 0.69 - XE - B - X2 : then he will get output in textbox as ***23*** 0.90 - XE - C - X3 : then he will get output in textbox as ***23*** 0.90 - XE - D - X1 : then he will get output in textbox as ***56***
Like : example as below : when any one select SECOND RANGE ( 1.00 - 2.90 ) :
1.01 - AB - A - b1 : then he will get output in textbox as ***33*** 1.11 - AB - A - b2 : then he will get output in textbox as ***34*** -
id | mid | pid | owgh | nwgh |1 3 12 1.5 0.62 3 12 1.5 0.33 3 14 0.6 0.44 3 15 1.2 1.15 4 16 1.5 1.06 4 17 2.4 1.27 3 19 3.0 1.4
Select mid , COUNT(distinct pid) as cpid , SUM(nwgh) as totalnwgh from test GROUP BY mid
sqlfiddle : link of below result with above query
mid cpid totalnwgh3 4 3.84 2 2.2
But above i need one more column that's as below : **totowgh**
mid cpid totalnwgh totowgh3 4 3.8 6.3 (DISTINCT value as per pid column)4 2 2.2 3.9
where totowgh = 6.3 come by DISTINCT value as per pid columnthat's mid = 3 has count 5 but distinct pid = 4 for mid=3 same way "distinct" owgh = 6.3 for mid=3 and distinct pid.As pid=12 is count 1 time hence,1.5 + 0.6 + 1.2 + 3 = 6.3 ( please not this is as per DISTINCT value of pid )Please note : i need owgh value as per distinct pid or group by pid .. because if i replace value of owgh 0.6 with 1.5 then it will be 5.7 instead of 7.2 but value of owgh 0.6 belong to pid = 14 and not pid = 12 hence totalcount of owgh change ...but i need is 7.2SEE WHAT I MEANS : sqlfiddle.com/#!9/2a53c/6
-
thanks sir will try this
-
Have you used recursive functions before?
NO SIR not used that ... actauly i am storing that main cat id and its parent id cat in product table ..becuase i have to manage that record with some inbuild functionality i know its bit complicated ...but to get fast result as per 6,3 ...
but geting all result as per 3 is making more complex ..i know i need to go with recurisve function ..but yes i havent used that before ...can you please guide me on same .. thanks
-
@ justsomeguy sir, can you tell me with example with above as a example .
i need to store cat id in product and user product table as i need to manitain the forigen relation with cat and prod,userprod table .... hence i have put cat id in both tables .... please guide me so that in my next phase of my s/w i can improve this better to get result ...
as now if above query i am get only parent and its single child ..... not n level childs
pls help me
-
any one ..please help me...
actually its combine of this : http://w3schools.invisionzone.com/index.php?showtopic=52843&hl=
i got solution on above link, thanks to davej ...but above question parent id wise result is not matching here ....
-
Cat and sub category Table (cat_tbl) :
id| cat_n | parent_id1 | cat | 02 | dog | 03 | tiger | 24 | lion | 05 | abc | 06 | bcd | 3
Now i have a product table as below (prod_tbl) :
id | pwght | cid | cpid10 | 1.2 | 1 | 011 | 2.4 | 2 | 012 | 3.4 | 2 | 013 | 4.5 | 6 | 3
and user final weight product table is below (userprod_tbl)
id | pwght | cid | cpid | prod_id ( is above prod_tbl primary id )1 | 1.1 | 1 | 0 | 102 | 2.3 | 2 | 0 | 113 | 3.1 | 3 | 2 | 124 | 4.0 | 6 | 3 | 13
**RESULT : ( OUTPUT WHICH I WANT ) IS comparison of prod_tbl with userprod_tbl as below :**
Prod tbl Userprod tblcat 1.2 cat 1.1dog 2.4 dog -- -- 2.3dog 3.4 dog tiger -- 3.1dog 4.5 dog tiger bcd 4.0
Hence in above result 2.4,3.4,4.5 are belong to Parent 2**But i am getting as below**
Prod tbl Userprod tblcat 1.2 cat 1.1dog 2.4 dog -- -- 2.3dog 3.4 dog tiger -- 3.1here i am not getting 4.5 value as 4.5 has 6,3 relation from above prod table but its parent of 2
**Below is my query which i have return:**
SELECT pt.pwght , upt.pwght ,ct.cat_n,uct.cat_n,umct.cat_nFROM prod_tbl AS ptLEFT JOIN userprod_tbl AS upt ON (pt.id = upt.prod_id)LEFT JOIN cat_tbl AS ct ON pt.packet_id = ct.idLEFT JOIN cat_tbl AS uct ON upt.packet_id = uct.idLEFT JOIN cat_tbl AS umct ON upt.parent_packet_id = umct.id
Please let me know what is missingThanks
-
Cat and sub category Table (cat_tbl) :
id | cat_n | parent_id1 | cat | 02 | dog | 03 | tiger | 24 | lion | 05 | abc | 06 | bcd | 3
Now i have a product table as below (prod_tbl) :
id | pwght | cid | cpid10 | 1.2 | 1 | 011 | 2.4 | 2 | 012 | 3.4 | 2 | 013 | 4.5 | 6 | 3
and user final weight product table is below (userprod_tbl)
id | pwght | cid | cpid | prod_id ( is above prod_tbl primary id )1 | 1.1 | 1 | 0 | 102 | 2.3 | 2 | 0 | 113 | 3.1 | 3 | 2 | 124 | 4.0 | 6 | 3 | 13
**RESULT : ( OUTPUT WHICH I WANT ) IS comparison of prod_tbl with userprod_tbl as below :**
Prod tbl Userprod tblcat 1.2 cat 1.1dog 2.4 dog -- -- 2.3dog 3.4 dog tiger -- 3.1dog 4.5 dog tiger bcd 4.0
Hence in above result 2.4,3.4,4.5 are belong to Parent 2**But i am getting as below**
Prod tbl Userprod tblcat 1.2 cat 1.1dog 2.4 dog -- -- 2.3dog 3.4 dog tiger -- 3.1
here i am not getting 4.5 value as 4.5 has 6,3 relation from above prod table but its parent of 2**Below is my query which i have return:**
SELECT pt.pwght , upt.pwght ,ct.cat_n,uct.cat_n,umct.cat_nFROM prod_tbl AS ptLEFT JOIN userprod_tbl AS upt ON (pt.id = upt.prod_id)LEFT JOIN cat_tbl AS ct ON pt.packet_id = ct.idLEFT JOIN cat_tbl AS uct ON upt.packet_id = uct.idLEFT JOIN cat_tbl AS umct ON upt.parent_packet_id = umct.id
Please let me know what is missingThanks
-
Thanks u so much
-
Hi davej ,
Thanks for reply ...
Sir Old Weight is coming wrong .. its taking SUM of all ... but if you check above i need only SUM of ( 1.5 + 0.6 + 1.2 + 3.0 ) = 6.3
but its doing actually ( (1.5 + 1.5)+ 0.6 + 1.2 + (3.0 +3.0 ))
ii dont want that repeated value sum of same group but if sum of this should work ( 1.5 + 0.6 + 1.5 + (3.0 +3.0 )) in this 1.5 are of other group id hence its should added
Hope you got me ....
and sir New weight is working perfect ...
if i have another column suppose as name = another_weight which values are ( 1+2+4+5+6+7) = 25 .. which belong to each above resp. id ..then can i get sum of it
Thanks
-
Hi ,
Can i get Grand Total : at last ? of each column
Please note : in old weight column weight are repeated twice , trice but i want its single value to be added as below :
Product Id | Old weight | New weight --------------------------------------- 12 1.5 0.6 12 1.5 0.3 Tot: 3.0 0.9 -------------------------------- 14 0.6 0.4 Tot: 0.6 0.4 -------------------------------- 15 1.2 1.1 Tot: 1.2 1.1 -------------------------------- 19 3.0 1.4 19 3.0 1.2 Tot: 6.0 2.6------------------------------------------ Grand Total : 6.3 5 Note in above Old weight : repeat value is not added thats 1.5 , 3.0 is coming twice hence while addition takensingle of it**OR** Product Id | Old weight | New weight --------------------------------------- 12 1.5 0.6 1.5 0.3 Tot: 3.0 0.9 -------------------------------- 14 0.6 0.4 Tot: 0.6 0.4 -------------------------------- 15 1.2 1.1 Tot: 1.2 1.1 -------------------------------- 19 3.0 1.4 3.0 1.2 Tot: 6.0 2.6------------------------------------------ Grand Total : 6.3 5Note in above Old weight : repeat value is not added thats 1.5 , 3.0 is coming twice hence while addition takensingle of it
echo "<table><tr><td> Product Id </td><td> Old Weight </td><td> New Weight </td></tr>";$pid = null;$sumold = 0;$sumnew = 0;while($row = mysql_fetch_array($query)){if ($row['pid'] == $pid || $pid === null){echo "<tr>";echo "<td>".$row['pid']."</td>";echo "<td>".$row['owgh']."</td>";echo "<td>".$row['nwgh']."</td>";echo "</tr>";$pid = $row['pid'];$sumold += $row['owgh'];$sumnew += $row['nwgh'];}else{echo "<tr><td></td><td></td><td></td></tr><tr><td></td><td>Tot :".$sumold."</td><td>".$sumnew."</td></tr><tr><td>------</td><td>------</td><td>------</td></tr>";echo "<tr>";echo "<td>".$row['pid']."</td>";echo "<td>".$row['owgh']."</td>";echo "<td>".$row['nwgh']."</td>";echo "</tr>";$sumold = $row['owgh'];$sumnew = $row['nwgh'];$pid = $row['pid'];}}echo "<tr><td></td><td></td><td></td></tr><tr><td></td><td>Tot :".$sumold."</td><td>".$sumnew."</td></tr><tr><td>------</td><td>------</td><td>------</td></tr><tr><td>Grand Tol </td><td>Total OLD WGT</td><td>Total NEW WGT</td></tr></table>";Please help me
-
@ davej,
Thanks , this is what i needed and struggling from last 1 week for same
..You made my day Sir .
Thanks alot alot... thank you sooo muchhh
Update on dulipcate entry while Import Excel file - php mysql
in PHP
Posted
any one here ....only my UPDATE QUERY IS NOT WORKING ... SELECT QUERY IS PERFECT ...IT TAKES ME IN IF CONDITION IF == else it take me in else loop
this is what echo/print my update query shows....but in database value is not update ....