I'm trying to use prepared statements to validate a form and I want to be able to use this same bit of code across multiple forms. I'd like to stick to the DRY method.
Each form will contain a different form name (a hidden field that represents the database table) and have different attributes (values in the select list That represent columns in a MYSQL database) that need to be validated before it can be added to the cart.
For some crazy reason the price additions are done in JS (I know it's a terrible way to add to the price but that's the only option I have so changing that unfortunately isn't an option). The form needs to be validated to make sure a user hasn't tampered with the form to save them some money. So the way I'm checking is grabbing the values from the form and checking them against values in a database to make sure they are the same if any of the values are different then what is in the database then the form is invalid. I keep getting an error about my SQL statements. Specifically that it failed to prepare the statement
Here is the PHP code with the prepared statement:
<?php
///////MYSQL Login Data/////
$user = "root";
$password = "";
$database = "printoptions";
///////MYSQL loging Data ///////
//Grab the quantity selected from the form
$quantity = explode('|+', $_POST['quantity']);
//Set quantity to be only the numerical value not the add amount part
$quantity = $quantity[0];
//what is the name of the form? This comes from a hidden field
$formname = $_POST['formname'];
//instantiate mysqli object used for db queries
$mysqli = new mysqli("localhost", $user, $password, $database);
//If there is an error connecting to the database spit it out and tell me what the error is
if ($mysqli->connect_errno)
{
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
//Start our prepared statment
//The query is:
//SELECT `$attribute` FROM `$formname` WHERE `quantity` = $quantity
if (!($stmt = $mysqli->prepare('SELECT ? FROM ? WHERE `quantity`=?')))
{
echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
//Loop Through each Post value
foreach ($_POST as $name => $value)
{
//make sure we don't grab formname and quantity just get all the attributes and leave those alone
if ($name != 'formname' AND $name != 'quantity')
{
//Prepared statement do our bind and execute
//the attribute is the field values first part before the |+
//so explode the $value
$attribute = explode('|+', $value);
//Assign just the first part
$attribute = $attribute[0];
//do the binding string, string, float(double)
if (!$stmt->bind_param("sss", $attribute, $formname, $quantity))
{
echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
}
//execute the query based on the binded paramaters
if (!$stmt->execute())
{
echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}
//Bind the results of the query temporarily to $result
$stmt->bind_result($result);
//If the result of the query is NOT equal to the value of the field make it fail validation
if ($result != $value)
{
//What to do if it fails validation
echo "Validation Failed You little Cheater!";
} else
{
//What to do if it passes validation
echo "Validation Success!";
}
//end if checking form values aginst DB values
}
//end if excluding formname and quantity
}
//end foreach loop
Then the form HTML itself just to clarify the first part of the value before the |+ is the column name while the second part is how much to add to the price. So: value="columnName|+priceIncrease"
<form action="post.php" method="post">
<input type="hidden" name="formname" value="brochure">
<div>
<select name="quantity" id="ff_elem13">
<option value="1000|+550.00">1000</option>
<option value="2500|+900.00">2500</option>
</select>
</div>
<div>
<select name="fold" id="ff_elem14">
<option value="foldBi|+15.00">Bi Fold</option>
<option value="foldTri|+20.00">Tri Fold</option>
</select>
</div>
<div>
<select name="color" id="ff_elem15">
<option value="color40|+15.00"> Color 4:0</option>
<option value="color41|+20.00">Color 4:1</option>
</select>
</div>
<div>
<select name="rush" id="ff_elem16">
<option value="rush13|+15.00">Rush 1-3 Days</option>
<option value="rush24|+20.00">Rush 2-4 Days</option>
</select>
</div>
<input type="SUBMIT" value="Submit">
</form>
The error I recieve says
Prepare failed: (1064) 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 '? WHERE
quantity=?' at line 1
How can I fix this so I can use this prepared statement? I've looked around but I don't ever see anyone using the ? for selecting the actual table name and mostly just people using it to insert or update values.
Hopefully that explains my situation well enough, but if I'm missing something let me know so I can correct myself.
