using php variables inside mysql code

I have a database with columns that are constantly edited. so i have set up a database list for mysql to pull from to make or remove columns.

this doesnt seem to be working. I tried passing the variable $specs_product2 into the mysql code, but the value comes up zero.

here is the relevent part of the form…
[php]

Product:

<?php require_once('functions.php'); require_once('config.php'); connect(); // Product and quantity 1 $result = mysql_query("select DISTINCT ID,products from dropdown where products is not null order by products");echo '';echo "Choose Product"; while ($row = mysql_fetch_array($result)){ $id = $row["ID"]; $po = $row["products"]; echo "$po"; } echo ''; ?>

Enter Manufacturing Specs Here

Product: <?php // Product and quantity 3 $result = mysql_query("select DISTINCT ID,products from dropdown where products is not null order by products");echo '';echo "Choose Product"; while ($row = mysql_fetch_array($result)){ $id = $row["ID"]; $po = $row["products"]; echo "$po"; } echo ''; ?> Quantity:

[/php]

and here is post_product_specs.php…

[php]<?php
require_once(‘functions.php’);
require_once(‘config.php’);
connect();
///Specs

$specs_product1= make_safe($_POST[‘product1’]);
$add=“INSERT INTO product_specs (product) VALUES (’$specs_product1’)”;
if (!mysql_query($add)) {
die('error uploading content: ’ . mysql_error());
}

if(empty($_POST[‘quantity1’])) {}
else {
$specs_product2= make_safe($_POST[‘product2’]);
$specs_quantity1= make_safe($_POST[‘quantity1’]);
$quantity= $specs_quantity1 * -1;
$add=“INSERT INTO product_specs (” . $specs_product2 . “) VALUES (’$quantity’)”;
if (!mysql_query($add)) {
die('error uploading content: ’ . mysql_error());
}
}
?>[/php]

I hope ive explained myself well enough, im not always good at getting everything out of my head. I basically just want to pass $variables like this…
[php]Mysql_query(" INSERT INTO table1 (" . $variable1 . “) VALUES (’$value1’)”;[/php]

Thanks in advance!

I’m having a hard time reading through your code…
Are you getting any error messages?

Here is a working example of passing variables to mysql_query

[php]

<?php if(isset($_POST['submit'])){ $variable = $_POST['variable']; $sql = mysql_query("INSERT INTO table (columnName) VALUES ('$variable')") or die("Error: ".mysql_error()); if($sql){ echo "Successfully entered"; exit; } else { die("There was an error: " . mysql_error()); } } ?> SQL Example
<form action="" method="post">
	<input type="text" name="variable"/>
    <input type="submit" name="submit"/>
</form>
[/php]

“columnName” in the mysql_query() needs to be a column name from your table.

You are not setting any value to your option.

[php]echo “$po”;[/php]

Should be something like

[php]echo “<option value=”" . $id . “”>$po";[/php]

if you don’t set the value for the option it defaults to the text between the tags…

<option>Money</option>

is the same as…

<option value="Money">Money<option>

I think only reason to use value is if you want the value inserted in the db to something different than what the user sees (like what you have with the $id in the value attr and $po between the tags).

You are right :wink:

what im trying to do is make the columnname a variable.
I have products and materials. different products use differend materials to assemble them. i have a table with a product column, then the rest of the columns are the materials.

i have a while loop that scrolls through the product names and takes the materials used for that item and inputs them into the database.

i didnt get any errors and the product posted to the database properly, its just the variable for the column name that doesnt post.

I just tried to make a simplistic version of what i want to do. here it is ->

[php]<?php
require_once(‘functions.php’);
require_once(‘config.php’);
connect();

$value_1=“column_name”;
$value_2=“row_field_value”;

$add=“INSERT INTO table (” . $value_1 . “) VALUES (’$value_2’)”;
if (!mysql_query($add)) {
die('error uploading content: ’ . mysql_error());
}
?>[/php]

I actually did get an error here. it was the following:
error uploading content: 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 ‘table (column_name) VALUES (‘row_field_value’)’ at line 1

Maybe im blind here, but i just cant see my error. the table “table” exists and the column “column_name” exists…

i dont know much but does this need to be edited?
original:
[php]$add=“INSERT INTO table (” . $value_1 . “) VALUES (’$value_2’)”;[/php]
EDITED:
[php]$add=“INSERT INTO table (’$value_1’) VALUES (’$value_2’)”;[/php]
OR:
[php]$add=“INSERT INTO table (” . $value_1 . “) VALUES (” . $value_2 . “)”;[/php]
??
it might not make a diffferance but thats all i can see…?

They should all work the same.
I would probably go with the last one though because your variables will stand out if you need to modify it later. The first one is sloppy because you are using different formatting.
The problem with the second on is the variables will be harder to find (this should effect the say your code is ran)

thank you for those suggestions, however none of them work correctly. I still get the same error, and nothing was put in my table.

check and make sure in your table that “column_name” is as such and not “Column_nAme” or something like that and change in your code accordingly… if it is this then its not finding the correct column… i dont know if that would cause it…

Yes the names match

maybe try addslashes() or mysql_real_escape_string()

[php]
$value_2 = addslashes($value_2);
[/php]

“TABLE” is a reserved word. If you must use reserved words you need to enclose them in backticks table

See: http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

AAAhhhh yes of course, table is also very broad. I would not recommend it, you should really look into naming conventions (especially if you are relatively new to this so you don’t get into bad habits).

table was only the example i used to show you guys what i was trying to do. i didnt use such general names in my actual code. thanks for the suggestion about that though i didnt know that rule.

i changed table to litterbox and added ‘’ to the field value. this gives a more acurate picture of what happens when i run my actual code.
[php]$add=“INSERT INTO litterbox (” . $value_1 . “) VALUES (’” . $value_2 . “’)”;[/php]
it looks like it runs fine, but when i go to my database the, the value put under column_name is not row_field_value. Its only a zero.

actually for my example code the problem was that my column_name was set to INT(11) instead of VARCHAR. That isnt the case for my other code though. i tried changing it from INT to VARCHAR even though the variable will be a number.

[php]if(empty($_POST[‘quantity1’])) {}
else {
$specs_product2= make_safe($_POST[‘product2’]);
$specs_quantity1= make_safe($_POST[‘quantity1’]);
$quantity= $specs_quantity1 * -1;
$add=“INSERT INTO product_specs (” . $specs_product2 . “) VALUES (’” . $quantity . “’)”;
if (!mysql_query($add)) {
die('error uploading content: ’ . mysql_error());
}
}[/php]

values product2 and and quantity1 come from this form field…
[php]

Enter Product Here

Product: <?php require_once('functions.php'); require_once('config.php'); connect(); // Product and quantity 1 $result = mysql_query("select DISTINCT ID,products from dropdown where products is not null order by products");echo '';echo "Choose Product"; while ($row = mysql_fetch_array($result)){ $id = $row["ID"]; $po = $row["products"]; echo "$po"; } echo ''; ?>

Enter Manufacturing Specs Here

Product: <?php // Product and quantity 2 $result = mysql_query("select DISTINCT ID,products from dropdown where products is not null order by products");echo '';echo "Choose Product"; while ($row = mysql_fetch_array($result)){ $id = $row["ID"]; $po = $row["products"]; echo "$po"; } echo ''; ?> Quantity:

[/php]

Ok Ive figured it out. Thank you to everyone on this thread. all these suggestions guided me to the right place.

My problem ended up being in the form. i had an input field named incorrectly (quantity1 should have been quantity2)

maybe something to be said for naming your variables and inputs well, ill try to do better with that. thank you guys again so much. i hope one day i can be as helpful.

Sponsor our Newsletter | Privacy Policy | Terms of Service