-1

I have this html form, which should work like a survey:

<body class="container">
        <form method = "post">
            <h4>Scheint morgen die Sonne?</h4>
            <input name = "sonne" type="radio" value="Ja">Ja<br>
            <input name = "sonne" type="radio" value="Nein">Nein
            <br>
            <br>
            <h4>Welches Eis isst du am liebsten?</h4>
            <input type="checkbox" name="erd" value="erdbeere">Erdbeere<br>
            <input type="checkbox" name="him" value="himbeere">Himbeere<br>
            <input type="checkbox" name="schok" value="schokolade">Schokolode<br>
            <input type="checkbox" name="van" value="vanille">Vanille<br>
            <br>
            <input name="anwenden" class="btn btn-info" type ="submit" value ="Anwenden">
        </form>
        <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/js/bootstrap.min.js"></script>
    </body>

I like to save the clicked choices into a SQL-Table by clicking the button to submit. First question is a radio button and the others are checkboxes with multiple choice possibility. The rows in SQL are all boolean type.

I get after submitting 6 rows with always one "1". Others are "0". Instead I like to have just one row with the clicked values as "1" or "0" depends on what was chosen.

So how can I do this in PHP? 

if ($_POST['email'] == 'Ja'){ 
            $query = "INSERT INTO auswahl (sonne) VALUES ('1')";
        } else {   
            $query = "INSERT INTO auswahl (sonne) VALUES ('0')";
        } 

        if ($_POST['erd']){ 
            $query = "INSERT INTO auswahl (erdbeere) VALUES ('1')"; 
        } else {   
            $query = "INSERT INTO auswahl (erdbeere) VALUES ('0')";
        } 

        if ($_POST['him']){ 
            $query = "INSERT INTO auswahl (himbeere) VALUES ('1')";
        } else {   
            $query = "INSERT INTO auswahl (himbeere) VALUES ('0')";
        } 

        if ($_POST['schok']){ 
            $query = "INSERT INTO auswahl (schokolade) VALUES ('1')"; 
        } else {   
            $query = "INSERT INTO auswahl (schokolade) VALUES ('0')";
        } 

        if ($_POST['van']){ 
            $query = "INSERT INTO auswahl (vanille) VALUES ('1')";
        } else {   
            $query = "INSERT INTO auswahl (vanille) VALUES ('0')";
        }
        mysqli_query($link, $query);
Kaushal shah
  • 557
  • 3
  • 12
volkanb
  • 229
  • 3
  • 14
  • Well you need to make _one_ INSERT statement inserting _six_ values into _six_ columns then, and not six separate ones … – misorude Aug 02 '19 at 08:00
  • 1
    So does removing all of the `... VALUES ('0')";` queries do the job. – Nigel Ren Aug 02 '19 at 08:00
  • 1
    But you should really rather fix your database layout first of all - you got a good recommendation regarding that on your question from yesterday, https://stackoverflow.com/a/57317445/10283047 – misorude Aug 02 '19 at 08:01
  • 1
    @misorude I agree the database layout is poor, but the suggestion you linked to is also not very good. Questions will change, you need a database schema that doesn't include the questions themselves, they should be the data. – KIKO Software Aug 02 '19 at 08:07

2 Answers2

1

Given the database setup you choose, you could do:

if (isset($_POST['email'])) {

   $sonne      = ($_POST['email'] == 'Ja') ? '1' : '0';
   $erdbeere   = isset($_POST['erd'])      ? '1' : '0';
   $himbeere   = isset($_POST['him'])      ? '1' : '0';
   $schokolade = isset($_POST['schok'])    ? '1' : '0';
   $vanille    = isset($_POST['van'])      ? '1' : '0';

   $query = "INSERT INTO auswahl (sonne,
                                  erdbeere,  
                                  himbeere,  
                                  schokolade,
                                  vanille) VALUES ($sonne,     
                                                   $erdbeere,  
                                                   $himbeere,  
                                                   $schokolade,
                                                   $vanille)";

    mysqli_query($link, $query);   
}

I combine all the answers into one insert query.

The ... ? ... : ... thing is called a Ternary Operator.

NOTES:

  1. You can only insert these PHP variables into the query directly because you know they will always be either zero or one. In all other cases use prepared statements, to prevent SQL-injection.
  2. Why do you abbreviate things to erd and him? Does that help readability?
  3. Your database design is very poor. Suppose your have 20 questions, how many columns will you need in your database? This was not part of the question, and I will therefore not show an alternative, but you should really reconsider this design.
KIKO Software
  • 15,283
  • 3
  • 18
  • 33
  • what would you do if for example `$_POST['erd']` is seted but not filled? It could gave you a SQL Error and you have no clue why... I prefer to use `if (isset() && !empty()) {` ;) – NvrKill Aug 02 '19 at 08:29
  • @RyffLe I corrected the missing `)`. Thanks. I don't understand your other point? If `$_POST['erd']` is set there will be a `1` in the query, otherwise a `0`. – KIKO Software Aug 02 '19 at 08:32
  • You can set a `""` in a variable so it is blank. you haven't a check for blanks in your code ;) Blanks could cause SQL Errors. – NvrKill Aug 02 '19 at 08:34
  • @RyffLe I didn't know I needed that. Are you saying that the checkbox could return that? – KIKO Software Aug 02 '19 at 08:35
  • @RyffLe: tested it to be sure: When a checkbox is not ticked its name doesn't appear in the submitted parameters, when it is ticked it does appear. – KIKO Software Aug 02 '19 at 08:38
  • Mhh ok I would add it for some security resouns xD But thats what I would do xD I mean some hack heads can change the type to text and some thing else and this will crash the server xD – NvrKill Aug 02 '19 at 08:39
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/197377/discussion-between-ryffle-and-kiko-software). – NvrKill Aug 02 '19 at 08:41
  • @ KIKO Software thank you for your suggestion it works perfectly :) Another Problem: When I reload the page it sends the form again, so another row inserts. How can I prevent this and just sending the form by clicking on submit? – volkanb Aug 02 '19 at 08:47
  • @volkanb There are two ways: 1. Redirect away from the submit page directly after you're processed the form. 2. Use AJAX and process form data in the background. The latter method is better, but more complex. PS: This is not related to your original question, or this answer, so should be discussed in a separate question. – KIKO Software Aug 02 '19 at 08:48
0

Try to use the isset() function.

For example: if (isset($_POST['van'])).

Is the Variable exists isset will give a true if it's not then false.

For more knowledge about the isset() function click here

If you want to check if the varaible is filled use the empty() function.

Is the Variable isn't filled emptry will give a true if it's not then false

For more knowledge about the emptry() function click here

$_POST['email'] == 'Ja' is posible but it will be every time $query = "INSERT INTO auswahl (sonne) VALUES ('0')"; becouse you are checking an E-Mail.

Also try to work with MySQLi_prepare and MySQLi_bind_param it will make your Query a bit secure.

For that take look here.

I hope this helped you a bit.

NvrKill
  • 327
  • 2
  • 16