Using an If statement to Eliminate Empty Rows and Columns in MySQL using PHP when Inserting

Question

I'm inserting date from a form using PHP. I have 24 fields in which a user can insert in data into the database. The Problem is how do I not allow empty fields from becoming rows in MySQL is they are left empty from the User? None of the fields are required and fields are formatted as input text fields. What I was considered using is in if statement in the values section once queried; here's an example:

/**************
* HTML Form
**************/
<form action='insert.php' method='post'>

<p> What items do you have for sale?</p>

Item 1: <input type='text' name='item1'> Price: <input type='text' name='item1cost'>
Item 2: <input type='text' name='item2'> Price: <input type='text' name='item2cost'>
Item 3: <input type='text' name='item3'> Price: <input type='text' name='item3cost'>

<input type="submit" name="Submit" value="Submit">
</form>


/**************
* PHP
**************/

$user = (from $_SESSION)
$item1      = $_POST['item1'];
$item1cost  = $_POST['item1cost'];
$item2      = $_POST['item2'];
$item2cost  = $_POST['item2cost'];
$item3      = $_POST['item3'];
$item3cost  = $_POST['item3cost'];


/*********************
* MySQL INSERT query
*********************/

$query = "INSERT INTO monthly_expenses (`userid`, `item`, `amount`)" 
                                   . "VALUES ($user,'$item1', '$item1cost'),
                                             ($user,'$item2', '$item2cost'),
                                             ($user,'$item3', '$item3cost'),

If user leaves either one of the fields blank how can I get them not to insert into the database so there's not an empty row with just the userid?

TCFDS · Accepted Answer · 2012-12-11 16:35:37Z

You can put values into array, then check if they are empty or not (maybe some other checks would be recomended concerning security issues) and create $values variable, which you can load into database.

$item[0]      = mysql_real_escape_string(trim($_POST['item1']));
$itemcost[0]  = mysql_real_escape_string(trim($_POST['item1cost']));
$item[1]      = mysql_real_escape_string(trim($_POST['item2']));
$itemcost[1]  = mysql_real_escape_string(trim($_POST['item2cost']));
// and so on

$values = "";
for ($i = 0; $i < 2; ++$i) {
    if ($item[$i] != "" && $itemcost[$i] != "") {
        $values .= "($user,'$item[$i]', '$itemcost[$i]'),";
    }
}

/*********************
* MySQL INSERT query
*********************/

if ($values != "") {
  $values = substr($values, 0, -1);
  $query = "INSERT INTO monthly_expenses (`userid`, `item`, `amount`)" 
                                   . " VALUES $values";
}

It would be also possible to give HTML input field name's as arrays like item[] and parse them straight to php-arrays.

david strachan · Accepted Answer · 2012-12-11 16:39:57Z

0

Use Client Side Validation before posing. If the form is not filled in to suit your requirements the user is informed and forced to do so.

See this Tutorial for help.

answered Dec 11, 2012 at 16:39

david strachan

7,2282 gold badges25 silver badges33 bronze badges

Comments

Glorious Kale · Accepted Answer · 2012-12-11 17:04:15Z

0

You can store your html as array values.

   <input type="text" name="item[]" />
   <input type="text" name="itemcost[]" />

Then you can loop them like:

   $query = "INSERT INTO monthly_expenses (`userid`, `item`, `amount`) VALUES ";

   foreach($_POST['item'] as $key => $value){
   {
        // Check if it's not filled.
        if($value !== '')
        {
              $query .= "($user, '$value', $_POST['itemcost'][$key])"; // whatever you need to insert.
        }
   }
   // Run the query.

I may have some typos but the concept is there.

edited Dec 11, 2012 at 17:04

answered Dec 11, 2012 at 16:56

Glorious Kale

1,31315 silver badges29 bronze badges

Collectives™ on Stack Overflow

Using an If statement to Eliminate Empty Rows and Columns in MySQL using PHP when Inserting

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related