Inserting Data into MySQL table using HTML form

Question

I am currently trying to insert rows into a MySQL database, and most of the code is there but I'm having a few issues I can't diagnose. I know the database connection is good, and every time the submit button is pressed it runs the correct php script. The issue I'm having is that it always adds 2 records to the database table and fails to carry though any of the form data (it inserts two completely blank rows.)

Here's the code for the form (with a little extra code for the wordpress page)

  <div class="main-container">
<div class="content-area">
    <div class="middle-align">
        <div class="site-main" id="sitefull">
            <?php while ( have_posts() ) : the_post(); ?>
                <?php get_template_part( 'content', 'page' ); ?>
            <div>
                <form method="POST" name="cutting tool" action="add-tool-script.php">
                    <table style="width:auto;">
                        <tr>
                            <th width="50%"><h2><ul>Tool Information</ul></h2><br></th>
                            <th width="50%"><ul><h2>Storage Information</h2></ul><br></th>
                        </tr>
                        <tr>
                            <td width="50%">
                                <h3>Tooling Name</h3><br>
                                <input type="text" name="name" placeholder="e.g. ShearHog"><br><br>

                                <h3>Tooling Type</h3><br>
                                <select name="type">
                                    <option selected disabled hidden style='display: none' value=''></option>
                                    <option value="Ballnose Endmill">Ballnose Endmill</option>
                                    <option value="Bullnose Endmill">Bullnose Endmill</option>
                                    <option value="Boring Bar">Boring Bar</option>
                                    <option value="Brush">Brush</option>
                                    <option value="Burnishing">Burnishing</option>
                                    <option value="Chamfer Mill">Chamfer Mill</option>
                                    <option value="Countersink">Countersink</option>
                                    <option value="Drill">Drill</option>
                                    <option value="Drill/Mill">Drill/Mill</option>
                                    <option value="Engraver">Engraver</option>
                                    <option value="Face Mill">Face Mill</option>
                                    <option value="Flat Endmill">Flat Endmill</option>
                                    <option value="High Feed Mill">High Feed Mill</option>
                                    <option value="Reamer">Reamer</option>
                                    <option value="Slitting Saw">Slitting Saw</option>
                                    <option value="Spot Drill">Spot Drill</option>
                                    <option value="Tap">Tap</option>
                                    <option value="Threadmill">Threadmill</option>
                                    <option value="Woodruff">Woodruff</option>
                                    <option value="Other">Other</option>
                                </select><br><br>

                                <h3>Tooling Brand</h3><br>
                                <input type="text" name="brand" placeholder="e.g. Lakeshore Carbide"><br><br>

                                <h3>Part Number</h3><br>
                                <input type="text" name="part_number" placeholder="e.g. 360014X"><br><br>

                                <h3>Price</h3><br>
                                <input type="text" name="price" placeholder="e.g. 24.95"><br><br>

                                <h3>Overall Length</h3><br>
                                <input type="text" name="oal" placeholder="e.g. 2.5"><br><br>

                                <h3>Tooling Material</h3><br>
                                <select name="material">
                                    <option selected disabled hidden style='display: none' value=''></option>
                                    <option value="Carbide">Carbide</option>
                                    <option value="Ceramic">Ceramic</option>
                                    <option value="Diamond">Diamond</option>
                                    <option value="HSS">HSS</option>
                                    <option value="Powdered Metal">Powdered Metal</option>
                                </select><br><br>

                                <h3>Cutting Diameter</h3><br>
                                <input type="text" name="cutting_diam" placeholder="e.g. 0.250"><br><br>

                                <h3>Shank Diameter</h3><br>
                                <input type="text" name="shank_diam" placeholder="e.g. .250"><br><br>

                                <h3>Number of Flutes</h3><br>
                                <input type="text" name="flutes" placeholder="e.g. 3"><br><br>

                                <h3>Length of Cut (Flute Length)</h3><br>
                                <input type="text" name="loc" placeholder="e.g. .750"><br><br>

                                <h3>Corner Radius</h3><br>
                                <input type="text" name="corner_rad" placeholder="e.g. .004"><br><br>
                            </td>
                            <td width="50%">
                                <h3>Quantity in Stock</h3><br>
                                <input type="text" name="qty" placeholder="e.g. 37"><br><br>

                                <h3>Minimum Trigger Quantity</h3><br>
                                <input type="text" name="trigger_qty" placeholder="e.g. 4"><br><br>

                                <h3>Reorder Link</h3><br>
                                <input type="text" name="reorder_link" placeholder="e.g. example.com"><br><br>

                                <h3>Toolbox Number</h3><br>
                                <input type="text" name="toolbox_no" placeholder="e.g. 1"><br><br>

                                <h3>Drawer Number</h3><br>
                                <input type="text" name="drawer_no" placeholder="e.g. 1"><br><br>

                                <h3>Bin Number</h3><br>
                                <input type="text" name="bin_no" placeholder="e.g. 1"><br><br>

                                <h3>Product</h3><br>
                                <input type="text" name="product" placeholder="e.g. Widget #2"><br><br>

                                <input type="submit" value="Add to Tool Crib" name="submitbutton" action="submit"/>
                            </td>
                        </tr>
                    </table>

                </form>
            </div>
                <?php
                //If comments are open or we have at least one comment, load up the comment template
                    if ( comments_open() || '0' != get_comments_number() )
                    comments_template();
                ?>
            <?php endwhile; // end of the loop. ?>
        </div>
        <div class="clear"></div>
    </div>
</div>
</div>

<?php get_footer(); ?>

