UPDATE only fields that are more than 0 characters long

Question

Hi, I have a table with several inputs and I need you to update the fields with more than 0 characters, the ones with 0 or blank are the same as this! There’s some way to do it ?

follows my code:

$query = mysql_query("UPDATE `is` SET `janmembros` = '$janmembros', `janvisitantes` = '$janvisitantes', `fevmembros` = '$fevmembros', `fevvisitantes` = '$fevvisitantes',  `marmembros` = '$marmembros', `marvisitantes` = '$marvisitantes', `abrmembros` = '$abrmembros', `abrvisitantes` = '$abrvisitantes', `maimembros` = '$maimembros', `maivisitantes` = '$maivisitantes', `julmembros` = '$julmembros', `julvisitantes` = '$julvisitantes', `junmembros` = '$junmembros', `junvisitantes` = '$junvisitantes', `agomembros` = '$agomembros', `agovisitantes` = '$agovisitantes', `setmembros` = '$setmembros', `setvisitantes` = '$setvisitantes', `outvisitantes` = '$outvisitantes', `outmembros` = '$outmembros', `novmembros` = '$novmembros', `outvisitantes` = '$outvisitantes', `novmembros` = '$novmembros', `novvisitantes` = '$novvisitantes', `dezmembros` = '$dezmembros', `dezvisitantes` = '$dezvisitantes' WHERE `celula` LIKE '$celula'") or die(mysql_error());

because there are many fields to update is very difficult to do with IF, example:

if (strlen($janmembros) > 0)
{
    $query = mysql_query("UPDATE `is` SET `janmembros` = '$janmembros' WHERE `celula` LIKE '$celula'") or die(mysql_error());
}

Answer 1

What you can do is to simplify the code a little by organizing the Ifs:

<?php

   $sets = '';
   $sets .= strlen( $janmembros    ) > 0 ? "SET janvisitantes = '".$janmembros   ."'";
   $sets .= strlen( $janvisitantes ) > 0 ? "SET janmembros    = '".$janvisitantes."'";
   $sets .= strlen( $fevmembros    ) > 0 ? "SET fevmembros    = '".$fevmembros   ."'";
   ... outros sets ...

   mysql_query( "UPDATE `is` ".$sets." WHERE `celula` LIKE '$celula'") or die(mysql_error())

?>

Note that your code has at least two serious problems:

• One is the possibility of SQL Injection, which can be solved with this syntax:

  $sets.=strlen($janmembros)>0?"SET janvisitantes='".mysql_real_escape_string($janmembros)."'";

  (on all lines of the set, and on WHERE).

• You should use mysqli in place of lib mysql, outdated.

Also, it might be the case to review the structure of your DB instead of putting too much information in the same row of the table.

_{Could be done a function, with a array containing the names of the fields, that would generate the query for you, but it is the typical effort on something that does not justify the work, and that hinders more the understanding and increases the complexity. Better give copy and Paste in a dozen lines and can see what’s happening.}