Jump to content

php mysql adding zero to emtpy row?


shams

Recommended Posts

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)

 

Link to comment
Share on other sites

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']);

 

Link to comment
Share on other sites


    <?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);

    ?>

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.