I learned in a previous thread that you can insert mutiple records from the same form into a database as separate records like this: My question: Is there a way to only insert ONLY if the information is filled out. For instance, if val1, val2 and val3 are filled out, but val4, val5 and val6 are blank, i don't want them added to the database. Please help me refine my code. Thanks!
If you have to check the variables first before inserting, you might as well step through them with a loop. Inside the loop you can check for non-blanks, then do an insert if it has valid data. That would mean doing an insert for each record, but there isn't much overhead involved so it should be fine.
You can change this function to suit what you want, but here's a basic that you can use. function query_builder(){ //set your table column name here $table = array('column1'=>$_POST['column1'], 'column2'=>$_POST['column2'], 'column3'=>$_POST['column3'] ); $counter = 1; foreach($table as $column => $value){ if($value != ''){ if($counter == count($table) || $counter == 1){ $col .= $column; $val .= $value; }else{ $col .= ','.$column; $val .= ','.$value; }//else }//end if $counter++; }//end foreach loop $query = 'INSERT INTO yourtablename ('.$col.') VALUES('.$val.')'; return $query; }//end function query_builder if(isset($_POST['submit'])){ $query = query_builder(); mysql_query($query); }//end if PHP:
Hmm. I tried using the code and inputing it into mine and I get an error message stating "query was empty" Here is the real code that I am working with -maybe this will help. I have a form for people submit a list of names and addresses to invite to an event. This is processing the form into a database. My original code is this - it works, but will insert a new record even if there is no data submitted. I gave each form field a distinct name ex: LastName, LastName2, etc. $db=mysql_connect("localhost", "artwo6_secretinp", "*********") or die("Could not connect to localhost."); mysql_select_db("artwo6_secretartists08", $db) or die("Could not find visitors."); $sql = "insert into `HHinput` (`HHFName`, `HHLName`, `FirstName`, `LastName`, `Address1`, `Address2`, `City`, `State`, `Zip`, `phone`, `email`) values ('{$_POST['HHFName']}', '{$_POST['HHLName']}', '{$_POST['FirstName']}', '{$_POST['LastName']}', '{$_POST['Address1']}', '{$_POST['Address2']}', '{$_POST['City']}', '{$_POST['State']}', '{$_POST['Zip']}', '{$_POST['Phone']}', '{$_POST['Email']}'), ('{$_POST['HHFName']}', '{$_POST['HHLName']}', '{$_POST['FirstName2']}', '{$_POST['LastName2']}', '{$_POST['Address12']}', '{$_POST['Address22']}', '{$_POST['City2']}', '{$_POST['State2']}', '{$_POST['Zip2']}', '{$_POST['Phone2']}', '{$_POST['Email2']}')"; $result = mysql_query($sql) or die(mysql_error() . '<br />' . $querySQL); Code (markup): I used the code from phplover and tweaked it to look like this: (in this one, all the fields going into the same column were named the same -ex: LastName, LastName) $db=mysql_connect("localhost", "artwo6_secretinp", "*********") or die("Could not connect to localhost."); mysql_select_db("artwo6_secretartists08", $db) or die("Could not find visitors."); function query_builder(){ //set your table column name here $table = array('HHFName'=>$_POST['HHFName'], 'HHLName'=>$_POST['HHLName'], 'FirstName'=>$_POST['FirstName'], 'LastName'=>$_POST['LastName'], 'Address1'=>$_POST['Address1'], 'Address2'=>$_POST['Address2'], 'City'=>$_POST['City'], 'State'=>$_POST['State'], 'Zip'=>$_POST['Zip'], 'Phone'=>$_POST['Phone'], 'Email'=>$_POST['Email'] ); $counter = 1; foreach($table as $column => $value){ if($value != ''){ if($counter == count($table) || $counter == 1){ $col .= $column; $val .= $value; }else{ $col .= ','.$column; $val .= ','.$value; }//else }//end if $counter++; }//end foreach loop $query = 'INSERT INTO HHinput ('.$col.') VALUES('.$val.')'; return $query; }//end function query_builder if(isset($_POST['submit'])) { $query = query_builder(); mysql_query($query);}//end if $result = mysql_query($query) or die(mysql_error() . '<br />' . $querySQL); Code (markup): This is giving me the query empty error. What am I doing wrong? I'm still kinda new to all of this. Thank you for the help!!
One more thing is left to implement in this function is put quote on string data type. When I have time i'll add this part, but if you can add then awesome. function query_builder($post){ //set your table column name here $table = array('column1'=>$post['column1'], 'column2'=>$post['column2'], 'column3'=>$post['column3'] ); $counter = 1; foreach($table as $column => $value){ if($value != ''){ if($counter == count($table) || $counter == 1){ $col .= $column; $val .= $value; }else{ $col .= ','.$column; $val .= ','.$value; }//else }//end if $counter++; }//end foreach loop $query = 'INSERT INTO yourtablename ('.$col.') VALUES('.$val.')'; return $query; }//end function query_builder if(isset($_POST['submit'])){ $query = query_builder($_POST); mysql_query($query); }//end if PHP:
quote on string data type.. I must need more coffee- I don't understand that sentance. I know i should... but it's flying over my head right now.
Here's the final version of query_builder. Basically, the query_builder function task is to build a query according to the user input data form. If there any empty field on the form then query_builder will strip out that field from the query. $db=mysql_connect("localhost", "artwo6_secretinp", "*********") or die("Could not connect to localhost."); mysql_select_db("artwo6_secretartists08", $db) or die("Could not find visitors."); function query_builder($post){ //set your table column name here //remember to add a quote around the string data type $table = array('HHFName'=>'\''.$post['HHFName'].'\'', 'HHLName'=>'\''.$post['HHLName'].'\'', 'FirstName'=>'\''.$post['FirstName'].'\'', //<----e.g name is should be char or varchar, so you must add here a quote 'LastName'=>'\''.$post['LastName'].'\'', 'Address1'=>'\''.$post['Address1'].'\'', 'Address2'=>'\''.$post['Address2'].'\'', 'City'=>'\''.$post['City'].'\'', 'State'=>'\''.$post['State'].'\'', 'Zip'=>$post['Zip'], //<---zip and phone number might be integer, so we don't need quote but its will depend on your table structure 'Phone'=>$post['Phone'], 'Email'=>'\''.$post['Email'].'\'' ); $counter = 0; foreach($table as $column => $value){ if($value != '\'\''){ //<--if the input value is empty then iqnore that column if($counter == count($table) || $counter == 0){ $col .= $column; $val .= $value; }else{ $col .= ','.$column; $val .= ','.$value; }//else }//end if $counter++; }//end foreach loop $query = 'INSERT INTO HHinput ('.$col.') VALUES('.$val.')'; return $query; }//end function query_builder if(isset($_POST['submit'])) //<-----make sure that your form button name is submit. Or you can change the submit name to your button name { $query = query_builder($_POST); //<---build up your query mysql_query($query) or die(mysql_error() . '<br />' . $querySQL); //<-------execute your query }//end if PHP: