Update mysql table from a single input to different columns

Question

I have a table called dircode:

CREATE TABLE dircode (
  jobcodeid int(11) NOT NULL AUTO_INCREMENT,
  jobcodeserial varchar(255) NOT NULL,
  jobcode1 varchar(255) NOT NULL,
  codestatus1 varchar(60) NOT NULL,
  jobcode2 varchar(255) NOT NULL,
  codestatus2 varchar(60) NOT NULL,
  jobcode3 varchar(255) NOT NULL,
  codestatus3 varchar(60) NOT NULL,
  jobcode4 varchar(255) NOT NULL,
  codestatus4 varchar(60) NOT NULL,
  PRIMARY KEY (jobcodeid)
)

A user may have upto 4 job codes for single serial number. When a user inputs a dir serial number and one code, I want that particular job's codestatus to be updated to 'Used'. The rest of the data should remain intact. For example, when a user enters a serial number and jobcode3 the status of codestatus3 should get updated to 'Used'. Please help me in writing PHP code for the above query.

score 0 · Answer 1 · edited May 23 '17 at 10:09

As you said, you need to UPDATE the row.

You are trying to first find out if the entered code is 1, 2, 3 or 4 (e.g. jobcode3) and then set the status of that code (in this case codestatus3). To do that, I would (or wouldn't, see below) get the whole row as an associative array and figure the index out in your code (check each jobcode, find the code and extract the index from the column name).

Then connect to the MySQL server and execute a query like this:

$columName = "codestatus" . $theCorrectIndex;
mysql_query(sprintf("UPDATE dircode SET %s = 'Used' WHERE jobcodeid = %d",
                      $columName,$dircodeId));

Check the boolean return. If it's false, examine the details on the error.

(Alternatively, you could put all this inside your SQL query, see this question or this and MySQL's control flow functions.)

This is not a very good solution. You might want to think about creating another table codes with columns id, status and foreign key column dircode_id so you avoid putting indices into your column names (usually not a good idea).

Also, there is a lot of documentation on this around (MySQL, PHP, SO).

Update mysql table from a single input to different columns

1 Answers1