PHP & mySQL Upload Question?

[olimits7]

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

[rocknbil]

This is not a PHP issue, it is a database table structure issue, and he/she would be correct. Look at your database tables:

UPC - varchar (255) not null
VendorID - int(11) not null default 0 (or no default)
VendorPrice - decimal (12,2) not null default '0.00' (or no default)
InventoryLevels - int(11) not null default 0 (or no default)

(Even if you don't set a default on an integer field, it will still return null or 0, the point is, it holds a place in the row.)

When you insert records, you insert a row for the entire recordset. So inserting a row without the inventory will set inventory to 0 . . .

insert into table (UPC,VendorID,VendorPrice) values ('K125','2345356','29.95');

A query on that row still gives you all columns:

select * from table where UPC='K125';

UPC ¦ VendorID ¦ VendorPrice ¦ Inventory 
K125 ¦ 2345356  ¦ 29.95    ¦ 0 

But there is a way around it. It just depends on what you're doing with this data. You can either import records into two other tables (a pain, and to what end?) Or manipulate the data differently on reading it (if value == 0 do nothing, if not, do something . . . ) In any case, the part about what to do with those zero values IS a programming task.

[olimits7]

Hi,

Thank you for your reply...

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

olimits7

[rocknbil]

I think I'm following you . . . it's not that complex. Here is what I'd do (but this is just me . . . .)

I wouldn't make program logic decisions based on the file type you're uploading, I'd make them based on what is or is not present in the uploaded file.

When uploaded, we are expecting 4 fields, any of which may or may not be present (UPC and vendor id are presumed to always be present, if not, we skip the line.)

So you open the file, split the 4 values. How you insert, and what you insert, will decide how to build the select statement.

1. If it finds the the UPC# in the "vendors" table it will just update either the "VendorPrice" or "InventoryLevels" column data ..... nd it will leave the other column data which is not included in the file type with no change made to it.

So we split up your upload line by line, store the four values in variables $upc, $vendor, $vendor_price, $inventory_levels.

Some mechanism goes out to the table it's inserting, whatever that table is, and looks for a match on upc. For simplicity, we'll store this in a variable "$target_table" which would have either the vendor table name or products table name as it's value, and presume the field names are the same for this table.

if (($upc != '') and ($isInTargetTable==1)) { 
 // Start the select statement 
 $select = "update $target_table set"; 
 // Add the vendor price if in acceptable range 
 if (($vendor_price != '0.00') and ($vendor_price !='')) { 
  $select .= " vendorPrice='$vendor_price'"; 
 } 
 // If inventory levels are in acceptable range, 
 if (($upc != '') and ($inventory_levels > 0)) { 
   // if price was added, we need a comma 
   if (($vendor_price != '0.00') and ($vendor_price !='')) { 
    $select .= ", "; 
   } 
 // add inventory to select 
   $select .= " inventoryLevels='$inventory_levels'"; 
  } 
  // Now add the where . . .  
  $select .= " where UPC='$upc'"; 
 } 

Something is awry here, I can't imagine why you'd have UPC's, which are a subset of a vendor, in the vendor table. But given the parameters, this is one solution . . .

So the possible outcomes of this snippet are:

update vendor_table set vendorPrice='29.95' where UPC='K125';
update vendor_table set vendorPrice='29.95',inventoryLevels='123' where UPC='K125';
update products_table set vendorPrice='29.95' where UPC='K125';
update products_table set vendorPrice='29.95',inventoryLevels='123' where UPC='K125';

The above logic does not cover this part

It will add the new UPC# to the "vendors" table

But the logic would be similar, you just build selects dynamically based on what data is present.

HOWEVER - let us examine something I don't think you're getting in point two:

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.

The first solution works for an update, and will leave existing values alone. That is, in your vendors table, you have

UPC ¦ VendorID ¦ VendorPrice ¦ inventoryLevels  
K125 ¦ 2345356 ¦ 29.95  ¦ 34 

If the uploaded values are null or 0, it will not touch "29.95" or "34" in this record. But what you must undestand is when adding a record, if there's no value for a column, it's going to add a null or zero value. So,

...It will add the new UPC# to the "vendors" table and add either the "VendorPrice" or "InventoryLevels" column data;...

Can only have one of two possible outcomes (four, when applied to either table:)

insert into table (UPC,VendorID,VendorPrice) values ('K125','2345356','29.95');

UPC ¦ VendorID ¦ VendorPrice ¦ Inventory  
K125 ¦ 2345356 ¦ 29.95  ¦ 0  

insert into table (UPC,VendorID,inventoryLevels) values ('K125','2345356','34');

UPC ¦ VendorID ¦ VendorPrice ¦ inventoryLevels  
K125 ¦ 2345356 ¦ 0.00  ¦ 34  

You can apply the same logic in doing an add - build the select statement dynamically based on whether or not values are in an acceptable range - but when adding a record, it has to have something for that column. You can say "if not present, this table only has three columns" - it still has four columns and something has to go in the column with missing data.