MySQL UPDATE query using field names/values from array

[RLJ]

Hi all,

 

I have the following MySQL insert query:

$insert= mysql_query   ("INSERT INTO tablename (column1,`".$EXPfields."`) VALUES ('$something','".$EXPvalues."')"); 

where $EXPfields is an array of table-field-names and $EXPvalues is an array of table-field-values.

 

Now I want to write an equivalent query, but using UPDATE instead of INSERT INTO, but I don't want to write out all the field names/values separately, but again want to use $EXPfields and $EXPvalues.

 

So something like this:

$update = mysql_query ("UPDATE tablename
                     SET (column1,`".$EXPfields."`) = ('$something','".$EXPvalues."') 
                     WHERE ....
                  ");

 

Is this possible? If so, what is the proper syntax? Thanks!

 

 

[harristweed]

I don't think mysql can use PHP arrays. You will need to build the query outside of mysql. Have a look at the PHP 'implode' function.

[RLJ]

Yeah sorry, $EXPfields and $EXPvalues are actually imploded arrays. But how do I use them with UPDATE?

[mattal999]

$update = mysql_query ("UPDATE tablename
                     SET column1 = '$something', `".$EXPfields."` = '".$EXPvalues."' 
                     WHERE ....
                  ");

[RLJ]

Thanks for the reply, that was simpler than I thought! I'm getting the following error message though:

 

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 ' `EXP2`, `EXP3` = 'Communications', 'Electronics', 'Engineering' ' at line 2

 

Where in this case $EXPfields is as follows: EXP1`, `EXP2`, `EXP3

and $EXPvalues is as follows: Communications', 'Electronics', 'Engineering

 

So somehow the field EXP1 is getting cut off, can you see why? (I'm afraid I'm new to MySQL)

Thanks!

[RLJ]

O and just to be clear, the exact query I am using is:

$edit2b= mysql_query   ("UPDATE tablename
                             SET `".$EXPfields."` = '".$EXPvalues."'
                             WHERE ID = '$ID'");

[mattal999]

The syntax for an update query is as follows:

 

UPDATE table_reference

    SET col_name1=expr1 [, col_name2=expr2] ...

    [WHERE where_condition]

    [ORDER BY ...]

    [LIMIT row_count]

 

So in your case it would be:

 

UPDATE tablename
    SET EXP1 = 'Communications', EXP2 = 'Electronics', EXP3 = 'Engineering'
    WHERE ID = '$ID'

 

So now you just have to modify the way your $EXPfields and $EXPvalues strings are set and you're good to go.