Error when passing multiple rows to sql database at once

Question

aahhhh so confusing. So when I just do one set, with just a set of product_1_name, product_1_price, product_1_quantity the script works fine! it adds 5 as the product id and everything else is added to the database fine. The other spots are left as null.

However, when I add the other variables i.e. products 1 to 7 all at once, the script doesnt execute! the database stays empty and doesnt add to the table. Ive spent 2 hours checking for syntax errors but couldnt find typos. Is this related to the number of items you can add at one?

$a = 5;
$productid = $a;


    $product_1_name = mysqli_real_escape_string($conn, $_POST['product_1_name']);   //uid
    $product_1_price = mysqli_real_escape_string($conn, $_POST['product_1_price']); //uid
    $product_1_quantity = mysqli_real_escape_string($conn, $_POST['product_1_quantity']);   //uid

    $product_2_name = mysqli_real_escape_string($conn, $_POST['product_2_name']);   //uid
    $product_2_price = mysqli_real_escape_string($conn, $_POST['product_2_price']); //uid
    $product_2_quantity = mysqli_real_escape_string($conn, $_POST['product_2_quantity']);   //ui3

    $product_3_name = mysqli_real_escape_string($conn, $_POST['product_3_name']);   //uid
    $product_3_price = mysqli_real_escape_string($conn, $_POST['product_3_price']); //uid
    $product_3_quantity = mysqli_real_escape_string($conn, $_POST['product_3_quantity']);   //uid

    $product_4_name = mysqli_real_escape_string($conn, $_POST['product_4_name']);   //uid
    $product_4_price = mysqli_real_escape_string($conn, $_POST['product_4_price']); //uid
    $product_4_quantity = mysqli_real_escape_string($conn, $_POST['product_4_quantity']);   //uid

    $product_5_name = mysqli_real_escape_string($conn, $_POST['product_5_name']);   //uid
    $product_5_price = mysqli_real_escape_string($conn, $_POST['product_5_price']); //uid
    $product_5_quantity = mysqli_real_escape_string($conn, $_POST['product_5_quantity']);   //uid

    $product_6_name = mysqli_real_escape_string($conn, $_POST['product_6_name']);   //uid
    $product_6_price = mysqli_real_escape_string($conn, $_POST['product_6_price']); //uid
    $product_6_quantity = mysqli_real_escape_string($conn, $_POST['product_6_quantity']);   //uid

    $product_7_name = mysqli_real_escape_string($conn, $_POST['product_7_name']);   //uid
    $product_7_price = mysqli_real_escape_string($conn, $_POST['product_7_price']); //uid
    $product_7_quantity = mysqli_real_escape_string($conn, $_POST['product_7_quantity']);   //uid






        $sql = "INSERT INTO products (userid, productname, venue, date, productdescription, name) VALUES ('$userid','$productname', '$venue', '$date', '$productdescription', '$name');";
        mysqli_query($conn, $sql);

        $sql = "INSERT INTO productproducts (productid, product_1_name, product_1_price, product_1_quantity, 
                            product_2_name, product_2_price, product_2_quantity, 
                            product_3_name, product_3_price, product_3_quantity, 
                            product_4_name, product_4_price, product_4_quantity, 
                            product_5_name, product_5_price, product_5_quantity, 
                            product_6_name, product_6_price, product_6_quantity, 
                            product_7_name, product_7_price, product_7_quantity) 
                            VALUES (
                            '$productid','$product_1_name', '$product_1_price', '$product_1_quantity',
                            '$product_2_name', '$product_2_price', '$product_2_quantity', 
                            '$product_3_name', '$product_3_price', '$product_3_quantity', 
                            '$product_4_name', '$product_4_price', '$product_4_quantity',  
                            '$product_5_name', '$product_5_price', '$product_5_quantity', 
                            '$product_6_name', '$product_6_price', '$product_6_quantity', 
                            '$product_7_name', '$product_7_price', '$product_7_quantity' 

                            );";

        mysqli_query($conn, $sql);

        header("Location: ../members.php?product=success");
        exit();



} else {
    header("Location: ../signup.php");
    exit();
}






?>

Code I used to make database:

