PHP/mySQL: How do a concatenate a variable in a mysql query?

Question

What is the proper way to concatenate text and a variable in PHP inside a mysql_query? Here is my attempt:

page.'$pageID'

I want it to output page3.

Here is all of the code (simplified to focus on the mysql_query):

if ($_POST['pageProgress']) {
        $pageProgress = $_POST['pageProgress'];
        $pageID = 3;
        $userID = 1;
        $updateUserProgress = mysql_query("UPDATE test SET page.'$pageID'='$pageProgress' WHERE userID='$userID'") or die(mysql_error());
    }

All of the code works perfectly if I simply replace page.'$pageID' with page3.

Please read about SQL injections and prepared statements in PHP. You are about to add a huge security vulnerability to your code. — Philippe Gerber, Nov 22 '11 at 19:49

score 9 · Accepted Answer · answered Nov 22 '11 at 19:52

9

You do not need the .. PHP parses double quoted (") strings and replaces the variables with their values. As such:

$pageID = 3;
echo "UPDATE test SET page$pageID = '$pageProgress' WHERE userID = '$userID'";

http://codepad.viper-7.com/uIdqqH

answered Nov 22 '11 at 19:52

Alex Turpin

46,743
23
113
145

While `page" . $pageID . "` does work, I prefer your method. I think it is much cleaner and it works! – Mark Rummel Nov 22 '11 at 19:57
I also find it much cleaner than the mess with the dot operator. Remember though that it will only work with double quoted string like `"this"` and not single quoted string like `'that'`. – Alex Turpin Nov 22 '11 at 20:11

score 2 · Answer 2 · answered Nov 22 '11 at 19:50

2

The problem is that your .'$pageID' is inside the double-quoted string; you don't concatenate this on the MySQL side; it gets parsed long before MySQL ever sees it.

It might be that you were trying to escape the field name for Mysql, in that case, you use backticks.

Try:

'UPDATE test SET `page'.$pageID.'`=\''.$pageProgress.'\' WHERE...'

Or, much easier on the eyes:

"UPDATE test SET `page{$pageID}`='{$pageProgress}' WHERE..."

answered Nov 22 '11 at 19:50

Kato

40,352
6
119
149

I haven't tried this solution yet. I went with Xeon06's solution: `page$pageID`. I think it is a bit cleaner. Thank you for your answer though! – Mark Rummel Nov 22 '11 at 20:01
If it's useful, you can give it a +1 even if you accept Xeon's answer, which is also fine. You should get in the habit of escaping your field names in MySQL with backticks, and also need to give serious thought to @Phillippe_Gerber's notes about escaping your variables (check out [mysql_real_escape_string()](http://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&ved=0CCAQFjAA&url=http%3A%2F%2Fphp.net%2Fmanual%2Fen%2Ffunction.mysql-real-escape-string.php&ei=qwDMTtbOA-Sg2AXSjKnIDw&usg=AFQjCNHeZ7lbY5LQwaxER94luYHu89AmpA)) – Kato Nov 22 '11 at 20:06
Thank you for your additional comment. I understand using `mysql_real_escape_string()`, but what is the purpose of escaping the field names? – Mark Rummel Nov 22 '11 at 20:10
1

When you get into utilizing variables in place of hard coded text, escaping is always a great idea. You might later expand this to work against multiple tables, import a table from a SQL Server or Oracle setup, or unassumingly add a reserved word as a field name (like `case` or `current_user` or `group`). These are easy to find if your field name is hard coded, since you will know the first time you run the function. However, with a variable field name, you may not discover this until production when the name fails (unless it is escaped). – Kato Nov 22 '11 at 20:27
I run into this issue frequently with products that support multiple databases (it becomes difficult to find field names that don't conflict with at least one). – Kato Nov 22 '11 at 20:28

score 1 · Answer 3 · answered Nov 22 '11 at 19:46

1

"UPDATE test SET page".$pageID."='".$pageProgress."' WHERE userID='".$userID."';"

Dots are in the wrong spot to do it with PHP's string functions.

answered Nov 22 '11 at 19:46

TelsaBoil

584
1
6
14

score 1 · Answer 4 · answered Nov 22 '11 at 19:48

1

Something like this.

mysql_query("UPDATE test SET page" . $pageID . " = '" . $pageProgress . "' WHERE userID = " . $userID)

answered Nov 22 '11 at 19:48

Andreas Wederbrand

38,065
11
68
78

This does work, but I prefer Xeon06's solution: `page$pageID`. Thank you for your solution! – Mark Rummel Nov 22 '11 at 19:58

score 0 · Answer 5 · answered Nov 22 '11 at 19:46

0

Try

mysql_query('UPDATE test SET page'.$pageID.'='.$pageProgress.' WHERE userID='.$userID)

answered Nov 22 '11 at 19:46

Pateman

2,727
3
28
43

I tried using your method with single quotes, but it didn't work for me. It did work though with double quotes. I used Xeon06's solution: `page$pageID`. Thank you for your answer though. – Mark Rummel Nov 22 '11 at 20:00

score 0 · Answer 6 · answered Nov 22 '11 at 19:46

0

$updateUserProgress = mysql_query("UPDATE test SET page".$pageID." = '".$pageProgress."' WHERE userID='".$userID."'") or die(mysql_error());

@Marc B ; that's not the question..

answered Nov 22 '11 at 19:46

Luceos

6,629
1
35
65

1

Correct! I like the way you think. – TelsaBoil Nov 22 '11 at 19:49
This does work, but I prefer Xeon06's solution: `page$pageID`. Thank you for your solution! – Mark Rummel Nov 22 '11 at 19:59

score 0 · Answer 7 · answered Nov 22 '11 at 19:53

0

You don't need to concatenate anything. you do need to sanitize your variable from post though.

   if ($_POST['pageProgress']) {
    $pageProgress = mysql_real_escape_string($_POST['pageProgress']);
    $pageID = 3;
    $userID = 1;
    $updateUserProgress = mysql_query("UPDATE test SET page$pageID='$pageProgress' WHERE   userID='$userID'") or die(mysql_error());
    }

answered Nov 22 '11 at 19:53

AR.

1,935
1
11
14

Thank you for your answer! It will be sanitized! – Mark Rummel Nov 22 '11 at 20:06

PHP/mySQL: How do a concatenate a variable in a mysql query?

7 Answers7

Linked