My function runs successfully, but the database shows a row with zeros or blank space

Question

I'm designing a crud app to store storage data in a database. Using php, mySQL (with the help of XAMPP). I keep running into this problem, though, where I click "Submit" on my form and the function appears to run successfully (I get a the success message on the php page), but when I look at the actual database, the row fills 3 of the fields (the first 1 and the last 2) with just "0" regardless of what's in the form when I click submit. And then the other two columns don't have anything in them at all.

Here's my code for the form:

<div id="newprojform">
  <fieldset><legend>Create New Project</legend>
    <form action='projectadded.php' name="addnewproject"  onsubmit="return(validate());">
      <div id="newprojfields"><br />
        Project Name: 
        <div class="field">
          <input type="text" name="projname" size="50">
        </div>
        <br /><br />
        Customer: 
        <div class="field">
          <input type="text" name="custname" size="50">
        </div>
        <br /> <br />
        Move ID: 
        <div class="field">
          <input type ="text" name="moveID" size="50"> 
        </div>
        <br /><br />
        <label for="unit">Unit of Measurement: </label>
        <div class="field" id="uomdd">
          <select id="unit" name="unit">
            <option value="Select">Select</option>
            <option value="CWT">CWT</option>
            <option value="Vault">Vault</option>
            <option value="SqFt">SqFt</option>
            <option value="Pallet">Pallet</option>
            <option value="Piececount">Piece Count</option>
          </select>
        </div>
        <br /><br />
        Cost Per Unit: 
        <div class="field">
          <input type="text" name="cost" size="50">
        </div>
        <br /> <br />
        <input type="submit" value="Add Project" id="submit"><br />
      </div>
  </form></fieldset>

</div>

And here's the php

<?php 
$con=mysqli_connect("localhost","root","", "storagelog");  
if (mysqli_connect_errno())
{
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$sql="INSERT INTO newproject (moveid, projectname, customername, UoM, PPU)
VALUES
('$_POST[moveID]','$_POST[projname]','$_POST[custname]','$_POST[unit]','$_POST[cost]')";

if(!mysqli_query($con,$sql))
{
    die ('Error: ' . mysqli_error($con));
}
echo "1 record added";

mysqli_close($con);
?>

Can anybody please tell me what I'm doing wrong? I've gone over the syntax and names of the forms a million times, but every time I click "submit," I just get a row that either has zeros in it or nothing at all. This is all very new to me, so i'm expecting a lot of stupid or clunky mistakes, but this is the first time I'v ever been so stuck..

any help is appreciated.

You are using mysqli ... any reason you aren't using parameterized queries or even making an attempt to sanitize your query inputs??? — Orangepill, Jul 20 '13 at 03:56

score 1 · Accepted Answer · edited May 23 '17 at 12:12

The problem is that you aren't specifying POST as the method in your form, but you are trying to read the form variables from the $_POST variable. See this thread: What is the default form HTTP method? for information about the form defaults. To fix it, you just need to change your form to this:

<form action='projectadded.php' name="addnewproject"  onsubmit="return(validate());" method="POST">

There are some other things you should change in here, like sanitizing your inputs before putting them into the database, but those aren't the root of your problem.

score 1 · Answer 2 · answered Jul 20 '13 at 03:54

So first things first. I don't recommend using unfiltered $_POST and $_GET variables when dealing with the database (in a production setting of course).

Second, when using the form element, you have to specify whether you're using a post method, or a get method:

<form action="location_of_file.php" method="POST">

Also, make sure you check that your column names in the table absolutely match the names in your sql insert syntax.

Maybe even try the following to see if it inserts:

$sql="INSERT INTO newproject (`moveid`, `projectname`, `customername`, `UoM`, `PPU`)
  VALUES ('" . $_POST["moveID"] . "','" . $_POST["projname"] . "','" . $_POST["custname"] . "','" . $_POST["unit"] . "','" . $_POST["cost"] . "')";

Sometimes, SQL can be touchy.

Lol, dang... seanmk beat me to the punch. If what we suggest works, choose his answer as he was first to post it.

score 1 · Answer 3 · answered Jul 20 '13 at 04:01

For future reference (aside from the excellent advice in the other answers) you should consider printing out $sql to see what your actual insert statement looks like. You'd see immediately that your values are empty strings and would know to go chase after the $_POST to see why it's arriving empty.

My function runs successfully, but the database shows a row with zeros or blank space

3 Answers3