If statement based on row result

[LinusIT]

Hi

This one will be long winded or really simple, hoping the later.

I'm using

$sql="SELECT * FROM pricelist";
$result = mysql_query($sql) or die(mysql_error());

and then

while($row=mysql_fetch_array($result))

to access the results. The code works fine but I'd like to perfect the results.

If the result returned is 0 then I'd like to display POA.

Here's how I display the results for each field.

<?=$row['copper_dry_bright']?>

I'm hoping I can set this globally rather than writing an IF statement for each row as there are quite a few.

Any pointers would be great, thanks.

[rocknbil]

What are the other possible values? Well let's say "display them as is." What I mean is, if it's only a 1 or 0, you can define an array; for example,

$YesNos=Array('No','Yes');

Then you'd be able to do

echo $YesNos[$row['somefield']];

But if the field can be any value . . .like, 0 to a million . . . I think you'll at least need a ternary. But you should be able to set an array for the fields you want to apply that to.

// A better method is to read the table to get all fields, but anyway . . .
$displayfields = Array('id','title','description',copper_dry_bright','field2','field3','field4');

// Field => default value

$needs_default = Array(
'copper_dry_bright' => 'POA',
'field2' => 'DOA',
'field3' = 'LSMFT',
'field4' => 'N/A'
);

while ($row= mysql_fetch_array($result)) { 
 echo "<tr>"; 
 foreach ($displayfields as $fieldname) { 
 $val = (($row[$fieldname]== 0) and array_key_exists($fieldname))?$needs_default[$fieldname]:$row[$fieldname]; 
 echo "<td>$val</td>"; 
 } 
 echo "</tr>"; 
} 

What the heck just happened?

We make a list of all fields we want to display: $displayfields.
We make a list of fields needing default values for zero: $needs_default.
While stepping through rows, we optimize our code with a foreach of the $displayfields array, requiring only 3 lines instead of a long hard coded block dr0pping <?php in?> and <?php out ?> of <?php php ?>.
$val is set under the following conditions: if this field name is in the $needs_default array and the row value for it is zero, use the default value set in the array . . . copper_dry_bright becomes POA, field2 becomes DOA, etc. . . .
. . . otherwise use the $row[$fieldname] value.

[LinusIT]

The possible values are anything numerical, it is a pricelist that I'm working on.

The reason I've done it the long way round is because I wanted to draw the table myself and put the results where I needed them. There might be a better way of doing it.

All I'm trying to achieve is if the result is 0 then display POA, else users might actually quote "It's free".

Just to show you the long winded way I've done it, look at the update pricelist script:

$query = "UPDATE pricelist SET copper_dry_bright='" . $_POST['copper_dry_bright'] . "', copper_greasy_dry_bright='" . $_POST['copper_greasy_dry_bright'] . "', copper_electro='" . $_POST['copper_electro'] . "', copper_stampings='" . $_POST['copper_stampings'] . "', copper_tinned='" . $_POST['copper_tinned'] . "', copper_new_tube='" . $_POST['copper_new_tube'] . "', copper_hard_drawn='" . $_POST['copper_hard_drawn'] . "', copper_98='" . $_POST['copper_98'] . "', copper_no1='" . $_POST['copper_no1'] . "', copper_no2='" . $_POST['copper_no2'] . "', copper_tanks='" . $_POST['copper_tanks'] . "', copper_braziery='" . $_POST['copper_braziery'] . "', copper_clean_pyro='" . $_POST['copper_clean_pyro'] . "', copper_pvc_pyro='" . $_POST['copper_pvc_pyro'] . "', copper_elements='" . $_POST['copper_elements'] . "', copper_electric_motors='" . $_POST['copper_electric_motors'] . "', copper_fridge_motors='" . $_POST['copper_fridge_motors'] . "', copper_high_grade='" . $_POST['copper_high_grade'] . "', copper_household='" . $_POST['copper_household'] . "', copper_lead_armoured_greasy='" . $_POST['copper_lead_armoured_greasy'] . "', copper_low_grade_armoured='" . $_POST['copper_low_grade_armoured'] . "', brass_mixed='" . $_POST['brass_mixed'] . "', brass_rod_solids='" . $_POST['brass_rod_solids'] . "', brass_no1='" . $_POST['brass_no1'] . "', brass_no2='" . $_POST['brass_no2'] . "', brass_cuttings='" . $_POST['brass_cuttings'] . "', brass_copper_rads='" . $_POST['brass_copper_rads'] . "', brass_commercial_pb='" . $_POST['brass_commercial_pb'] . "', brass_gun_metal_solids='" . $_POST['brass_gun_metal_solids'] . "', brass_gun_metal_borings='" . $_POST['brass_gun_metal_borings'] . "', brass_ab_solids='" . $_POST['brass_ab_solids'] . "', brass_ab_swarf='" . $_POST['brass_ab_swarf'] . "', alu_clean_he9='" . $_POST['alu_clean_he9'] . "', alu_painted_he9='" . $_POST['alu_painted_he9'] . "', alu_thermal_break='" . $_POST['alu_thermal_break'] . "', alu_alloy_wheels='" . $_POST['alu_alloy_wheels'] . "', alu_alloy_radiators='" . $_POST['alu_alloy_radiators'] . "', alu_litho_sheets='" . $_POST['alu_litho_sheets'] . "', alu_commercial_pure='" . $_POST['alu_commercial_pure'] . "', alu_poly_cuts='" . $_POST['alu_poly_cuts'] . "', alu_cast_alloy='" . $_POST['alu_cast_alloy'] . "', alu_old_rolled='" . $_POST['alu_old_rolled'] . "', alu_turnings='" . $_POST['alu_turnings'] . "', alu_copper_rads='" . $_POST['alu_copper_rads'] . "', alu_irony_alloy='" . $_POST['alu_irony_alloy'] . "', alu_zinc='" . $_POST['alu_zinc'] . "', ss_188_solids='" . $_POST['ss_188_solids'] . "', ss_188_turnings='" . $_POST['ss_188_turnings'] . "', ss_316_solids='" . $_POST['ss_316_solids'] . "', ss_316_turnings='" . $_POST['ss_316_turnings'] . "', ss_chrome_iron='" . $_POST['ss_chrome_iron'] . "', lead_contaminated_lead='" . $_POST['lead_contaminated_lead'] . "', lead_clean_lead='" . $_POST['lead_clean_lead'] . "', lead_acid_batteries='" . $_POST['lead_acid_batteries'] . "', other_catalytic_converters='" . $_POST['other_catalytic_converters'] . "', other_cars='" . $_POST['other_cars'] . "', other_engines='" . $_POST['other_engines'] . "', iron_no1_no2='" . $_POST['iron_no1_no2'] . "', iron_burnings='" . $_POST['iron_burnings'] . "', iron_cylinder_iron='" . $_POST['iron_cylinder_iron'] . "', iron_light_iron='" . $_POST['iron_light_iron'] . "', iron_pg1='" . $_POST['iron_pg1'] . "', iron_pg2='" . $_POST['iron_pg2'] . "', iron_profile='" . $_POST['iron_profile'] . "', iron_turnings='" . $_POST['iron_turnings'] . "', iron_shearings='" . $_POST['iron_shearings'] . "', last_updated='" . $date . "' WHERE pricelist_id=1";

[rocknbil]

Holy COW man that is a huge select and a wide table! :-) My suggestion will still work, but let's back up a step. The following comes with the advice that it's a **really super uber bad idea** to have your form fields the same as your database fields, a small suggestion below - but first things first, here . . .

