Insert empty fields gives errors

[dmhall0]

I have a form with a series of text boxes where the User enters specific data, be it text, numeric, or date/time.  It is not mandatory to fill in every box, but when I submit the form to be put into a mysql table, I get errors such as

 

"Incorrect time value: '' for column 'starttime' at row 1"

"Incorrect decimal value: '' for column 'workkj' at row 1"

 

 

How can I setup the mysql table to accept blank answers?  Or is there something I need to do in php to fix this?

 

Sorry if this is simple... I'm still learning.

Thanks for the help!

[The Little Guy]

can you show your query?

[dmhall0]

Here is the code:

// Add workout to the database
   if (isset($_POST['submit'])) {
  	// Grab the data from POST
  	 $username = $_SESSION['username'];
  	 $wotype = cleaninput($dbc, $_POST['wotype']);
   	$wodate = isset($_REQUEST["wodate"]) ? $_REQUEST["wodate"] : "";
    $woname = cleaninput($dbc, $_POST['woname']);
    $bikename = cleaninput($dbc, $_POST['bikename']);
    $starttime = cleaninput($dbc, $_POST['starttime']);
    $duration = cleaninput($dbc, $_POST['duration']);
    $distance = cleaninput($dbc, $_POST['distance']);
    $workkj = cleaninput($dbc, $_POST['workkj']);
    $spdavg = cleaninput($dbc, $_POST['spdavg']);
    $spdmax = cleaninput($dbc, $_POST['spdmax']);
    $hravg = cleaninput($dbc, $_POST['hravg']);
    $hrmax = cleaninput($dbc, $_POST['hrmax']);
    $cadavg = cleaninput($dbc, $_POST['cadavg']);
    $cadmax = cleaninput($dbc, $_POST['cadmax']);
    $pwravg = cleaninput($dbc, $_POST['pwravg']);
    $pwrmax = cleaninput($dbc, $_POST['pwrmax']);
    $temp = cleaninput($dbc, $_POST['temp']);
    $terrain = cleaninput($dbc, $_POST['terrain']);
    $weather = cleaninput($dbc, $_POST['weather']);
    $notes = cleaninput($dbc, $_POST['notes']);
    
     if (!empty($wodate) && !empty($duration) && !empty($distance)) {
        $query = "INSERT INTO u_rides (username, wotype, wodate, woname, bikename, starttime, " .
          " duration, distance, workkj, spdavg, spdmax, hravg, hrmax, cadavg, cadmax, pwravg, pwrmax, " .
          " terrain, temp, weather, notes) " .
          " VALUES ('$username', '$wotype', '$wodate', '$woname', '$bikename', '$starttime', " .
          " '$duration', '$distance', '$workkj', '$spdavg', '$spdmax', '$hravg', '$hrmax', '$cadavg', '$cadmax', '$pwravg', '$pwrmax', " .
          " '$terrain', '$temp', '$weather', '$notes')";
        mysqli_query($dbc, $query) or die('Connection Error: '.mysqli_error($dbc));

 

Here is my cleaninput function:

 

function cleaninput($dbc, $var) {
$var=stripslashes($var);
$var=htmlentities($var);
$var=strip_tags($var);
$var=mysqli_real_escape_string($dbc, $var);
$var=trim($var);
return $var;

[fenway]

Funny, that's code -- not the query.

 

Echo $query.

[dmhall0]

Ha... sorry.  Here is the query.

 

INSERT INTO u_rides (username, wotype, wodate, woname, bikename, starttime, duration, distance, workkj, spdavg, spdmax, hravg, hrmax, cadavg, cadmax, pwravg, pwrmax, terrain, temp, weather, notes) VALUES ('dhall', 'Cycling', '2011-12-08', 'Test Ride', 'Tarmac SL', '3:34', '3:23', '45', '', '', '', '', '', '', '', '', '', '', '', '', '')

 

So I need it to accept the blanks, or fill them with something I guess.

 

Thanks for the help!

[fenway]

You need to fill them with the default values -- which you can do, just use the literal DEFAULT (no quotes).

 

Or, better yet, don't include them in the column list, and then mysql will do it for you.

[dmhall0]

So how do I write the query to include them when there is a value, yet not include them if there isn't?

[fenway]

So how do I write the query to include them when there is a value, yet not include them if there isn't?

A hash would be the easiest way.

[dmhall0]

I am sorry but I don't follow.

How would I get the hash in the variable if the User leaves it blank?

Sorry if this is all elementary mysql stuff.

[fenway]

For each non-blank value, assign a new key to the hash.  Then simply go through the keys -- the field names -- to build your query string.

[dmhall0]

Sorry but I am still not following.

Could I see an example?

Please don't hate me! ha

 

Thanks for the help.