gordo2dope Posted February 23, 2008 Share Posted February 23, 2008 if, i have a script here im making to upload a .txt TAb delimited file. It moves and renames the file. Then It reads the file and makes each row into a line to read. Then it gets the rows and makes it into an array with seperate variables. So ... Im trying to get it to get the first number and check for it and if it exists return a true or false or something like that. The point it to search of an existing Part Number. If it exists, update, if not then insert as a new row. Right now I'm just trying to get the inserting part WHICH WORKS BY THE WAY. I just cant get the the IF result is matching to work .... The problem is it RUNS EITHER WAY ... so i need to get a correct IF statement so it will be able to determine to run or not. If i delete the rows in the TABLE, it runs (as it should), but if they are already in there, it STILL tries to insert and comes back as duplicate entries. But the whole point of the If is to determine if they are in there or not, so it will know NOT to try and insert. .Im not sure how to go about doing that.Right now ive just been trying crap like " if ($query = NULL) " and a bunch of other things. Anybody got some suggestions ?? [pre] <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>UPLOAD CSV FILE</title> </head> <body> <?php $csvdirectory = "uploadedcsv"; $currentdir=getcwd(); $target_path = $currentdir."\\".$csvdirectory."\\".basename($_FILES['userfile']['name']); echo "Target: $target_path<br>"; if (move_uploaded_file($_FILES['userfile']['tmp_name'], $target_path)) { echo "The file ".($_FILES["userfile"]["name"])." has been uploaded to ".$currentdir. "\\".$csvdirectory."\\<br><br>"; $uploaded = 'yes'; if (file_exists($target_path)) { echo "<font color='red'><b>FILE FOUND !!</b></font><BR>"; } } else { echo "There was an error uploading the file, please try again!<br>"; echo '<br>Here is some more debugging info:'; print_r($_FILES); print "</pre>"; } /////////////////////////////////////////////////////////////////// $uploaded_file = $target_path; $fcontents = file_get_contents($uploaded_file); // you have to point $uploaded_file at the correct file if(!strlen($fcontents)) { echo "file missing info , ERROR!"; } else { // START OF ELSE TO CONNECT AND TUN echo "<br><br><font color='red' size='+2'> FOUND THE RENAMED FILE !<hr></font>"; echo"STARTED <BR><BR> "; mysql_connect("XXXXXXX", "XXXXXXX", "XXXXXXX") or die(mysql_error()); mysql_select_db("XXXXXXX") or die(mysql_error()); $fcontents = file($uploaded_file); for($i=0; $i<sizeof($fcontents); $i++) { //start of FOR $line = trim($fcontents[$i]); $arr = explode("\t", $line); $partnum = $arr[0]; // the one we are searching for ... $second = $arr[1]; $description = $arr[2]; $onhand = $arr[3]; $avail = $arr[4]; $oo = $arr[5]; $cost = $arr[6]; $retail = $arr[7]; $sale = $arr[8]; $active = $arr[9]; #if your data is comma separated # instead of tab separated, # change the '\t' above to ',' $query = "SELECT * FROM `mczippo1` WHERE PartNum = ".$partnum; $data = mysql_query( $query ); // OK HERE IS THE PROBLEM BELOW ... i basically need to say IF query came back positive ... ??? if ($data != NULL) { // START OF IF $sql = "INSERT INTO mczippo1 (`PartNum`,`2nd`,`Description`,`OnHand`,`Avail`,`OO`,`Cost`,`Retail`,`Sale`,`Active`) VALUES ('".implode("','", $arr)."')"; mysql_query($sql); echo $sql ."<br><br>\n"; if(mysql_error()) { echo mysql_error() ."<br><br>\n"; } } // END OF IF } // END OF FOR } // END OF ELSE TO CONNECT AND RUN ?> </body> </html> [/pre] HERE IS A DOUBLE SCREEN SHOT SPLICED TOGETHER. the first one above green line is inserting, now... the second below the green line should not run because i want it to see the parts exist and therefor NOT run the code. Quote Link to comment Share on other sites More sharing options...
Bauer418 Posted February 23, 2008 Share Posted February 23, 2008 Since your part numbers are set by you, not your database, it makes this a bit easier. I see that you already have the part number column in your database set to be a unique index. You can run a query such as: REPLACE INTO table_name SET part_number='partnum', otherdata='otherval' And continue finishing that SQL query. REPLACE INTO will search to see if a row with that part_number already exists. If it does, it'll delete the old row and replace it with the new data. If none exists, it'll just insert a new row. Quote Link to comment Share on other sites More sharing options...
gordo2dope Posted February 23, 2008 Author Share Posted February 23, 2008 well see i do need that for the second part im going to be working on.. BUT , let me explain the problem. - we have a cashiering system that has say 10 fields we will be exporting to SQL everyday to either insert or update. - our SQL database has 33 fields. (10 regular and 23 we made up). - now, the problem with YOUR UPDATE LINE is if we UPDATE like that, it will overwrite existing data with NULLs since we wont have those extra fields populated before we INSERT, ive already tried. This is why i want to split it up into 2 seperate IF's and QUERYS. IF NO EXIST, insert 33 fields, including the unique indexed PARTNUM. ELSE exists, only UPDATE these 10 fields. WHERE PartNUM = $partnum (variable from array) (dont have this ELSE showing on the code on the page because i need to get the first part working first, then the second will work easy) this is what BOTH would LOOK LIKE ... something like this, the else im not sure if i should do insert OVER, or make UPDATE ... Unless you or someone else knows a betterway of going about it... [pre] <?php $query = "SELECT * FROM `mczippo1` WHERE PartNum = ".$partnum; $data = mysql_query( $query ); // OK HERE IS THE PROBLEM BELOW ... i basically need to say IF query came back positive ... ??? if ($data != $partnum) { // START OF IF /// REPLACE INTO table_name SET part_number='partnum', otherdata='otherval' $sql = "INSERT INTO mczippo1 (`PartNum`,`2nd`,`Description`,`OnHand`,`Avail`,`OO`,`Cost`,`Retail`,`Sale`,`Active`) VALUES ('".implode("','", $arr)."')"; mysql_query($sql); echo $sql ."<br><br>\n"; if(mysql_error()) { echo mysql_error() ."<br><br>\n"; } } // END OF IF else { $sql = "INSERT INTO mczippo1 (`2nd`,`Description`,`OnHand`,`Avail`,`OO`,`Cost`,`Retail`,`Sale`,`Active`) VALUES (`$second`,`$description`,`$onhand`,`$avail`,`$oo`,`$cost`,`$retail`,`$sale`,`$active`) WHERE PartNum = ".$partnum; mysql_query($sql); echo $sql ."<br><br>\n"; if(mysql_error()) { echo mysql_error() ."<br><br>\n"; } } ?>[/pre] Quote Link to comment Share on other sites More sharing options...
Bauer418 Posted February 23, 2008 Share Posted February 23, 2008 You could use SQL's: INSERT ... ON DUPLICATE KEY UPDATE Which will perform an update rather than a delete/reinsert Quote Link to comment Share on other sites More sharing options...
mem0ri Posted February 23, 2008 Share Posted February 23, 2008 You need to replace your 2nd INSERT with an UPDATE statement: UPDATE (table) SET col1 = 'var1', col2 = 'var2'... WHERE col = var; Quote Link to comment Share on other sites More sharing options...
gordo2dope Posted February 23, 2008 Author Share Posted February 23, 2008 mm this seems more like it probably let me mess around with that a little when i do the bottom half. BUt still the problem im having is the IF. either way its running the code. like the if is always coming back true. P.S. i dont know about or think of "INSERT ... ON DUPLICATE KEY UPDATE" thanks for that too. this might be a while but ill come back to post my findings, THANKS! Quote Link to comment Share on other sites More sharing options...
gordo2dope Posted February 23, 2008 Author Share Posted February 23, 2008 see it needs to be something like this ... added the FETCH ARRAY LINE. ive NEVER had a problem with an If or ELSE, so i think the problem is that its inside the FOR , which ive never used. [pre] <?php // this following array is supplied above from a tab dilimited file $partnum = $arr[0]; // the one we are searching for ... $second = $arr[1]; $description = $arr[2]; $onhand = $arr[3]; $avail = $arr[4]; $oo = $arr[5]; $cost = $arr[6]; $retail = $arr[7]; $sale = $arr[8]; $active = $arr[9]; $query = "SELECT * FROM `mczippo1` WHERE PartNum=".$partnum; $data = mysql_query( $query ); $part = mysql_fetch_array( $data ); // OK HERE IS THE PROBLEM BELOW ... i basically need to say IF query came back negative... ??? if ($partnum != $part['PartNum']) //if txt file PartNum does not = partnum from query { // START OF IF $sql = "INSERT INTO mczippo1 (`PartNum`,`2nd`,`Description`,`OnHand`,`Avail`,`OO`,`Cost`,`Retail`,`Sale`,`Active`) VALUES ('".implode("','", $arr)."')"; mysql_query($sql); echo $sql ."<br><br>\n"; if(mysql_error()) { echo mysql_error() ."<br><br>\n"; } } // END OF IF } // END OF FOR LOOP } // END OF ELSE TO CONNECT AND RUN ?> [/pre] Quote Link to comment Share on other sites More sharing options...
Bauer418 Posted February 23, 2008 Share Posted February 23, 2008 Did you give up on the single line INSERT ON DUPLICATE KEY UPDATE? Quote Link to comment Share on other sites More sharing options...
gordo2dope Posted February 23, 2008 Author Share Posted February 23, 2008 no im working on it on the side also ... just dont want to mix too much stuff in this post. Quote Link to comment Share on other sites More sharing options...
mem0ri Posted February 23, 2008 Share Posted February 23, 2008 Instead of checking if $partnum = $part['PartNum'] you need to check if you got an array result at all. if($part == FALSE) would do the trick. Quote Link to comment Share on other sites More sharing options...
Bauer418 Posted February 23, 2008 Share Posted February 23, 2008 This should be a working example (don't currently have a testing server to put it on) which I'll post without using INSERT ON DUPLICATE KEY UPDATE so you can clear up your problems. Text file (parts.txt - this file isn't tab delimited...it's actually 5 spaces, so the code won't work through copy/paste without changing the spaces to a tab): prt-1535 My Part This part has 3 parts prt-1536 My Part 2 This part has 4 parts Script file (parts.php - there are spaces in the fopen and fgetcsv commands because the forum won't let me post otherwise): <?php $csv = f open('parts.txt', 'r'); while ($line = f getcsv($csv, 0, "\t")) { list($part_num, $part_name, $part_desc) = $line; $query = "SELECT * FROM parts WHERE part_num='" . $part_num . "'"; $result = mysql_query($query) or die(mysql_error()); if (mysql_num_rows($result)) { mysql_query("UPDATE parts SET part_name='" . $part_name . "', part_desc='" . $part_desc . "' WHERE part_num='" . $part_num . "'") or die(mysql_error()); print "Existing part updated in the database"; } else { mysql_query("INSERT INTO parts SET part_num='" . $part_num . "', part_name='" . $part_name . "', part_desc='" . $part_desc . "'") or die(mysql_error()); print "New part added to the database"; } } ?> Quote Link to comment Share on other sites More sharing options...
gordo2dope Posted February 23, 2008 Author Share Posted February 23, 2008 Instead of checking if $partnum = $part['PartNum'] you need to check if you got an array result at all. if($part == FALSE) would do the trick. well memOri, this is what i WANT, but it still rungs the IF either way. damnit, i hate computers. Im telling you, it i think it has to do with the fact the IF is inside a FOR statement. Bauer - im working on 1 with yorus also right now. gonna take a bit to modify the fields and stuff though, thanks Quote Link to comment Share on other sites More sharing options...
gordo2dope Posted February 23, 2008 Author Share Posted February 23, 2008 awww well the updating works, which is great. but i get an error cause its searching for a PartNum that doesnt exist and comes back as false so the code DIES heres the error code.. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE PartNum='8001-3'' at line 1 [pre] <?php mysql_connect("XXXX", "XXXX", "XXXX") or die(mysql_error()); mysql_select_db("XXXX") or die(mysql_error()); $csv = f open('updatefile.txt', 'r'); while ($line = f getcsv($csv, 0, "\t")) { list($part_num, $part_2nd, $part_desc, $part_onhand, $part_avail, $part_oo, $part_cost, $part_retail, $part_sale, $part_active) = $line; $query = "SELECT * FROM mczippo1 WHERE PartNum='" . $part_num . "'"; $result = mysql_query($query) or die(mysql_error()); if (mysql_num_rows($result)) { mysql_query("UPDATE mczippo1 SET 2nd='" . $part_2nd . "', Description='" . $part_desc . "', OnHand='" . $part_onhand . "', Avail='" . $part_avail . "', OO='" . $part_oo . "', Cost='" . $part_cost . "', Retail='" . $part_retail . "', Sale='" . $part_sale . "', Active='" . $part_active . "' WHERE PartNum='" . $part_num . "'") or die(mysql_error()); print "Existing part updated in the database<BR><BR>"; } else { mysql_query("INSERT INTO mczippo1 SET PartNum='" . $part_num . "', 2nd='" . $part_2nd . "', Description='" . $part_desc . "', OnHand='" . $part_onhand . "', Avail='" . $part_avail . "', OO='" . $part_oo . "', Cost='" . $part_cost . "', Retail='" . $part_retail . "', Sale='" . $part_sale . "', Active='" . $part_active . "' WHERE PartNum='" . $part_num . "'") or die(mysql_error()); print "New part added to the database<BR><B>"; } } ?> [/pre] Quote Link to comment Share on other sites More sharing options...
Bauer418 Posted February 23, 2008 Share Posted February 23, 2008 Take the "WHERE PartNum..." off of the end of the insert statement and you should be good. Quote Link to comment Share on other sites More sharing options...
gordo2dope Posted February 23, 2008 Author Share Posted February 23, 2008 HAH! duhh ! why didnt i see that, its cause i copied and pasted the line from the IF so I wouldnt have to type it all out again. And didnt take the WHERE off. this works great, thanks alot. now i just need to get it working with my file uploader and renamer thing. THANKS!! heres what i get when i change the file and upload it... Existing part updated in the database New part added to the database New part added to the database Existing part updated in the database New part added to the database Existing part updated in the database Quote Link to comment Share on other sites More sharing options...
gordo2dope Posted February 23, 2008 Author Share Posted February 23, 2008 just 1 question ... what is the R for ?? $csv = f open('updatefile.txt', 'r'); Quote Link to comment Share on other sites More sharing options...
Bauer418 Posted February 23, 2008 Share Posted February 23, 2008 That specifies what type of file handle should be created. 'r' Open for reading only; place the file pointer at the beginning of the file. 'r+' Open for reading and writing; place the file pointer at the beginning of the file. 'w' Open for writing only; place the file pointer at the beginning of the file and truncate the file to zero length. If the file does not exist, attempt to create it. 'w+' Open for reading and writing; place the file pointer at the beginning of the file and truncate the file to zero length. If the file does not exist, attempt to create it. 'a' Open for writing only; place the file pointer at the end of the file. If the file does not exist, attempt to create it. 'a+' Open for reading and writing; place the file pointer at the end of the file. If the file does not exist, attempt to create it. 'x' Create and open for writing only; place the file pointer at the beginning of the file. If the file already exists, the fopen() call will fail by returning FALSE and generating an error of level E_WARNING. If the file does not exist, attempt to create it. This is equivalent to specifying O_EXCL|O_CREAT flags for the underlying open(2) system call. 'x+' Create and open for reading and writing; place the file pointer at the beginning of the file. If the file already exists, the fopen() call will fail by returning FALSE and generating an error of level E_WARNING. If the file does not exist, attempt to create it. This is equivalent to specifying O_EXCL|O_CREAT flags for the underlying open(2) system call. Quote Link to comment Share on other sites More sharing options...
gordo2dope Posted February 23, 2008 Author Share Posted February 23, 2008 cool, thanks for all the help and info. theres no way to mark this topic as SOLVED huh, like that other page does. THANKS EVERYONE! especially you Bauer. Quote Link to comment Share on other sites More sharing options...
Bauer418 Posted February 23, 2008 Share Posted February 23, 2008 Glad to help. Currently, there's no topic solved button...I think they're working on getting a new one put in. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.