I tried to find solution for this about everywhere, but nothing Google gave seems to work for my specific case.
My table has 4 columns that I want updated that I have named 0, 1, 2, 3, conveniently as I have to loop through it to update. Here is one instance of the column structure :
column name 0 ENUM('Y','N') null=no default='N'
Now I want to set the value to 'Y' using this loop, but it will not accept the way I am referring to the $i variable within the query.
See below my last attempt. I also tried to cast $i as int, but that did not work either.
I appreciate your guidance on this.
/* update versions columns to Y where applicable */
for ($i=0;$i <= $ctvid-1;$i++){
$sql = mysql_query (
"SELECT trim_id
FROM versiontrim
WHERE (
version_id =$arr_vid[$i]
)");
$escapedi=mysql_real_escape_string($i);
while ($row1 = mysql_fetch_assoc( $sql ))
{
$r=$row1['trim_id'];
mysql_query ("UPDATE ttcomp SET '".$escapedi."' ='Y' where trim_id=$r ");
}
}
As documented under Schema Object Names:
Permitted characters in unquoted identifiers:
[ deletia ]
- Identifiers may begin with a digit but unless quoted may not consist solely of digits.
[ deletia ]The identifier quote character is the backtick (“
`”):
Therefore, if you must name your columns in this way (and, really, you should find some other solution), you will need to quote them with backticks:
UPDATE ttcomp SET `0` = 'Y' WHERE ...
You can use a php variable as the name of a SQL column like this.
UPDATE tablename SET `".$variable."`= whatever