UPDATE only of fields that are longer than 0 characters

Question

UPDATE only of fields that are longer than 0 characters

Navigation

#1 by (2 votes)

0

Hi, I have a table with several inputs and I need to make UPDATE of fields with more than 0 characters, those with 0 or blank are the way it is! Is there any way to do this?

follow 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 many fields to update is very difficult to do with IF, for example:

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

php mysql

asked by anonymous 02.07.2014 / 18:31

1 answer

Button in Html while hovering Change variable in a Task

score 2 · Accepted Answer

What you can do is to simplify the code a bit by organizing the FIs:

<?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 issues:

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 set lines, and WHERE ).
You should use mysqli instead of lib mysql , which is deprecated.

In addition, it might be the case to revise your DB structure instead of putting too much information on the same table row.

A function could be done, with an array containing the field names, that would generate the query for you, but it is the typical effort on a thing that does not justify the work, and that disrupts understanding and increases complexity. Better give copy and paste in a dozen lines and you can see what is happening.