dynamic column name for mysql

[Poddy]

Hi i am trying to update data into mysql where the column name is a variable.. however i cant get it to work with my facebook application for some reason

 

i have the following code that works on a clean page:

 

if(isset($_POST['submit'])){
## Added mysql_real_escape_string to prevent SQL Injection
$q1 = mysql_real_escape_string($_POST['q1']);
$q2 = mysql_real_escape_string($_POST['q2']);
$q3 = mysql_real_escape_string($_POST['q3']);
$q4 = mysql_real_escape_string($_POST['q4']);
$q5 = mysql_real_escape_string($_POST['q5']);
$q6 = mysql_real_escape_string($_POST['q6']);

$qid1 = mysql_real_escape_string($_POST['qid1']);
$qid2 = mysql_real_escape_string($_POST['qid2']);
$qid3 = mysql_real_escape_string($_POST['qid3']);
$qid4 = mysql_real_escape_string($_POST['qid4']);
$qid5 = mysql_real_escape_string($_POST['qid5']);
$qid6 = mysql_real_escape_string($_POST['qid6']);

$sql = " select q_filled from `users` where user_id='$fb_user'";
$result = mysql_query($sql) or die ('error getting data' . mysql_error());
$row = mysql_fetch_assoc($result);
$self = $row['q_filled'];
mysql_free_result($result);

if ($q1 != null) { $self += 1; }
elseif ($q2 != null) { $self += 1; }
elseif ($q3 != null) { $self += 1; }
elseif ($q4 != null) { $self += 1; }
elseif ($q5 != null) { $self += 1; }
elseif ($q6 != null) { $self += 1; }

$sql = " UPDATE `users` SET " . $qid1 . "='$q1', " . $qid2 . "='$q2'," . $qid3 . "='$q3'," . $qid4 . "='$q4'," . $qid5 . "='$q5'," . $qid6 . "='$q6',  q_filled='$self' where user_id='$fb_user'  ";
mysql_query($sql) or die('Error, update query failed' . mysql_error());

 

the connect config is included above.. the error i am reciving is

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '='', ='fgh',='',='',='',='', q_filled='2' where user_id='576173459'' at line 1

 

so the column names aren't going through... any thoughts welcome

[DarkWater]

I think your quotes are horribly mangled.  I can't even glance over that properly without getting like, a headache. >_>  Redo those quotes on the string.

[moselkady]

 

You can print the query string ($sql) and check what was substituted for variables $qid1 through $qid6. Maybe you can post that query string for us along with any mysql errors you may have got.

[craygo]

Try this

 

$sql = " UPDATE `users` SET `$qid1` = '$q1', `$qid2` = '$q2', `$qid3` = '$q3',`$qid4` = '$q4', `$qid5` = '$q5', `$qid6` = '$q6', `q_filled` = '$self' where user_id='$fb_user'  ";

 

Ray

 

[Poddy]

Solved, thank you.

the problem was mainly on the variable interpretation because the system in unix it might case sensitive? the variable had one capped letter... changed it and worked.

also used craygo sql statement for better quotes managing.

thank you