Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3

Thread: Userbased table

  1. #1
    New to the CF scene
    Join Date
    Nov 2017
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Userbased table

    I have tried to figure out this for a period now but have not yet found a solution. I'm still in learning stage and this is my first project to make a website form scratch.

    I have a form where there is a table that the user can fill in information and add new rows.

    I wonder how I can save this in MySql.

    Hope you can help me with this.

    Html code looks like this

    Code:
    <div class="table-responsive">
    <table class="table table-bordered table-hover" id="tab_logic">
        <thead>
            <tr>
                <th>#</th>
                <th>Varenavn</th>
                <th>Mengde</th>
                <th>Enhet</th>
                <th>Pris</th>
            </tr>
        </thead>
        <tfoot>
            <tr>
                <td></td>
                <td></td>
                <td></td>
                <td></td>
                <td><a id="add_row" class="btn btn-success pull-right">Add Row</a><a id='delete_row' class="pull-right btn btn-danger">Delete Row</a></td>
            </tr>
        </tfoot>
        <tbody>
           <tr id='addr0'>
                <td>1</td>
                <td><input type="text" name='v_navn0' placeholder='Varenavn' class="form-control" /></td>
                <td><input type="text" name='mengde0' placeholder='Mengde' class="form-control" /></td>
                <td><select name='enhet0' placeholder='Enhet' class='form-control input-md'><option>KG</option><option>GR</option><option>L</option<option>DL</option></select></td>
                <td><input type="text" name='pris0' placeholder='Pris' class="form-control" /></td>
            </tr>
            <tr id='addr1'></tr>
        </tbody>
    </table>
    And her is my JavaScript code

    Code:
    $(document).ready(function () {
        var i = 1;
        $("#add_row").click(function () {
            $('#addr' + i).html("<td>" + (i + 1) + "</td><td><input name='v_name" + i + "' type='text' placeholder='Varenavn' class='form-control input-md'  /> </td><td><input  name='mengde" + i + "' type='text' placeholder='Mengde'  class='form-control input-md'></td><td><select name='enhet" + i + "' placeholder='Enhet'  class='form-control input-md'><option>KG</option><option>GR</option><option>L</option><option>DL</option></select></td</td></td><td><input name='pris" + i + "' type='text' placeholder='Pris' class='form-control input-md'  />");
    
            $('#tab_logic').append('<tr id="addr' + (i + 1) + '"></tr>');
            i++;
        });
        $("#delete_row").click(function () {
            if (i > 1) {
                $("#addr" + (i - 1)).html('');
                i--;
            }
        });
    
     });
    And here is my Query

    Code:
    // Create connection
    $conn = new mysqli($servername, $username, $password, $db);
    
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    } 
    
    // definding names
    if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $navn = ($_POST["navn"]);
    $antall = ($_POST["antall"]);
    $holdbar = ($_POST["holdbar"]);
    $fremgang = ($_POST["fremgang"]);
    $allergen = ($_POST["allergen"]);
    $varenavn = ($_POST["v_navn0"]);
    $mengde = ($_POST["mengde0"]);
    $enhet = ($_POST["enhet0"]);
    $pris = ($_POST["pris0"])     
    }
    
    // Query
    
    $sql = "INSERT INTO oppskrift (opp_navn, antall, holdbarhet, beskrivelse, allergen, varenavn, mengde, enhet, pris)
    VALUES ('$navn', '$antall', '$holdbar', '$fremgang', '$allergen', '$varenavn', '$mengde', '$enhet', '$pris')";
    
    if ($conn->query($sql) === TRUE) {
    
    // Redirect to login page
                    header("location: /bin/appdata/ny_opp.php");
    
                } else{
                    echo "Something went wrong. Please try again later.";
                }
    
    $conn->close();
    ?>
    and this is my table

    Code:
    CREATE TABLE `oppskrift` (
        `id` INT(11) NOT NULL AUTO_INCREMENT,
        `opp_navn` VARCHAR(255) NULL DEFAULT NULL,
        `antall` VARCHAR(255) NULL DEFAULT NULL,
        `holdbarhet` VARCHAR(50) NULL DEFAULT NULL,
        `beskrivelse` TEXT NULL,
        `allergen` VARCHAR(255) NULL DEFAULT NULL,
        `varenavn` VARCHAR(255) NULL DEFAULT NULL,
        `mengde` VARCHAR(255) NULL DEFAULT NULL,
        `enhet` VARCHAR(255) NULL DEFAULT NULL,
        `pris` VARCHAR(255) NULL DEFAULT NULL,
        PRIMARY KEY (`id`)
    )
    COLLATE='latin1_swedish_ci'
    ENGINE=InnoDB
    AUTO_INCREMENT=16
    ;
    Last edited by vinyl-junkie; 11-08-2017 at 11:02 PM. Reason: added code tags

  2. #2
    Regular Coder Vege's Avatar
    Join Date
    Jan 2008
    Posts
    839
    Thanks
    21
    Thanked 114 Times in 113 Posts
    Altough I should know Swedish/Norwegian I'm really rusty. Could you translate into English the html table headers and mysql table columns.

  3. #3
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    4,079
    Thanks
    3
    Thanked 506 Times in 494 Posts
    You need two database tables. The first one will hold the unique/one-time values. The second one will hold the multiple sets of values, related back to the first table using the id from the first table.

    The form field names for the multiple sets of values need to be array names - name='v_navn[]'. This will cause the data from those fields to be submitted as arrays, that you can simply loop over in the php code. This will also eliminate the need to generate the numerical sequence of field names. Using the [] syntax, the array elements will be numerically indexed, starting at zero.

    The javascript to dynamically add rows needs to simply copy the set of existing markup and append it where you want it. The way to do this is to put a <div> around the 1st set of form fields you want to duplicate. The code for the add_row function will just get the html from the first <div>, and append it to a second <div>. This will cause the code to use the actual html markup, so that you don't need to duplicate it inside the javascript code (DRY - Don't Repeat Yourself.)

    You need to switch to the php PDO extension, it is much simpler to use, especially when using prepared queries, which you also need to use in order to prevent sql special characters in the data from breaking the sql query syntax (which is how sql injection is accomplished.)

    For your php code -

    1) You need to use exceptions to handle database errors. This will eliminate the need to have logic at each statement that can fail, which will simplify the code. Simple enable exceptions for the php database extension you are using and let php catch the exception. Php will use it's error_reporting, display_errors, log_errors settings to control what happens with the actual error information. Error_reporting should always be set to E_ALL. When learning, developing code, and debugging code, display_errors should be set to ON. When on a live server, display_errors should be set to OFF and log_errors should be set to ON. When using exceptions, your main code only 'sees' error free execution, and so doesn't need any logic testing if a statement worked or not, thereby simplifying the code.

    2) ALL the form processing code needs to be inside the {...} of the conditional statement. The code forming and executing the sql query is outside of and after the closing }. This will cause an insert query to be executed every time the page gets requested, not just when the form has been submitted.

    3) All the lines of code that are assigning $_POST variables to other variables are a waste of typing time and are error prone. You actually should make a trimmed copy the $_POST data in to a common program variable (this is more useful if editing existing data, rather than inserting new data), but you can do this all at once using a single statement, not a separate statement for each form field.

    4) You need to validate all the input data before using it. If there are validation errors, there's no point in running any code that's using the data. If you use a php array variable to hold the validation errors, the variable will be an error flag. If the array is empty(), there are no errors. If the array is not empty(), there are errors. To display the validation errors, you can simply loop over this array at the appropriate point in your html markup.

    5) If you are inserting the unique/one-time data and the multiple sets of data all at once, you would form and execute the insert query for the unique data first, then get the last insert id from the query to use with the insert queries for the multiple sets of data.

    6) As already mentioned, you need to use prepared queries (you can research in the php.net documentation or on the web to find out what this means), with place-holders in the sql query statements for the data values, then supply the actual data when you execute the query. For the case of the multiple sets of data, you would prepare the query once, before the start of any looping, then just supply each set of data values when you execute the query inside of the loop. Using prepared queries actually simplifies the sql query syntax, since there are will be no single-quotes around the place-holders in the sql query statement.

    7) Any header() redirect needs an exit; statement after it to stop code execution. The target of the header redirect needs to be a URL. Your code is apparently trying to use a file system path.

    8) Your form and your form processing code need to be on the same page, so that you can display any validation errors when you display the form and you can re-populate the form fields with previously submitted data so that the visitor doesn't need to keep typing in the same values when there are validation errors.

    I highlighted the word simple and its variations in the above, because using these programming practices will reduce the amount of code and syntax you have to type and debug in order to accomplish a programming task.
    Last edited by CFMaBiSmAd; 11-09-2017 at 01:37 AM. Reason: fix wording
    Finding out HOW to do something is called research, i.e. keep searching until you find the answer. After you attempt to do something and cannot solve a problem with it yourself, would be when you ask others for help.


 

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •