Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I have a form that has multiple inputs of the same name, resulting in an array. I want to pass this $_POST array to a function which will process and save the array into a database.

I've done this before with no problems without using a function, but now that I want to contain it all in a nice neat function call it won't do it and I'm not sure why?

The input/post variable in question is named option[] and is passed to the function as $_POST['option']. Here is the function:

function newVariant($name, $option) {
global $db;
global $table_prefix;
$table = $table_prefix . "variants";

$query = $db->prepare("INSERT INTO $table(name) VALUES(:name)");
$query->bindParam(":name", $name);

if (!$query->execute()) {
    die(showMessage("Error!","There has been a problem saving your variant. Please try again or contact technical support if the problem persists.",""));
    }

$variant_id = $db->lastInsertId('id');

for($i = 0; $i < count($option); $i++) {
   if($option[$i] != "") {

      $table2 = $table_prefix . "variant_items";
      $query2 = $db->prepare("INSERT INTO $table2(variant, option) VALUES(:variant, :option)");
      $query2->bindParam(":variant", $variant_id);
      $query2->bindParam(":option", $option[$i]);

      if (!$query2->execute()) {
         die(showMessage("Error!","There has been a problem saving your variant. Please try again or contact technical support if the problem persists.",""));
      }
   }
}

$redirect = renderLink("/beyond/?act=admin&sub=variants", "true");
showMessage("Saving variant...<META HTTP-EQUIV=\"Refresh\" Content=\"1; URL=$redirect\">","","");
}

This is the error I'm getting in my log:

PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 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 'option) VALUES(?, ?)' at line 1' in /Users/adampcollings/Sites/Hot Mint Development/beyond/php/functions/product.php:131
Stack trace:
#0 /Users/adampcollings/Sites/Hot Mint Development/beyond/php/functions/product.php(131): PDO->prepare('INSERT INTO bb_...')
#1 /Users/adampcollings/Sites/Hot Mint Development/beyond/html/admin/new-variant.php(5): newVariant('Test', Array)
#2 /Users/adampcollings/Sites/Hot Mint Development/beyond/php/html.php(19): include('/Users/adampcol...')
#3 /Users/adampcollings/Sites/Hot Mint Development/beyond/html/admin.php(10): subElement('admin', 'new-variant')
#4 /Users/adampcollings/Sites/Hot Mint Development/beyond/php/html.php(8): include('/Users/adampcol...')
#5 /Users/adampcollings/Sites/Hot Mint Development/beyond/index.php(14): siteElement('a in /Users/adampcollings/Sites/Hot Mint Development/beyond/php/functions/product.php on line 131
share|improve this question
1  
Please expand on "it won't do it." What do you expect to happen, and what really happens? – George Cummins Apr 22 at 15:50
I'm expecting it to save the name to one table, and all the options to a different table in their own row while storing the ID of the parent with it. At the moment it is storing the parent name in the first query, but none of the options from the second. – Adam Apr 22 at 15:53
Are any of your error conditions triggered? Do you get a message? – George Cummins Apr 22 at 15:53
None of the errors are triggered, but it doesn't complete the function as there is no redirect at the end. It just shows whitespace where there should be something. – Adam Apr 22 at 15:55
Have you enabled error reporting in your PHP configuration? – George Cummins Apr 22 at 15:56

1 Answer

up vote 2 down vote accepted

Based on the comment thread above, there are several things you should try.

First, always enable error reporting when debugging an application. To do this in your script, add:

error_reporting(E_ALL);

to the top of your script.

Second, ensure that $options contains the data you expect. Somewhere in your code, add the following line:

var_dump($options);

This will show you the contents of $options. If it does not contain the values you expect, check your submission process.

Finally, if $options contains the expected data, check your table structure to ensure that your query matches and inserts the correct values.

EDIT: After you posted the MySQL error, I cross-checked the MySQL Reserved Words list. The word 'option' is on the list. As such, the query is failing because the word is not recognized as a column name. Try surrounding the column name with backticks:

$query2 = $db->prepare("INSERT INTO $table2(`variant`, `option`)...
share|improve this answer
Okay, I will try that. I have also added the PHP error into the original post. – Adam Apr 22 at 16:02
Right, a var_dump on $options returns null. So it seems the array isn't getting passed to the function. – Adam Apr 22 at 16:05
Actually nevermind, the array is now getting passed. – Adam Apr 22 at 16:08
@Adam, see the edit to my answer. I think your column name option is conflicting with a MySQL reserved word. – George Cummins Apr 22 at 16:08
1  
Ah that was exactly it. Backticks solved it, thank you very much! – Adam Apr 22 at 16:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.