Inserting $variable or $_POST value into mysql tab

Question

My question concerns why one piece of code works and two that does not, and how i can get the code that does not work to work.

The code that works:

mysql_select_db("webuser1", $con);

mysql_query("INSERT INTO users (column 1, column2) VALUES ('value1', 'value2')");

mysql_close($con);

Code no1 that does not ($var1 contains 'value1' etc.):

mysql_select_db("webuser1", $con);

mysql_query("INSERT INTO users (column 1, column2) VALUES ($var1, $var2)");

mysql_close($con);

And code no2 that does not work ($_POST['value1'] contains 'value1' etc.):

mysql_select_db("webuser1", $con);

mysql_query("INSERT INTO users (column 1, column2) VALUES ($_POST['value1'], $_POST['value2'])");

mysql_close($con);

Am i not supposed to be able to insert $var or $_POST in mysql? I hope you do not find this Q stupid but i have been looking around for solutions but i have not understood them. Thank you

Answer 1

Seems like you're not escaping and quoting your arguments to mysql properly.

To insert variables in MySQL you need to escape them at least: $var = mysql_real_escape_string($_POST['variable']) and then ".. VALUES ('".$var."')"

You should also probably consider using libraries for connecting to MySQL like DOCTRINE: http://www.doctrine-project.org/ that handles this for you.

Answer 2

$var1=$_POST['variable_name1'];
$var2=$_POST['variable_name2'];
$q="INSERT INTO `users` (`column 1`, `column2`) VALUES ($var1, $var2)";

$result=mysql_query($q);

Answer 3

One thing you have to understand:
You can't insert $variable or $_POST value into mysql table.

You can insert them in another PHP variable only.

Which variable, if happens to be a valid SQL query, can be sent to mysql, which will add corresponding values in table.

So, you have to learn proper PHP strings syntax first.

So, PHP lets you 3 different ways of adding an associative array member into string:

$array['key'] = 'world';
$str = "Hello ".$array['key'];
$str = "Hello {$array['key']}";
$str = "Hello $array[key]";

You also have issues with SQL syntax.
Strings in the SQL query have to be escaped and quoted. Your code lacks both.

Answer 4

Use this solution, its 100% works

mysql_query("INSERT INTO users (column 1, column2) VALUES ('{$_POST[value1]}', '{$_POST[value2]}')");

when you use {}, you dont need write value in ' '

Answer 5

Your variables are not recognized as variables. They are a part of your string.

Try:

mysql_query("INSERT INTO users (column 1, column2) VALUES ('".$var1."', '".$var2."')");

Same for your second problem.

Answer 6

Because the POST variables have ' in them, you have to concatenate instead.

I.E.

mysql_query("INSERT INTO users (column 1, column2) VALUES (".$_POST['value1'].", ".$_POST['value2'].")");

Or

mysql_query("INSERT INTO users (column 1, column2) VALUES ({$_POST['value1']}, {$_POST['value2']})");

It's also a good idea to put quotes around the variables, in case its empty (or a string rather than an integer)