php mysql adding zero to emtpy row?

[shams]

I created a html form to insert 4 rows in to mysql table with the one submit button and the php mysqli_query(), but some times i need to only insert one or two rows, when i want to insert two rows and run the query with the submit button, the query create two more rows with the 0 values in the first column, how to solve this problem to only insert the rows i want?

this is tables attributes;

mysql>  DESCRIBE subcategory;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| cat_id      | int(3)      | YES  |     | NULL    |       |
| subcategory | varchar(25) | YES  |     | NULL    |       |
| subcat2     | varchar(25) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

 

Edited July 19, 2018 by shams

[cyberRobot]

Is your PHP code testing the values from the HTML form before it inserts the information into the database? Basically, you want to make sure the form value for each row isn't blank.

[shams]

This is the  code for variable and there is no other testing code:

    // Escape user inputs for security

    $cat_id = mysqli_real_escape_string($link, $_REQUEST['cat_id']);

    $subcategory = mysqli_real_escape_string($link, $_REQUEST['subcategory']);

    $subcat2 = mysqli_real_escape_string($link, $_REQUEST['subcat2']);

 

[requinix]

What is the code that does the inserting?

[shams]

    <?php

    /* Attempt MySQL server connection. Assuming you are running MySQL

    server with default setting (user 'root' with no password) */

    $link = mysqli_connect("localhost", "user", "", "quran");

     

    // Check connection

    if($link === false){

        die("ERROR: Could not connect. " . mysqli_connect_error());

    }

     

    // Escape user inputs for security

    $cat_id = mysqli_real_escape_string($link, $_REQUEST['cat_id']);

    $subcategory = mysqli_real_escape_string($link, $_REQUEST['subcategory']);
    
    $subcat2 = mysqli_real_escape_string($link, $_REQUEST['subcat2']);

    $cat_id_2 = mysqli_real_escape_string($link, $_REQUEST['cat_id_2']);

    $subcategory2 = mysqli_real_escape_string($link, $_REQUEST['subcategory2']);

    $subcat2_2 = mysqli_real_escape_string($link, $_REQUEST['subcat2_2']);
    
    $cat_id_3 = mysqli_real_escape_string($link, $_REQUEST['cat_id_3']);

    $subcategory3 = mysqli_real_escape_string($link, $_REQUEST['subcategory3']);

    $subcat2_3 = mysqli_real_escape_string($link, $_REQUEST['subcat2_3']);

    $cat_id_4 = mysqli_real_escape_string($link, $_REQUEST['cat_id_4']);

    $subcategory4 = mysqli_real_escape_string($link, $_REQUEST['subcategory4']);

    $subcat2_4 = mysqli_real_escape_string($link, $_REQUEST['subcat2_4']);

    // attempt insert query execution

    $sql = "INSERT INTO subcategory (cat_id, subcategory, subcat2) VALUES
	    ('$cat_id', '$subcategory', '$subcat2'),
	    ('$cat_id_2', '$subcategory2', '$subcat2_2'),
	    ('$cat_id_3', '$subcategory3', '$subcat2_3'),
            ('$cat_id_4', '$subcategory4', '$subcat2_4')";

    if(mysqli_query($link, $sql)){

        echo "Records added successfully.";

    } else{

        echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);

    }

     

    // close connection

    mysqli_close($link);

    ?>

 

[ginerjm]

1 - DON'T use the $_REQUEST array.  Use the proper one based upon how your form inputs are being delivered to your script.  If you use a POST form, then read the $_POST array.  If using a GET, then use that array.  Using the REQUEST array is one way of getting caught by a hacker.

 

2 - You aren't doing ANY kind of checking of your inputs.  One should always review the inputs to ensure that they are of the correct type and are considered valid for your application before even bothering to  sanitize them.  If you run a query that has inputs for 4 rows then you are going to end up with 4 new rows.  So - instead of blindly running the escape routine on all of the form's fields, you should examine each beforehand by checking if it is blank as well as seeing that it contains something that your application is expecting.  Then you can escape it, although the better way is to be using prepared queries that will help to ensure that your app isn't getting hacked.  Of course that means you will have to think about how to construct that query if you are not going to have the 4 sets of inputs that you are currently using.  Have fun thinking about that - it will be a test of your programming skills.

 

PS - I prefer using the PDO interface instead of MySQLI.  If your server has it enabled, consider it the better choice.  Many forum users would agree with me.

[benanamen]

When you find yourself doing sequential numbering of fields that is a good sign you are doing something wrong. Your form names should be the same name but make them arrays, then you would loop through the array when you submit the form.

You can see a complete example on my site at https://galaxyinternet.us/php-insert-form-array/

[gizmola]

Take a look at Benanamen's page for an example of how to do this.  With a static form it's quite simple to change your markup in the way he shows using

 

<!-- First entry -->
<label>Category</label>
<select name="cat_id[]">
</select>
<label>Sub category</label><input type="text" name="subcategory[]">
<label>Sub sub category</label><input type="text" name="subcat2[]">

<!-- Second entry -->
<label>Category</label>
<select name="cat_id[]">
</select>
<label>Sub category</label><input type="text" name="subcategory[]">
<label>Sub sub category</label><input type="text" name="subcat2[]">

 

As he illustrates you then get arrays instead of individual elements.  Also, don't use $_REQUEST but the specific form method you are implementing, which is most often going to be POST.  Use $_POST instead.