db colunm name / update data

[RON_ron]

I need to read a column name (in my db) using a variable. How can I do it. Below is my code.

 

Eg.

Name of the column which I need to update is

"AppleFruitProducts"

 

variable sent to the php containing the word "AppleFruit" is in:

$category = mysql_real_escape_string($_POST['categories']);

 

My code:

$outlet = mysql_real_escape_string($_POST['outlets']);
$category = mysql_real_escape_string($_POST['categories']);

$catName = "Products";
$newColumnName = $category.$catName;
$availability = "3";


$query = sprintf("UPDATE db5 SET $newColumnName=".'$availability'.", WHERE Outlet ='%s'", mysql_real_escape_string($outlet));

Edited February 4, 2013 by RON_ron

[PFMaBiSmAd]

This is going to be all negative, but Is that really the code you are using? You are using three different methods of putting php variables into ONE string (direct, concatenation, and a sprintf parameter), you are escaping one of the values twice, and you cannot prevent sql injection in database, table, or column names by using a string escape function because they are not string data that's inside of single or double quotes in the query (and yes you can turn on a setting for your database engine that allows you to use double-quotes around database/table/column names, but its not normally turned on, nor are you using double-quotes around your database/table/column names in your code), so there's not anything you are preventing them from escaping from. The meaning of the term 'escaping data' means to prevent special characters in the data from allowing that data to 'break out' i.e escape from, the string it is in.

 

You also should not have column names that are specific values, like AppleFruitProducts. That indicates a bad database design that is not normalized. Based on what your have posted, your table should have columns for outlet_id, product_id, and availability (which I suspect is probably a quantity.) The id in the product_id column would indicate that the row is for AppleFruitProducts.

[Christian F.]

This sounds like a prime candidate for proper database normalization, as stated by PFMaBiSmAd.

I recommend starting with a previous post of mine, to get to grips on that subject.

 

I was also going to comment upon your code, but since PFM just covered it all I won't bother with repeating it. Read his reply thoroughly, until you understand every single detail of what he's talking about. Then you'll be able to rewrite your script, and database, to something a lot better.