gordo2dope

yeah seems like it works, takes a couple seconds to run the query and display the page, but well see how it is in the end. this is what i ended up changing the query to. $query = "SELECT category1.category1, category1.cat1id FROM `category1`, category2, mczippo1 WHERE category1.cat1id = category2.cat1id AND category2.cat2id = mczippo1.cat2 AND mczippo1.Picture >= '1' AND mczippo1.Avail >= mczippo1.MinQty AND mczippo1.Closeout = 'Y' AND mczippo1.Active = 'S' GROUP BY cat1id";

i think i got it... so far seems ok, im gonna do some testing before i post as solved....

ok im back, been a while since ive been here. I ran across a little situation with a long query here. well problem is we are going to be selling full price items and sale items and need to have sepereate pages for them. there are 3 tables (MAIN CATEGORY, 2nd CATEGORY, and ITEMS FOR SALE). The fullprice page will show everything, and then the sale page will show only items marked as 'S' for sale. Problem is both pages will be dynamically displayed and i dont know how to get the SALE PAGE to only show links to sub categories for items only on sale and have BOTH full price and sale price tables pull from the same category tables. I know it sounds like a simple query, but here is the actually query im using. $query = "SELECT category2.*, mczippo1.* FROM `category2`,mczippo1 WHERE category2.cat2id = mczippo1.cat2 AND mczippo1.Picture >= '1' AND mczippo1.Avail >= mczippo1.MinQty AND mczippo1.Closeout = 'Y' AND mczippo1.Active = 'S' GROUP BY cat1id"; Its pulling the correct data from items table and sub category2 table. Whoever I dont know how to relate it to the Category1 table. Here is a snap show of some sample tables. I altered the data a little make simple sense. The thing I'm trying to solve is I dont want it to display a MAIN category 1 icon, if there are no items in a sub category of that that are on sale. For example: if theres JACKETS (leather, denim) SHIRTS (long sleeve, short sleeve) GLOVES (cut, full finger), HELMETS (half ,full face), PANTS (denim, leather). If theres no helmets of pants on sale i dont want the icon to show up on the main category page. Cause If there is, then youll have an icon option for Half helmet, or full face helmet. But if there are no helmets on sale then those pages will be empty. So Like I said, so far the query i pulling the correct SALE ITEMS AND SUB CATEGORIES, but im not sure how to link the CAT2ID in category 2 table to the Category1 name in category1 table all in the same query (if its even possible) Or maybe if anybody knows another way. Heres the whole section of code below that im working with here. [pre] $query = "SELECT category2.*, mczippo1.* FROM `category2`,mczippo1 WHERE category2.cat2id = mczippo1.cat2 AND mczippo1.Picture >= '1' AND mczippo1.Avail >= mczippo1.MinQty AND mczippo1.Closeout = 'Y' AND mczippo1.Active = 'S' GROUP BY cat1id"; $data = mysql_query( $query ); $strSQL = mysql_query($query); while ($part = mysql_fetch_array($strSQL)) { $cell++; $cat1 = $part['category1']; $cat1id = $part['cat1id']; $cat1n = str_replace("_", " ", $cat1); //Strip underscore $capcat1 = ucwords($cat1n); // makes first letter of every word uppercase $nscat1 = str_replace(" ", "", $cat1); //Strip space $andnscat1 = str_replace("&", "", $nscat1); //Strip space $findiscat1 = str_replace("&", "&<br>", $capcat1); //replace & with &<br> echo "<td valign='top' align='center'><a href='mcsalesubcategory.php?cat=".$cat1id."'><img src='images/pageall/".$cat1id."icon".$andnscat1.".gif' border='0'><br><font color='#672607'><b>".$findiscat1."</font></a></td></tr>"; } [/pre] I took some stuff out to simplify it and make it a little more readable and to the point. Thanks in advance to anybody who takes a look at this.

OMG, what a dum **** I am, jeez. Just goes to show sometime you need a fresh set of eyes to look at things. Ive been doing this little project for a couple days straight and keep changing things, adding, and taking things off. I totally didnt notice that. Damn, THANKS! - G

ok, I have a problem here. I have a page that i got help from on here. well it works, but for SOME reason .... if you have it update all 33 fields, it changes EVERYTHING in the table with the same information. If i delete half the fields to UPDATE, it works fine, or if i delete the other half of the insert fields to UPDATE it also works fine. So why wont it work with ALL the fields to update. Here is the code with ALL .... <?php mysql_query("UPDATE mczippo1 SET Part2='" . $part_2nd . "', Description='" . $part_stripdesc . "', OnHand='" . $part_onhand . "', Avail='" . $part_avail . "', OO='" . $part_oo . "', Cost='" . $part_cost . "', Retail='" . $part_retail . "', Sale='" . $part_sale . "', Active='" . $part_active . "', Dept='motorclothes', Comments='" . $part_comments . "', Style='" . $part_style . "', Theme='" . $part_theme . "', Cat1='" . $part_cat1 . "', Cat2='" . $part_cat2 . "', Closeout='" . $part_closeout . "', OrderOnly='" . $part_orderonly . "', Core='" . $part_core . "', OBS='" . $part_obs . "', Aftermarket='" . $part_aftermarket . "', MinQty='" . $part_minqty . "', Picture='" . $part_picture . "', Detail='" . $part_stripdetail . "', Size='" . $part_size . "', SizeID='" . $part_sizeid . "', Gender='" . $part_gender . "', Sleeves='" . $part_sleeves . "', Backprint='" . $part_backprint . "', Color='" . $part_color . "', Pocket='" . $part_pocket . "', Weight='" . $part_weight . "', SetupDate='" . date("Y/m/d") . "'") or die(mysql_error()); ?> NOW, if i delete the first half of the UPDATE fields it works fine. SAMPLE CODE BELOW <?php mysql_query("UPDATE mczippo1 SET Dept='motorclothes', Comments='" . $part_comments . "', Style='" . $part_style . "', Theme='" . $part_theme . "', Cat1='" . $part_cat1 . "', Cat2='" . $part_cat2 . "', Closeout='" . $part_closeout . "', OrderOnly='" . $part_orderonly . "', Core='" . $part_core . "', OBS='" . $part_obs . "', Aftermarket='" . $part_aftermarket . "', MinQty='" . $part_minqty . "', Picture='" . $part_picture . "', Detail='" . $part_stripdetail . "', Size='" . $part_size . "', SizeID='" . $part_sizeid . "', Gender='" . $part_gender . "', Sleeves='" . $part_sleeves . "', Backprint='" . $part_backprint . "', Color='" . $part_color . "', Pocket='" . $part_pocket . "', Weight='" . $part_weight . "', SetupDate='" . date("Y/m/d") . "'") or die(mysql_error()); ?> ALSO, if i just use the FIRST half of the UPDATe, it works.. <?php mysql_query("UPDATE mczippo1 SET Part2='" . $part_2nd . "', Description='" . $part_stripdesc . "', OnHand='" . $part_onhand . "', Avail='" . $part_avail . "', OO='" . $part_oo . "', Cost='" . $part_cost . "', Retail='" . $part_retail . "', Sale='" . $part_sale . "', Active='" . $part_active . "'") or die(mysql_error()); ?> I have to go right now, but ill be back to explain more if theres any questions as to what the problem is here. here is a picture showing what happens. AGAIN, this works PERFECTLY if i only use 1 half of the line at a time... never all 33 fields...

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.

just 1 question ... what is the R for ??

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

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

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

no im working on it on the side also ... just dont want to mix too much stuff in this post.

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]

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!

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]

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.