multiple dynamically generated checkboxes in PHP/MySQL

Question

Hi,

I have a series of check boxes that are coming out of one MySQL table:

<?php
    $result = mysql_query("SELECT * FROM strategies");
    if (!$result) {
    die("Database query failed: " . mysql_error());
    }
while($row = mysql_fetch_array($result)) {
    $strategylist = $row['name'];
    $strategyname = htmlspecialchars($row['name']);
    echo '<input type="checkbox" name="strategy[]" value="' . $strategylist . '" />' . $strategyname;
}
?>

I want to be able to store multiple "strategies" to each row on a "studies" table, so I am employing another table (sslink) to store the id of the study and the name of the strategy. This is partly because there will be an ever growing number of "strategies", so they need to be stored in the database. This is the code I'm currently using:

<?php   



if(isset($_POST['update1']))
{
  $strategy=serialize($_POST['strategy']); //line 66, where the warning is happening

   if(!get_magic_quotes_gpc())
   {
      $strategy = addslashes($strategy);
   }

   // update the article in the database
   $query ="INSERT INTO sslink('study_id', 'strategyname') VALUES ('".$_GET['id']. "', '" .$strategy. "')";

   mysql_query($query) or die('Error : ' . mysql_error());

   $cacheDir = dirname(__FILE__) . '/cache/';

   $cacheFile = $cacheDir . '_' . $_GET['id'] . '.html';

   @unlink($cacheFile);

   @unlink($cacheDir . 'index.html');

   echo "<b>Article '$title' updated</b>";

      $strategy = stripslashes($strategy);
}


?>

And this is the error that gets returned:

Notice: Undefined index: strategy in /casestudyform.php on line 66 Error : 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 ''study_id', 'strategyname') VALUES ('1', 'N;')' at line 1

Does anyone know how to fix this? or a better way to do it?

Thanks in advance!

Answer 1

Try this:

$query ="INSERT INTO sslink (study_id, strategyname) VALUES ('".$_GET['id']. "', '" .$strategy. "')";

Answer 2

Undefined index suggests that $_POST['strategy'] wasn't set. Could you do a sanity check that your form has it? Also, an echo of the actual query would be nice.

Answer 3

You have two errors that are unrelated to one another:

Notice: Undefined index: strategy in /casestudyform.php on line 66

As @montooner points out, this notice is from PHP, because the $_POST array contains no value for the 'strategy' key. That is, the form was submitted with no strategy checkbox checked. You should test that the key exists before trying to reference it.

 if (array_key_exists('strategy', $_POST)) ...

---

Error : 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 ''study_id', 'strategyname') VALUES ('1', 'N;')' at line 1

This is an SQL parsing error. You have put single-quotes around the columns in your INSERT statement. In SQL, single-quotes delimit string constants, not column names.

If you need to delimit column names (because they contain SQL keywords, whitespace, special characters, etc.), you should use back-quote in MySQL or double-quotes in ANSI SQL.

Also be careful of SQL injection. Don't assume that the HTTP request parameters contain only integers or friendly strings. Filter the values or escape them before you use them in SQL. The addslashes() function is not a good solution to protect against SQL injection.

$id = filter_input(INPUT_GET, 'id', FILTER_SANITIZE_NUMBER_INT);
$strategy_esc = mysql_real_escape_string($strategy);
$query ="INSERT INTO sslink(`study_id`, `strategyname`)
  VALUES ($id, '$strategy_esc')";