CREATE TABLE productevents (
    o_id int(11) not null AUTO_INCREMENT PRIMARY KEY,
    productid int(11),
    product_1_name varchar(256),
    product_1_price int(11),
    product_1_quantity int(11),

    product_2_name varchar(256),
    product_2_price int(11),
    product_2_quantity int(11),

    product_3_name varchar(256),
    product_3_price int(11),
    product_3_quantity int(11),

    product_4_name varchar(256),
    product_4_price int(11),
    product_4_quantity int(11),


    product_5_name varchar(256),
    product_5_price int(11),
    product_5_quantity int(11),

    product_6_name varchar(256),
    product_6_price int(11),
    product_6_quantity int(11),

    product_7_name varchar(256),
    product_7_price int(11),
    product_7_quantity int(11)





);

echosql result:

Notice: Undefined index: product_3_name in website.xx.xxon line 29

Notice: Undefined index: product_3_price in website.xx.xxon line 30

Notice: Undefined index: product_3_quantity in website.xx.xxon line 31

Notice: Undefined index: product_4_name in website.xx.xxon line 33

Notice: Undefined index: product_4_price in website.xx.xxon line 34

Notice: Undefined index: product_4_quantity in website.xx.xxon line 35

Notice: Undefined index: product_5_name in website.xx.xxon line 37

Notice: Undefined index: product_5_price in website.xx.xxon line 38

Notice: Undefined index: product_5_quantity in website.xx.xxon line 39

Notice: Undefined index: product_6_name in website.xx.xxon line 41

Notice: Undefined index: product_6_price in website.xx.xxon line 42

Notice: Undefined index: product_6_quantity in website.xx.xxon line 43

Notice: Undefined index: product_7_name in website.xx.xxon line 45

Notice: Undefined index: product_7_price in website.xx.xxon line 46

Notice: Undefined index: product_7_quantity in website.xx.xxon line 47
INSERT INTO productevents (productid, product_1_name, product_1_price, product_1_quantity, product_2_name, product_2_price, product_2_quantity, product_3_name, product_3_price, product_3_quantity, product_4_name, product_4_price, product_4_quantity, product_5_name, product_5_price, product_5_quantity, product_6_name, product_6_price, product_6_quantity, product_7_name, product_7_price, product_7_quantity) VALUES ( '5','product1', '1', '1', 'product2', '2', '2', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '' );

BTW: there seems to be a major security leak in your code. Your `product_price` should come from the database and not from the user form, not to mention that you are not using pdo — B001ᛦ, Oct 11 '17 at 07:41
I hope this is just example code and not actually being used in anything... So have you echoed the actual $sql and checked it and tried to use it directly on your table via phpmyadmin or what ever Database utillity you have? — TimBrownlaw, Oct 11 '17 at 07:48
Does your table really contain all the necessary columns to add 7 product and their properties in 1 row? You should really look into normalizing your database structure. And in combination with the question title, your code doesn't seem to make any sense. — jeroen, Oct 11 '17 at 07:49
@jeroen yes it does :) I also made them accept null so that the rest can also be filled out in case. I have added the code I used to make the database — d joe, Oct 11 '17 at 08:36
@TimBrownlaw yeah i have tried it, why do you hope its just an example code? cause of the post method? — d joe, Oct 11 '17 at 08:40
@djoe Well yes you are not performing any validation...Never trust any user input... If you have tried the generated SQL did it work? If not it will give you an error message of some kind. — TimBrownlaw, Oct 11 '17 at 08:55
I don't think my answer adds any value now. So i am deleting it. — Shahbaz A., Oct 11 '17 at 09:04
@TimBrownlaw i need user input on my website e.g. product description from users :/ I'm not getting any errors. It's almost like the table itsnt set up correctly and im putting in the wrong values. But I tried it with one set and it works, it just doesnt work with more than one — d joe, Oct 11 '17 at 09:17
In regards to your attempted $sql output, did you fill in your form to generate it? So fill in your form, then echo your $sql. If you need to put an exit(); after the echo to halt the code after you submit the form. The whole point is to view the $sql AFTER the form is submitted, — TimBrownlaw, Oct 11 '17 at 21:50
@TimBrownlaw Yes that is after i filled in my form and had an exit(). I only added two tickets as seen in the code. But yeah, it was after I submitted the form — d joe, Oct 11 '17 at 21:53
@TimBrownlaw ahhh i think i know. It's from doing $ticket_1_name = mysqli_real_escape_string . It gets no value from everything above product 2 and therefore throws an error. Do I do an if statement to only execute it on the variables that are entered? — d joe, Oct 11 '17 at 22:34
So are you only entering in 1 product in your form and "allowing" for the off chance someone "might" enter in all 7... You are working on the basis that your form populates with fields for all 7 products. Your errors suggest that you only have fields for the 1st two products. I'd have to write you a story on how to solve this... — TimBrownlaw, Oct 12 '17 at 02:52
@TimBrownlaw i want the user to select how many. e.g. 1,2 or 3 products etc. They then enter information for however many they selected. Just a hint in the right direction will suffice :) appreciate it. — d joe, Oct 12 '17 at 06:10
I would suggest you create a new question as this one has been answered... When you add all 7 products this should work. The right direction is redesigning your whole process and Database. The product table holds a single product related to a user or something like that... With what you have now when it comes to editing / deleting/ adding new entries this becomes a total nightmare and I only like happy thoughts. — TimBrownlaw, Oct 12 '17 at 06:51
Please do not vandalize your posts. By posting on the Stack Exchange network, you've granted a non-revocable right for SE to distribute that content (under the [CC BY-SA 3.0 license](https://creativecommons.org/licenses/by-sa/3.0/)). By SE policy, any vandalism will be reverted. If you would like to disassociate this post from your account, see [What is the proper route for a disassociation request?](https://meta.stackoverflow.com/q/323395/3524982) — DJMcMayhem, Oct 12 '17 at 20:16