$needs_default = Array(
'copper_dry_bright' => 'POA',
'field2' => 'DOA',
'field3' = 'LSMFT',
'field4' => 'N/A'
);

$myfields = Array (
'copper_dry_bright',
'copper_greasy_dry_bright',
'copper_electro',
'copper_stampings',
'copper_tinned',
'copper_new_tube',
'copper_hard_drawn',
'copper_98',
'copper_no1',
'copper_no2',
'copper_tanks,
'copper_braziery',
'copper_clean_pyro',
'copper_pvc_pyro',
'copper_elements',
'copper_electric_motors',
'copper_fridge_motors',
'copper_high_grade',
'copper_household',
'copper_lead_armoured_greasy',
'copper_low_grade_armoured',
'brass_mixed',
'brass_rod_solids',
'brass_no1',
'brass_no2',
'brass_cuttings',
'brass_copper_rads,
'brass_commercial_pb,
'brass_gun_metal_solids',
'brass_gun_metal_borings',
'brass_ab_solids',
'brass_ab_swarf',
'alu_clean_he9',
'alu_painted_he9',
'alu_thermal_break,
'alu_alloy_wheels',
'alu_alloy_radiators',
'alu_litho_sheets,
'alu_commercial_pure',
'alu_poly_cuts',
'alu_cast_alloy',
'alu_old_rolled',
'alu_turnings',
'alu_copper_rads',
'alu_irony_alloy',
'alu_zinc',
'ss_188_solids',
'ss_188_turnings',
'ss_316_solids',
'ss_316_turnings',
'ss_chrome_iron',
'lead_contaminated_lead',
'lead_clean_lead',
'lead_acid_batteries',
'other_catalytic_converters',
'other_cars',
'other_engines',
'iron_no1_no2',
'iron_burnings',
'iron_cylinder_iron',
'iron_light_iron',
'iron_pg1',
'iron_pg2',
'iron_profile',
'iron_turnings',
'iron_shearings'
);

$fields=null; 
foreach ($myfields as $field) { 
 if (isset($_POST[$field])) { 
 $_POST[$field] = ((empty($_POST[$field]) or ($_POST[$field]==0)) and array_key_exists($field))?$needs_default[$field]:$_POST[$field];  
 $fields .= '`' . $fields . '`=\'' . $_POST[$field] . '\','; 
 } 
} 
// Only update if something was set 
if ($fields) { 
 // Normally we'd strip last comma, but since you have another field we don't need to 
 $sql = "update pricelist set $fields last_updated='" . $date . "' WHERE pricelist_id=1";  
 // echo $sql; exit; // for testing 
 echo "updated"; 
} 
else { echo "nothing to update"; } 

About the same field names comment: Hackers are hoping you're doing what you're doing so they can identify more details about your DB. Using an array like above as a starting point, you'd create an associative array instead:

$myfields = Array (
'cpdb' => 'copper_dry_bright',
'cpgdb' => 'copper_greasy_dry_bright',
// exc.
)

Then use the keys in your form,

<input type="text" name="cpdb" id="cpdb">

and in submit access the field names by the values.

$fields=null; 
foreach ($myfields as $field => $databasefield) { 
 if (isset($_POST[$field]) { 
 $fields .= '`' . $databasefield . '`=\'' . $_POST[$field] . '\','; 
 } 
} 

But that's something for your next revision, I think . .. still important.

[LinusIT]

Sorry for the late reply!

The code you've given above, does that replace the massive update script?

I'll be changing the form field names to be more secure, although this system is on a local network. Better to be safe than sorry.

[rocknbil]

Put it in a test file and try it out, it hasn't been debugged but should work. Look at what it does: instead of a long error-prone hard coded string, it takes all the table fields and builds the select based on the fields in the array in a short foreach. An even better way to do it so you don't need an array is to use some method to just get all the table fields of a given table. Then if the table changes, your programming doesn't need to.

You could parse out the rows from a "describe table" select or some other method.