mysql prepared statement parameters and ordering queries

Question

I have a form, the purpose of which is to place the currently displayed record into a category. I am using the following html code via php to do so:

<form name="categoryForm">
  <input name="radiobutton" type="radio" value="fakeapproved" />Fake (Approved)<p>
  <input name="radiobutton" type="radio" value="fakesuspected" />Fake (Suspected)<p>
  <input name="radiobutton" type="radio" value="keyword" />Forbidden Keywords<p>
  <input name="radiobutton" type="radio" value="parallelimport" />Parallel Imports
  <input name="Submit" type="submit" value="Update" onclick="handleClick(".$pk.");return false"/>
</form>

At the moment, I simply have an AUCTIONS table, with a category column, and this column is set to one of the categories defined in my form.

This approach is not effective for what I need to do with the data, so I am planning to change it to have a separate column for each category, which can be set to either true or false.

What I would like to know, is if it is possible to use the text defined in my form and obtained via my javascript function, in my sql query.

For example, update auctions set $textfromfrom = true

At the moment, I am using the following prepared statement:

if($cmd=="addcat"){
  $alterQuery = "UPDATE auctions SET category = ? WHERE article_no= ?";
  if ($altRecord = $con->prepare($alterQuery)) {
    $altRecord->bind_param("ss", $subcat, $pk);
    $altRecord->execute();
    $altRecord->close();
    echo "true";
  } else {
    echo "false";
  }
}

Is there a way to replace

$alterQuery = "UPDATE auctions SET category = ? WHERE article_no= ?";

with $alterQuery = "UPDATE auctions SET ? = true WHERE article_no= ?";

Would it also be possible to execute a separate query straight after, i.e.:

if($cmd=="addcat"){
  $alterQuery = "UPDATE auctions SET ? = true WHERE article_no= ?";
  $insertQuery = "INSERT into users (username, ?) values ?, true";
  if ($altRecord = $con->prepare($alterQuery)) {
    $altRecord->bind_param("ss", $category, $pk);
    $altRecord->execute();
  if ($insRecord = $con->prepare($insertQuery)) {
    $insRecord->bind_param("ss", $category, $username);
    $insRecord->execute();
    $insRecord->close();
  }
  $altRecord->close();
  echo "true";
} else {
  echo "false";
}

My reasoning for using the above approach is as follows:

The auctions database is imported from another source, and I cannot change the structure at all, except to add categories on to the end. Primary keys and such must not be changed.

There are only 4 categories

An individual auction may belong to more than one category

The auctions table only deals with auctions. I will need a users table, which will consist of primarily new user input.

The users table must be able to show for each users, the categories they have had auctions in.

There must not be more than one record in the users table per user. The username will function as the primary key.

Answer 1

It is the point of preparing queries that nothing except data is going to change. You could prepare different queries for each category you have (put them into array indexed by category name). But I doubt preparing queries would actually make any difference here.

Answer 2

Yikes, classic database denormalization here about to happen. You don't want to do this. You want to keep it either a single column, or, as it would seem to need to be, create a one to many relationship between auctions and type. Your first sign that something is wrong is that there's no easy path to do what you're trying to do.

A better approach would be to create an auction table, and a categoryAuctionForm table...

It would look like:

auctions Table:
auction_id 
---------
0
1
2
3

auctions Type:
auction_type_id auction_id auction_type
1               0          something
2               0          othertype
3               0          fake
4               1          othertype
5               2          fake
6               3          fake

You can also add more levels of normalization such as creating an auction_type column.

Just my thought.

Answer 3

$alterQuery = "UPDATE auctions SET ? = true WHERE article_no= ?";

No, you can't use query parameters for column names. Query parameters can be used only in the place of a literal value in an SQL expression.

You'll have to use dynamic SQL. That is, form an SQL statement as a string, and interpolate your application variables into this string as column names.