0

I am trying to be clever, possibly too clever in creating a set of forms that will dynamically insert their data into the database based on their input names.

I have it so it builds the query correctly, however when I attempt to insert it fails as to say it is a malformed query, is there a better way of doing what I am trying to achieve, but similarly?

<form method="post">
    <input type="text" name="form_array['text']" /> 
    <button name="insert">Go</button>
</form>

Here is my query generation, on submit of the form:

$setup['table'] = "table1";

if(isset($_POST['insert'])){
    $inserts = "";
    $values = "";

    foreach($_POST['form_array'] as $form_id => $form_data){
        if(!empty($form_data)){
            if(!isset($first_run)){
                $first_run = 1;
                $inserts .= $form_id;
                $values .= "'".$form_data."'";
            } else {
                $inserts .= ", ".$form_id;
                $values .= ", '".$form_data."'";
            }
        } else {
            die('error');
        }
    }

    die("INSERT INTO '".$setup['table']."' ($inserts) VALUES ($values)");
}

Thanks.

Danny
  • 579
  • 2
  • 7
  • 13
  • This is a very insecure script setup. You should research SQL Injection to find out why. To more directly address your question though... you have a syntax problem. Each set of values needs to be enclosed in parentheses like the answer to the following question: http://stackoverflow.com/questions/452859/inserting-multiple-rows-in-a-single-sql-query – craigts Aug 17 '15 at 21:02
  • None of this is sanitized purposely, this is a test script and completely redacted. I am inserting one row, so all the values are to be comma separated within one set of brackets. – Danny Aug 17 '15 at 21:04
  • Ok then it is likely the fact that you are single quoting the table name. Remove those and you should be good. Unless you have perhaps redacted too much and changed the die call from what actually gets sent to the db. – craigts Aug 17 '15 at 21:06
  • I tried with and without, still the same: **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 ''title', 'body', 'author_id') VALUES ('dsfeqf', 'sdwdwewe', '1')' at line 1** and I haven't redacted anything required. – Danny Aug 17 '15 at 21:11
  • Remove the single quotes around the column names as well. – craigts Aug 17 '15 at 21:13
  • You must not put the table name or any of the column names in quotes. If you do so, MySQL will interpret them as literal values rather than identifiers. – Don't Panic Aug 17 '15 at 21:16
  • possible duplicate of [When to use single quotes, double quotes, and backticks?](http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks) – Don't Panic Aug 17 '15 at 21:17
  • Can you post the sql query output? – phpchap Aug 17 '15 at 21:21
  • suggest using an sql query builder (working with php objects), like [Dialect](https://github.com/foo123/Dialect) (ps author) – Nikos M. Aug 17 '15 at 21:22

1 Answers1

0

Aside from all the fixes that was commented to you; this can be simplified, try it;

<form method="post">
    <input type="text" name="form_array[column1]" /> 
    <input type="text" name="form_array[column2]" /> 
    <button name="insert">Go</button>
</form>

<?php
if(isset($_POST['insert'])){
    $table = 'table';
    $handler = 'form_array';
    $keys = array_keys($_POST[$handler]);
    $sql = "INSERT INTO `{$table}` (`".implode('`, `', $keys)."`) VALUES ('".implode("', '", $_POST[$handler])."')";
    echo $sql;
}

Output:

INSERT INTO `table` (`column1`, `column2`) VALUES ('value1', 'value2')

vonUbisch
  • 1,384
  • 17
  • 32