using php variables as a MySQL column name

[Snewzzer]

Hi, This is my first posting on a forum so hope I don't break too many (none hopefully!) etiquette rules. I am new to both PHP and MySQL and would appreciate any help that could be given with my problem.

 

I have a form that posts values (mark1, mark2, etc) to the script that I am having the problem with. This script then uses a FOR loop to assign these into an array - $servicearr[ ] in this case. This all works fine and I can echo these values to test them.

 

I have also used a FOR loop to assign my MySQL column names into an array - $colarr[ ] in this case. Again this works and the values can be echoed to test it.

 

The problem occurs when using an UPDATE query, it doesn't understand the column name variables. The script is below :-

 

<?php

SESSION_START();

require("servicedb_con.php");

 

if(!$_SESSION['UserIsLoggedIn'])

    {

    header("location:1_techloginform.php");

    die();

    }

 

$fleetnum = $_SESSION['FleetNo'];

 

for($var=1;$var<300;$var++)

    {    $servicearr[$var] = $_POST['mark'.$var];    }

 

$sql = "SELECT * from truck_serv";

$result = mysql_query($sql);

 

for($var=1;$var<300;$var++)

    {    $colarr[$var] = mysql_field_name($result,$var);    }

 

for($var=1;$var<300;$var++)

{

if(!$servicearr[$var] == NULL)

    {

echo $colarr[$var]."<br/>";

    $sql = mysql_query('UPDATE truck_serv SET seatbelt = "'.$servicearr[$var].'" WHERE Fleet_No = "'.$fleetnum.'"');

    if(!$sql)

        {

        die('Could not query: '. mysql_error());

        }

    }

}

 

header("location:8B_truck_serv.php");

 

?>

 

The error I get is :-

Could not query: 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 '".$colarr[$var]." = "S" WHERE Fleet_No = "DK07 CKA"' at line 1

 

Fleet_No is a column name and DK07 CKA its value (these are vehicles).

"S" is the correct value (my code for serviceable) supplied from the post form.

The only problem I can see is that an extra apostrophe has crept in at the start of my column name variable i.e. '".$colarr[$var]."

I have retyped the script completely and get the same error.

If I manually insert the column name it works fine. The trouble with that is I would have to write the same code out 300 times with a different column name in each time as the script update a vehicle servicesheet database .

 

Hope someone can help.

Thanks

 

 

[Mchl]

Are you sure this is the query that throws this error?

 

As to breaking rules, please read them here:

http://www.phpfreaks.com/page/rules-and-terms-of-service

 

(using

tags around code you paste here is strongly encouraged)

[Snewzzer]

SORRY, many apologies, error in code supplied...

 

$sql = mysql_query('UPDATE truck_serv SET seatbelt = "'.$servicearr[$var].'" WHERE Fleet_No = "'.$fleetnum.'"');

 

This should read :-

 

$sql = mysql_query('UPDATE truck_serv SET " '.$colarr[$var].' " = " '.$servicearr[$var].' " WHERE Fleet_No = " '.$fleetnum.' " ');

 

and yes, this part of the code is the problem as when the column name is 'seatbelt' it works, when it uses the variable it doesn't.

[Mchl]

This doesn't seem wrong either. Can you change it to:

 

$query = 'UPDATE truck_serv SET " '.$colarr[$var].' " = " '.$servicearr[$var].' " WHERE Fleet_No = " '.$fleetnum.' " ';
$sql = mysql_query($query) or trigger_error(mysql_error(). ": $query",E_USER_WARNING);

[Snewzzer]

Problem solved... code should be :-

$sql = 'UPDATE truck_serv SET '.$colarr[$var].'= "'.$servicearr[$var].'" WHERE Fleet_No = "'.$fleetnum.'"';

....thanks for your input.

[Mchl]

Of course! Now I can see it too