Concatenating in MySQL

Traduim

I am making a script to append some text in a given field. I am using a form where I ask for the text to append and for the field where it must be appended to.

<form action="script.php" method="post">
<textarea name="afegito"></textarea>
<select name="camp">
<option value="nom">Nom</option>
<option value="categoria">Categoria</option>
<option value="descripcio">Descripcio</option>
<option value="comentaris">Comentaris</option>
</select>
</form>

Then, I process the form in script.php with this code:

<?
$result = mysql_query("UPDATE Fitxes SET $camp = CONCAT($camp, '$afegito') WHERE id_fitxa in ($llisteta)");
?>

Obviously, I check for the existance of both $camp and $afegito. And $llisteta always contain a valid list of ids.

The script works fine where the field I edit had content before running the script. But if I try to use Concat() in a field containing NULL, it just don't append anything.

Is there any MySQL way to check if a given field isnull before trying to use Concat? I mean if it is possible to have one only sentence which checks if the given field is null and then proceed with $camp = '$afegito' if it is NULL, or $camp = Concat($camp, '$afegito') if it isn't.

Lars_Berg

<?
$result = mysql_query("UPDATE Fitxes 
SET $camp = CONCAT(coalesce($camp,''), '$afegito') WHERE id_fitxa in ($llisteta)");
?>

Traduim

Thank you very much, but It hasn't worked 🙁

I'll just set NOT NULL all the fields I would need.

herve

Hi,

extract from the MySQL manual :

--- 8< ----------
CONCAT(str1,str2,...)'
Returns the string that results from concatenating the arguments.
Returns NULL' if any argument isNULL'. May have more than 2
arguments. A numeric argument is converted to the equivalent
string form.
mysql> select CONCAT('My', 'S', 'QL');
-> 'MySQL'
mysql> select CONCAT('My', NULL, 'QL');
-> NULL
mysql> select CONCAT(14.3);
-> '14.3'

--- 8< ----------

I don't think it will work, but may be you don't need CONCATE()
call ; like that :

UPDATE Fitxes SET $camp = $camp+$afegito WHERE id_fitxa in ($llisteta)

Hervé.