php variable as a mysql column name

Question

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 ");
        }
                    }

SQL injection attracts sould be with your codes when we use variable directly to the SQl.And Ua are using deprecated mysql — underscore
– underscore, Commented Apr 27, 2013 at 19:01
Dear Your Common Sense, you may read it more carefully before you say anything. I believe I have clearly stated my issue and in the interest of efficiency I indicated what I already tried and did not work. Your lecture is unnecessary and misplaced. — BernardA
– BernardA, Commented Apr 27, 2013 at 19:12
Have you tried using ` around your column names instead of '? — Phill Sparks
– Phill Sparks, Commented Apr 27, 2013 at 19:22
Hi again Phill. Your solution works. I had another problem downstream that was keeping me from getting the correct result. Found it now. t — BernardA
– BernardA, Commented Apr 28, 2013 at 13:41

eggyal · Accepted Answer · 2013-04-27 19:41:24Z

1

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 ...

answered Apr 27, 2013 at 19:41

eggyal

127k18 gold badges220 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

fgryphus · Accepted Answer · 2018-12-18 15:52:24Z

1

You can use a php variable as the name of a SQL column like this.

UPDATE tablename SET `".$variable."`= whatever

answered Dec 18, 2018 at 15:52

fgryphus

134 bronze badges

Collectives™ on Stack Overflow

php variable as a mysql column name

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related