olimits7 Posted January 13, 2009 Share Posted January 13, 2009 Hi, Ok, for all the PHP & mySQL experts I have a question. I currently upload my vendor prices & inventory levels to a mySQL table through a PHP page. I use a *.txt file with the following column headings for the upload: UPC#, VendorID, VendorPrice, InventoryLevels. I have a programmer working on this but I want to make sure he is correct on this issue. Depending on the vendor; some send me files with just the UPC# and InventoryLevels, some send me files with just the UPC# and VendorPrice, and other send me all the information in one file. So I need my upload to support 3 different file type uploads: - UPC#, VendorID, VendorPrice, InventoryLevels - UPC#, VendorID, VendorPrice - UPC#, VendorID, InventoryLevels Currently, the 1st file type with all the vendor information works fine. However, when I use the file with just the "InventoryLevels" it makes all the data in the VendorPrice change to 0, and if I use the file with just the "VendorPrice" it makes all the InventoryLevels change to 0. I'm thinking can't he just write code to say for example; if the "VendorPrice" column heading name is not found in the *.txt file it will ignore this column and just update records according to the UPC# and InventoryLevels? He's telling me if the *.txt file doesn't have the price column included it will make all the data in the price column in mySQL to 0, but there has to be a way around this. It can't be this difficult to do... Thank you, olimits7 Quote Link to comment Share on other sites More sharing options...
premiso Posted January 13, 2009 Share Posted January 13, 2009 He is right and wrong. It will make any data that is being imported to 0 or set to the default of the table. Since that column is in the DB it has to be entered or it is defaulted to the value. You could set it to NULL instead of 0 in the default definition, which might be more versatile, but yea. Quote Link to comment Share on other sites More sharing options...
olimits7 Posted January 14, 2009 Author Share Posted January 14, 2009 Thank you for your reply. Then if I set the default definition to NULL then he can code the upload script to leave either the "VendorPrice" or "InventoryLevels" columns as is depending on which file I use?? So if I upload a "UPC#, VendorID, VendorPrice" file type; it will leave the current values I have in the "InventoryLevels" column the same?? So if I upload a "UPC#, VendorID, InventoryLevels" file type; it will leave the current values I have in the "VendorPrice" column the same?? Thank you, olimits7 Quote Link to comment Share on other sites More sharing options...
Philip Posted January 14, 2009 Share Posted January 14, 2009 Given that the file is an UPDATE to the others, yes, your programmer would be able to leave the others alone (if coded correctly) However, if the products in the file are NEW (and not updates), then the VendorPrice or InventoryLevels would remain at NULL until you, or another query overwrote that. Quote Link to comment Share on other sites More sharing options...
olimits7 Posted January 14, 2009 Author Share Posted January 14, 2009 Ok, yes I think that's how I thought it would work. My current script first looks to see if it can find the UPC# in the "products" table; if it does find the UPC# it then adds the [uPC#, VendorID, VendorPrice, InventoryLevels] to my "vendors" table. If it doesn't find the UPC# it doesn't import any of the values to the "vendors" table. So I'm thinking for the other 2 file types, the file types that are giving me problems, because I don't have either "VendorPrice" or "InventoryLevels" column listed: 1. If it finds the the UPC# in the "vendors" table it will just update either the "VendorPrice" or "InventoryLevels" column data; depending on which file type I'm uploading. And it will leave the other column data which is not included in the file type with no change made to it. This way my current values that I have in that column don't get messed up. 2. Now if it doesn't find the UPC# listed in the "vendors" table and I have it listed in my "products" table. It will add the new UPC# to the "vendors" table and add either the "VendorPrice" or "InventoryLevels" column data; depending on which file type I'm uploading. And it will leave the other column data which is not included in the file type as NULL or 0. Then when I upload the other file type which has the correct information for the column data it will update the NULL or 0 values from above; and the other column data at this point would be left alone. So does what I wrote in 1 & 2 above possible?? To me this makes sense and sounds pretty straight forward, but when I go over this with my programmer it doesn't seem like this is possible. Thank you, agaain! olimits7 Quote Link to comment Share on other sites More sharing options...
Philip Posted January 14, 2009 Share Posted January 14, 2009 Both are possible. Quote Link to comment Share on other sites More sharing options...
olimits7 Posted January 14, 2009 Author Share Posted January 14, 2009 Ok, thank you. I just wanted to make sure; so now I can try explaining this theory to my programmer again. olimits7 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.