And here's the code for the php script to add form data to the database:

   //MySQL Database
    $servername = "url.com";
    $username = "user_login";
    $password = "user_password";
    $datab = "database_name";

    // Create connection
    $conn = new mysqli($servername, $username, $password, $datab);

    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
  }

$name = $_REQUEST['name'];
$type = $_REQUEST['type'];
$brand = $_REQUEST['brand'];
$part_number = $_REQUEST['part_number'];
$price = $_REQUEST['price'];
$oal = $_REQUEST['oal'];
$material = $_REQUEST['material'];
$cutting_diam = $_REQUEST['cutting_diam'];
$shank_diam = $_REQUEST['shank_diam'];
$flutes = $_REQUEST['flutes'];
$loc = $_REQUEST['loc'];
$corner_rad = $_REQUEST['corner_rad'];
$qty = $_REQUEST['qty'];
$trigger_qty = $_REQUEST['trigger_qty'];
$reorder_link = $_REQUEST['reorder_link'];
$toolbox_no = $_REQUEST['toolbox_no'];
$drawer_no = $_REQUEST['drawer_no'];
$bin_no = $_REQUEST['bin_no'];
$product = $_REQUEST['product'];
$username = $user_login;

$sql = "INSERT INTO `cutting tools` (`name`, `type`, `brand`, `part_number`, `reorder_link`, `oal`, `price`, `material`, `cutting_diam`, `shank_diam`, `flutes`, `loc`, `corner_rad`, `qty`, `trigger_qty`, `user`, `drawer_no`, `bin_no`, `toolbox_no`) 
        VALUES ('$name', '$type', '$brand', '$part_number', '$reorder_link', '$oal', '$price', '$material', '$cutting_diam', '$shank_diam', '$flutes', '$loc', '$corner_rad', '$qty', '$trigger_qty', '$username', '$drawer_no', '$bin_no', '$toolbox_no')";


if(mysqli_query($conn, $sql)){
    echo "Record added successfully.";
} else{
    echo "ERROR: Could not execute $sql. " . mysqli_error($conn);
}

// Close connection
mysqli_close($conn);
?>

Also I know my database is vulnerable to injection, that was a change I planned on making once the form was up and running.

If it's inserting blank rows and echo'ing "Record added successfully.", then my first check would be to see if there's anything in $_REQUEST. What does var_dump($_REQUEST); output when you submit the form? (sidenote: you almost never want to use $_REQUEST since it's a mix of query parameters, posted form values and cookie values and it depends on server configuration which one your script actually receives. In this case, prefer using $_POST instead) — rickdenhaan
– rickdenhaan, Commented Oct 30, 2019 at 18:49

quicklikerabbit · Accepted Answer · 2019-10-30 20:09:35Z

1

Use WordPress to your advantage. Instead of defining your own connection, use global $wpdb, and then use the insert command.

global $wpdb;

$success = $wpdb->insert('tbl_name', array(<br>
   'field1_name' => $_REQUEST['field1'],<br>
   'field2_name' => $_REQUEST['field2'],<br>
));<br>
if($success){<br>
   echo "Inserted correctly";<br>
} else {<br>
   echo "Something went awry!";<br>
}

edited Oct 30, 2019 at 20:09

quicklikerabbit

3,6166 gold badges29 silver badges43 bronze badges

answered Oct 30, 2019 at 19:29

UM-ISRphp

466 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SJacks · Accepted Answer · 2019-10-30 21:00:16Z

Here's a prepared statement, a more secure way of creating a MYSQL record in your table.

<?php    
//MySQL Database
$servername = "url.com";
$username = "user_login";
$password = "user_password";
$datab = "database_name";

// Create connection
$con = new mysqli($servername, $username, $password, $datab);
global $con;

// Post form data
$name = $_POST['name'];
$type = $_POST['type'];
$brand = $_POST['brand'];
$part_number = $_POST['part_number'];
$price = $_POST['price'];
$oal = $_POST['oal'];
$material = $_POST['material'];
$cutting_diam = $_POST['cutting_diam'];
$shank_diam = $_POST['shank_diam'];
$flutes = $_POST['flutes'];
$loc = $_POST['loc'];
$corner_rad = $_POST['corner_rad'];
$qty = $_POST['qty'];
$trigger_qty = $_POST['trigger_qty'];
$reorder_link = $_POST['reorder_link'];
$toolbox_no = $_POST['toolbox_no'];
$drawer_no = $_POST['drawer_no'];
$bin_no = $_POST['bin_no'];
$product = $_POST['product'];

// Prepared statement
$insert = mysqli_prepare($con, "insert into cutting tools (name,type,brand,part_number,reorder_link,oal,price,material,cutting_diam,shank_diam,flutes,loc,corner_rad,qty,trigger_qty,user,drawer_no,bin_no,toolbox_no) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
mysqli_stmt_bind_param($insert, "sssssssssssssssssss", $name,$type,$brand,$part_number,$reorder_link,$oal,$price,$material,$cutting_diam,$shank_diam,$flutes,$loc,$corner_rad,$qty,$trigger_qty,$product,$drawer_no,$bin_no,$toolbox_no);
mysqli_stmt_execute($insert);

if ($insert) { echo "success"; mysqli_close($con); } else { echo "error"; mysqli_close($con); }
?>

Pay close attention to the order of your columns and the data you are submitting to those columns. I have edited this post because your order was incorrect in several places.

The column names and the data variables being uploaded to them have to be in the exact same order if you want data created correctly.

By the way the variable $product does not seem to match the column name of user, you may want to check this.

Collectives™ on Stack Overflow

Inserting Data into MySQL table using HTML form

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related