CODEWITHSUNDEEP
phpmysql
Boris
Boris

Reputation: 749

MYSQL CASE problem with string

Guys can you give me any tip why the string values are not passed to the query. Here is the code:

Array to be used in the query:

Array ( [16] => 41 [17] => 0 [18] => 0 [19] => 0 [20] => 0 [21] => 0 [22] => 0 [23] => 0 [24] => 0 [25] => 0 [26] => 0 [27] => 0 [28] => 0 [29] => buu [30] => bauadaskd )

Code:

foreach ($arrAnswers as $id => $answer)
{   $update .= sprintf("WHEN '%d' THEN '%d' ", $id, $answer);
}
echo $update .= "END WHERE a_question IN ($ids) AND a_user = '{$_SESSION['current_user_id']}'";

Output

UPDATE answers SET a_answer = CASE a_question WHEN '16' THEN '41' WHEN '17' THEN '0' WHEN '18' THEN '0' WHEN '19' THEN '0' WHEN '20' THEN '0' WHEN '21' THEN '0' WHEN '22' THEN '0' WHEN '23' THEN '0' WHEN '24' THEN '0' WHEN '25' THEN '0' WHEN '26' THEN '0' WHEN '27' THEN '0' WHEN '28' THEN '0' WHEN '29' THEN '0' WHEN '30' THEN '0' END WHERE a_question IN (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30) AND a_user = '19

As you can see index 29 and 30 are equal to 0 in the output even if they have their own value in the array.

Upvotes: 0

Views: 64

Answers (3)

piotrp
piotrp

Reputation: 3874

You have wrong formatting string in your sprintf() call, use %s for strings. %d is for numbers:

$update .= sprintf("WHEN '%d' THEN '%s' ", $id, $answer);

Upvotes: 2

J0HN
J0HN

Reputation: 26941

You are trying to format it as integers with %d. You should use %s for strings:

$update .= sprintf("WHEN '%d' THEN '%s' ", $id, $answer);

Upvotes: 2

ChrisK
ChrisK

Reputation: 1402

Your issue is here

$update .= sprintf("WHEN '%d' THEN '%d' ", $id, $answer);

Should be 

    $update .= sprintf("WHEN '%d' THEN '%s' ", $id, $answer);

Upvotes: 2

Related Questions