Variable as the field name problem PHP/mysql

[danx30]

Here is the code.

 

$query4 = "UPDATE users SET $id='1' WHERE name='".$n1."'";

 

if I change the $id to a specific field it works fine.  But I need it to be a variable.  Any ideas?

[wildteen88]

The basic format for an UPDATE query is

 

UPDATE table_name SET field_name=new value

 

In order for it to function $id will HAVE to be set to a field name. What are you trying to do? What is $id set to? Maybe your query is supposed to be:

$query4 = "UPDATE users SET id='$id' WHERE name='$n1'";

[danx30]

the $id is a variable that changes depending on who access the page, so it can't be a specific field name.  It needs to be a variable.

[wildteen88]

Can you explain what you're trying to achieve with the query? I am not understanding you.

[JasonLewis]

the $id is a variable that changes depending on who access the page, so it can't be a specific field name.  It needs to be a variable.

 

So you create a new column in a table for every new user that signs up?

[Mark Baker]

$query4 = "UPDATE users SET ".$id."='1' WHERE name='".$n1."'";

Should work, as long as $id is the name of a valid column in the users table

 

[danx30]

There are about 20 columns, all with different names.  When the user hits the php page it will parse a specific column name which is the variable $id.  So I need it to insert a 1 into that column if it's used.  But because there could be any of 20 different ones I have to use a variable.  So I need the field name to be a variable.

[wildteen88]

Well set $id to that particular field you wish to update.

[danx30]

Apprently I am not making myself very clear.  Lets try this again..

 

Someone hits the page and parses a 12 to the $id variable.  Which in my database there is a column named 12..  I need it to insert a 1 into that field.

 

But not everyone will parse a 12, some may parse another number which will have columns already setup for in the database.

[Mark Baker]

Someone hits the page and parses a 12 to the $id variable.  Which in my database there is a column named 12.. 

IIRC, you can't have a column name that begins with a numeric value in mySQL, or most other databases

[wildteen88]

How does 12 get passed to the page? Via the url like

 

filename.php?id=12

 

In which case to get the id from the url you'll use $_GET['id'] like so

 

if(isset($_GET['id']) && is_numeric($_GET['id']))
{
    $id = (int) $_GET['id'];

    $query4 = "UPDATE users SET $id='1' WHERE name='$n1'";

    // rest of code
}

[danx30]

Well that is my whole problem then I think...

[danx30]

Yep, that's my issue.  If I use a number as the field name it doesn't work.  What would the syntax be to add a letter in front of the number, so instead of 12 it would be like a12?

 

[wildteen88]

If you fields named a1 .. to .. a12 and you're using the example code I made above

 

Then you'd do:

if(isset($_GET['id']) && is_numeric($_GET['id']))
{
    $id = 'a'.$_GET['id'];

    $query4 = "UPDATE users SET $id='1' WHERE name='$n1'";

    // rest of code
}