My first post here and I'm very new (a couple weeks) to codeing and/or DB's.

My DB has three related tables dealing with Vacuum Tubes.

t_TubeEquipLU (includes info on equipment with multiple types of tubes)

t_TubeTypeLU (includes different names for tubes as the same tube can be a 12AU7, 6922, or ECC83 etc.)

Equip_Type_Cross (Pivot table with two fields consisting of pk_Equip and pk_Type with a primary that combines the two.

So, in theory one piece of equipment might have 5 tubes that each have 5 different naming schemes but the pivot table would contain a record that maintains integrity.

EXCEPT...how do I deal with blank, NUll and zero values when updating and inserting equipment? The t_TubeEquipLU has 4 fields for each tube Tube_1, pk_TubeTypeLU_1, Tube_1_Qty, Tube_1_Use AND when it and t_TubeTypeLU have blank fields and I run this:

<?php
$query = "UPDATE t_TubeTypeLU,t_TubeEquipLU
        SET t_TubeEquipLU.pk_TubeTypeLU1= t_TubeTypeLU.pk_TubeTypeLU
        WHERE t_TubeEquipLU.Tube_1= t_TubeTypeLU.Type_US
        Or t_TubeEquipLU.Tube_1= t_TubeTypeLU.Type_Euro
        Or t_TubeEquipLU.Tube_1= t_TubeTypeLU.Type_Mil_1
        Or t_TubeEquipLU.Tube_1= t_TubeTypeLU.Type_Mil_2
        Or t_TubeEquipLU.Tube_1= t_TubeTypeLU.Type_Other";
$result = mysql_query($query); 

?>

for each of the five Tubes in the Equip table I get unwanted values because a piece of equipment may have no tube in slot 4 AND the Type table has NO value for Type_Mil_2 but does for Type_US so it returns that pk.

Do I need to delete the Tube_1 field (currently contains 12AU7 or whatever) and just grab the pk_TubeTypeLU_1 when information is added?

Also, there has to be a better way to update this stuff then running the above query 5 times but I don't see it.

Thanks in advance,

Brad

    Write a Reply...