score 0 · Answer 1 · answered Oct 11 '17 at 11:31

This is aimed at your particular question.

You never did what I asked with echoing the $sql and copying and pasting it into phpmyadmin to test the SQL. Maybe I wasn't clear enough. This is standard debugging for lazy people (myself included)

If you had... you would find that your SQL Statement is... Note I have used dummy values for your variables...

INSERT INTO productproducts (
  productid, 
  product_1_name,product_1_price,product_1_quantity,
  product_2_name, product_2_price,product_2_quantity, 
  product_3_name, product_3_price,product_3_quantity, 
  product_4_name, product_4_price,product_4_quantity, 
  product_5_name, product_5_price,product_5_quantity, 
  product_6_name, product_6_price,product_6_quantity, 
  product_7_name, product_7_price,product_7_quantity) 
  VALUES ( 
  '1',
  'One', '1', '1',
  'Two', '2', '2',
  'Three', '3', '3', 
  'Four', '4', '4', 
  'Five', '5', '5', 
  'Six', '6', '6', 
  'Seven', '7', '7' 
)

Now you would have found that it generates an Error and on close observation you don't have a table called productproducts, you have called it productevents.

So your table name is wrong...

What is price?

Is it an integer ( whole number )? It has to be because you have defined price as an integer. So how are you preventing people from entering $4.99 for instance? What is stopping someone from entering in Fluffy Bunnies?

Validation of ALL user input is critical.

Your Database structure is a whole other question...

hello :) might have been a typo when i copied it over to stackoverflow. So I just executed your code in sql and it WORKED :) so that means the table is configured right, the code just isnt being passed through ?my code is still not adding to the table — d joe, Oct 11 '17 at 21:12
Ok so now you have to echo your $sql and "execute" what you get. — TimBrownlaw, Oct 11 '17 at 21:15

score -1 · Answer 2 · answered Oct 11 '17 at 08:03

you added extra semi-colon before the end of query.you should try

$sql = "INSERT INTO productproducts (productid, product_1_name, product_1_price, product_1_quantity, 
                        product_2_name, product_2_price, product_2_quantity, 
                        product_3_name, product_3_price, product_3_quantity, 
                        product_4_name, product_4_price, product_4_quantity, 
                        product_5_name, product_5_price, product_5_quantity, 
                        product_6_name, product_6_price, product_6_quantity, 
                        product_7_name, product_7_price, product_7_quantity) 
                        VALUES (
                        '$productid','$product_1_name', '$product_1_price', '$product_1_quantity',
                        '$product_2_name', '$product_2_price', '$product_2_quantity', 
                        '$product_3_name', '$product_3_price', '$product_3_quantity', 
                        '$product_4_name', '$product_4_price', '$product_4_quantity',  
                        '$product_5_name', '$product_5_price', '$product_5_quantity', 
                        '$product_6_name', '$product_6_price', '$product_6_quantity', 
                        '$product_7_name', '$product_7_price', '$product_7_quantity' 

                        )";

hope it works for you.

That semi colon doesn't matter. Nor does it pose any problems. — Shahbaz A., Oct 11 '17 at 09:06

Error when passing multiple rows to sql database at once

2 Answers2