How to solve the "comma problem" in an elegant way?

Question

This is a problem I solved a lot of times in different ways, but never could quite get an elegant solution for.

I have an SQL string which I want to build:

$sql = "UPDATE table";
$sql .= "SET"; 
$sql .= "sqlTableName = 'Y', column2 = value2";

now, based on

isset($_GET['value1'])

I want to add:

$sql .= "sqlTableName = 'Y',"

and based on

isset($_GET['value2'])

I want:

$sql .= "sqlTableName2 = 'Y',"

to be added.

If I put the comma before or after the string I run into trouble.

Now I know how to solve it but how to do this elegantly?

this is known as a "fencepost" problem, FYI. – pkamb Dec 13 '19 at 21:18 — pkamb, Dec 13 '19 at 21:18

Quentin · Accepted Answer · 2019-12-13T21:36:31.083

Put the values in an array, then use an array join method. The specific syntax would depend on which programming language you are using.

The tags you selected are not helpful at all in determining what that is but it looks like you are using PHP.

<?php
    $values = [ $_GET['value1'], $_GET['value2'] ];
    print join(", ", $values);

(Obviously, since the number of values in the array is variable for your case, you would generate it dynamically, probably using a push method, and sticking user input directly into SQL is dangerous).

score 1 · Answer 2 · answered Dec 13 '19 at 21:44

You can build an array of all the fields to be updated and then implode them together.

$sql = "UPDATE table SET "; 

$setArray = [];
if(isset($_GET['value1'])){
    $setArray[] = "sqlTableName = 'Y'";
}
if(isset($_GET['value2'])){
    $setArray[] = "sqlTableName2 = 'Y'";
}

echo $sql.implode(', ', $setArray);

This is very simple when you have hardcoded values. It gets more difficult with user input, which must be parameterized. You need to collect the values in a separate table.

Here is an example, how this would be achieved with PDO.

$setFields = [];
$setValues = [];
if (isset($_GET['value1'])) {
    $setFields[] = "sqlTableName = ?";
    $setValues[] = $_GET['value1'];
}
if (isset($_GET['value2'])) {
    $setFields[] = "sqlTableName2 = 'Y'";
    $setValues[] = $_GET['value2'];
}

$pdo = new PDO($dsn, $user, $pass, [
    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
    \PDO::ATTR_EMULATE_PREPARES => false
]);

$pdo->prepare("UPDATE table SET ".implode(', ', $setFields))->execute($setValues);

I would like to get some feedback for the downvote. What should I improve? — Dharman, Dec 13 '19 at 21:59

How to solve the "comma problem" in an elegant way?

2 Answers2