INSERT INTO mysql only if input text is not empty - Codeigniter

Question

I m trying to insert some data into a table but I want to only insert if the variable is not empty.

My model code below.

$query_str = "INSERT INTO todo (";

     $query_and=array();

          if( !empty($one_input)){
              $query_and[]= 'one';
          }
          if( !empty($two_input)){
              $query_and[]=  'two';
          }
          if( !empty($three_input)){
               $query_and[]=  'three';
           }
        $query_str .= implode(', ', $query_and);
        $query_str .= ') VALUES (?, ?, ?)';

        $query_data=array();

            if( !empty($one_input)){ 
                $query_data[]= $one_input;
            }
            if( !empty($two_input)){ 
                $query_data[]= $two_input;
            }
            if( !empty($three_input)){ 
                $query_data[]= $three_input;
            }
         $query_dat = implode(', ', $query_data);
         $query_dat .= ");";

    $q = $this->db->query($query_str, $query_dat);

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

INSERT INTO todo (one, two, three) VALUES ('val1, val2, val3);',

i think you need to check if the variables were empty just before you run the $q = $this->db->query.... — Joey Hipolito
– Joey Hipolito, Commented Mar 18, 2014 at 1:14
I have three text inputs ... and adding the values to the array if they're not empty works. I just don't know how to format the $query_dat array. If I only have two values I get this: INSERT INTO todo (one, two) VALUES ('one, two);', The problem is here: ('one, two);',. — Ciprian
– Ciprian, Commented Mar 18, 2014 at 1:18

smozgur · Accepted Answer · 2014-03-18 01:32:39Z

1

I would use following syntax in your situation:

INSERT INTO todo
SET
    one='val1',
    two='val2',
    three='val3'

You can set field=value pairs in an array easier and add to the statement by imploding.

Added sample below:

        if( !empty($one_input)){ 
            $query_data[]= "one='$one_input'";
        }
        if( !empty($two_input)){ 
            $query_data[]= "two='$two_input'";
        }
        if( !empty($three_input)){
            $query_data[]= "three='$three_input'";
        }

        $query = "INSERT INTO todo SET " . implode(",", $query_data);

edited Mar 18, 2014 at 1:32

answered Mar 18, 2014 at 1:24

smozgur

1,7941 gold badge16 silver badges24 bronze badges

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

1 Comment

smozgur Over a year ago

You're welcome. Of course I would also check if $query_data has any item or not before proceeding with the query execution. Not exists in the sample I provided but should be done since it is the possible failure reason in case $query_data is empty.

Gordon Linoff · Accepted Answer · 2014-03-18 01:16:32Z

0

If this is your query:

INSERT INTO todo(one, two, three) VALUES ('val1, val2, val3);'

Then you have a problem with single quotes. You probably want:

INSERT INTO todo(one, two, three) VALUES ('val1', 'val2', 'val3');

or

INSERT INTO todo(one, two, three) VALUES (val1, val2, val3);

answered Mar 18, 2014 at 1:16

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Comments

Logan Wayne · Accepted Answer · 2014-03-18 01:20:20Z

0

Can you please elaborate more what you want for your output? And input?

      if(empty($one_input) || empty($two_input) || empty($three_input)){

      /* Do Nothing */

      }

      else {

      query str="INSERT INTO todo('','','') VALUES ('','','')";

      }

answered Mar 18, 2014 at 1:20

Logan Wayne

5,98716 gold badges33 silver badges50 bronze badges

Collectives™ on Stack Overflow

INSERT INTO mysql only if input text is not empty - Codeigniter

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related