Insert NULL values on DB (method)

Question

UPDATE----

Afer using this method:

                        foreach($_POST as $k => $v) { 
  $params[] = empty($v)? "NULL":$v;
} 

$params_string_values = "'" . implode("','",$params) . "'";
$param_name_list = "tu_id,tu_status,tu_name,tu_fk_tt_id,tu_mon_1_s,tu_mon_1_e,tu_mon_2_s,tu_mon_2_e,tu_mon_3_s,tu_mon_3_e,tu_tue_1_s,tu_tue_1_e,tu_tue_2_s,tu_tue_2_e,tu_tue_3_s,tu_tue_3_e,tu_wed_1_s,tu_wed_1_e,tu_wed_2_s,tu_wed_2_e,tu_wed_3_s,tu_wed_3_e,tu_thu_1_s,tu_thu_1_e,tu_thu_2_s,tu_thu_2_e,tu_thu_3_s,tu_thu_3_e,tu_fri_1_s,tu_fri_1_e,tu_fri_2_s,tu_fri_2_e,tu_fri_3_s,tu_fri_3_e,tu_sat_1_s,tu_sat_1_e,tu_sat_2_s,tu_sat_2_e,tu_sat_3_s,tu_sat_3_e,tu_sun_1_s,tu_sun_1_e,tu_sun_2_s,tu_sun_2_e,tu_sun_3_s,tu_sun_3_e";
$param_values = "'','1',{$params_string_values}";

$insert_query = mysql_query("INSERT into turn_conf( {$param_name_list} ) values ({$param_values})");

It creates a valid query as it seems (here I paste it), but no NULL value is stored on databse, all NULL values go to database as "00:00":

INSERT into turn_conf( tu_id,tu_status,tu_name,tu_fk_tt_id,tu_mon_1_s,tu_mon_1_e,tu_mon_2_s,tu_mon_2_e,tu_mon_3_s,tu_mon_3_e,tu_tue_1_s,tu_tue_1_e,tu_tue_2_s,tu_tue_2_e,tu_tue_3_s,tu_tue_3_e,tu_wed_1_s,tu_wed_1_e,tu_wed_2_s,tu_wed_2_e,tu_wed_3_s,tu_wed_3_e,tu_thu_1_s,tu_thu_1_e,tu_thu_2_s,tu_thu_2_e,tu_thu_3_s,tu_thu_3_e,tu_fri_1_s,tu_fri_1_e,tu_fri_2_s,tu_fri_2_e,tu_fri_3_s,tu_fri_3_e,tu_sat_1_s,tu_sat_1_e,tu_sat_2_s,tu_sat_2_e,tu_sat_3_s,tu_sat_3_e,tu_sun_1_s,tu_sun_1_e,tu_sun_2_s,tu_sun_2_e,tu_sun_3_s,tu_sun_3_e ) values ('','1','12345555','1','10:00','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL')

This is what it records on DB:

I have this query, in which sometimes variables '$va1', '$val2' and '$val3' will have no value:

$insert = mysql_query("INSERT INTO turn_conf (tu_id,value1,value2,value3) VALUES ('','$va1','$val2','$val3')") or die (mysql_error());

In case any of these variables have no value stored, anything related to it must be sent to the DB (in order to store a NULL value on DB), for exampe if only '$val1' stores info, the final query must be:

$insert = mysql_query("INSERT INTO turn_conf (tu_id,value1) VALUES ('','$va1')") or die (mysql_error());

To solve this I have created an structure for each variable, checking wether it stores something or not, and in case it doesn't, just declarating nothing and sending nothing:

if ($_POST['value1'] == ""){
    $val1_p = "";
    $val1_s = ""; 
}else {
    $val1_p = ",value1";
    $val1_sv = $_POST['value1'];
    $val1_s = ", '$val1_sv'" ;}  

if ($_POST['value2'] == ""){
    $val2_p = "";
    $val2_s = ""; 
}else {
    $val2_p = ",value2";
    $val2_sv = $_POST['value2'];
    $val2_s = ", '$val2_sv'" ;}  

if ($_POST['value3'] == ""){
    $val3_p = "";
    $val3_s = ""; 
}else {
    $val3_p = ",value3";
    $val3_sv = $_POST['value3'];
    $val3_s = ", '$val3_sv'" ;}

and:

$insert = mysql_query("INSERT INTO turn_conf (tu_id $val1_p $val2_p $val3_p) VALUES ('' $val1_s $val2_s $val3_s)") or die (mysql_error());

This works, and creates the right query, but id like to know if you find ths method proper, or if it would be better to choose another more efficient one. Please not in this example I used only 3 variables, but this query on real has 43 variables, I make this question due to the amount of data.

as @Mureinik suggest use prepared statements, and allow NULL value insert so if you have no correct value simple insert NULL. — gafreax
– gafreax, Commented May 6, 2014 at 9:37
@Daan this is working now perfectly by this way, whats wrong with it? — Biomehanika
– Biomehanika, Commented May 6, 2014 at 9:39
@gafreax, thank you i'll take a look now at it. NULL values are allowed on this DB fields but if I send a emty value, DB takes "00:00:00" value (these are time value type fields) — Biomehanika
– Biomehanika, Commented May 6, 2014 at 9:40

Olivier Van Bulck · Accepted Answer · 2014-05-06 09:45:58Z

2

$insert = mysql_query("INSERT INTO turn_conf (tu_id,value1,value2,value3) VALUES ('','".((isset($va1))?"'".$va1."'":"NULL")."','".((isset($va2))?"'".$va2."'":"NULL")."','".((isset($va3))?"'".$va3."'":"NULL")."')") or die (mysql_error());

Basically you want to test if the value is set. We use the short if-notation for this:

isset($va1)?"'".$va1."'":"NULL"

If $va1 is set (has a value), we will put "'value'" in the query, otherwise "NULL" for an empty value.

If you want to test on an empty string too:

(isset($va1) && $va1 != '')?"'".$va1."'":"NULL"

answered May 6, 2014 at 9:45

Olivier Van Bulck

7861 gold badge8 silver badges24 bronze badges

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

Comments

gafreax · Accepted Answer · 2014-05-06 10:43:16Z

1

If you don't use prepared statement Try this:

foreach($_POST as $k => $v) { 
  $params[] = empty($v)? "NULL":$v;
} 
mysql_query("insert into turn_conf(field1,field2...) values(" . implode(",",$params).  ");

If you use prepared statement (better!) try something like this:

foreach($_POST as $v) {
 $params[] = $v;
}
$sth = $dbh->prepare("INSERT INTO turn_conf (tu_id $val1_p $val2_p $val3_p) VALUES (?,?,?)");
$sth->execute($params);

So simple!

PS: This is an example but doesn't use directly $_POST value, filter it before (http://www.php.net/manual/en/function.filter-input.php, and http://www.php.net/manual/en/filter.filters.sanitize.php). example:

$field_int= filter_input(INPUT_POST, 'field1', FILTER_SANITIZE_NUMBER_INT);

UPDATE

you use this code:

 $insert = mysql_query("INSERT into turn_conf(tu_id,tu_name,tu_status,tu_fk_tt_id,tu_mon_1_s,tu_mon_1_e,tu_mon_2_s,t‌u_mon_2_e,tu_mon_3_s,tu_mon_3_e,tu_tue_1_s,tu_tue_1_e,tu_tue_2_s,tu_tue_2_e,tu_tu‌e_3_s,tu_tue_3_e,tu_wed_1_s,tu_wed_1_e,tu_wed_2_s,tu_wed_2_e,tu_wed_3_s,tu_wed_3_‌e,tu_thu_1_s,tu_thu_1_e,tu_thu_2_s,tu_thu_2_e,tu_thu_3_s,tu_thu_3_e,tu_fri_1_s,tu‌_fri_1_e,tu_fri_2_s,tu_fri_2_e,tu_fri_3_s,tu_fri_3_e,tu_sat_1_s,tu_sat_1_e,tu_sat‌_2_s,tu_sat_2_e,tu_sat_3_s,tu_sat_3_e,tu_sun_1_s) values('','$newTurnName','1','$newTurnType'," . implode(",",$params). "))")

but change it like this:

$params_string_values = "'" . implode("','",$params) . "'";
$param_name_list = "tu_id,tu_name,tu_status,tu_fk_tt_id,tu_mon_1_s,tu_mon_1_e,tu_mon_2_s,t‌u_mon_2_e,tu_mon_3_s,tu_mon_3_e,tu_tue_1_s,tu_tue_1_e,tu_tue_2_s,tu_tue_2_e,tu_tu‌e_3_s,tu_tue_3_e,tu_wed_1_s,tu_wed_1_e,tu_wed_2_s,tu_wed_2_e,tu_wed_3_s,tu_wed_3_‌e,tu_thu_1_s,tu_thu_1_e,tu_thu_2_s,tu_thu_2_e,tu_thu_3_s,tu_thu_3_e,tu_fri_1_s,tu‌_fri_1_e,tu_fri_2_s,tu_fri_2_e,tu_fri_3_s,tu_fri_3_e,tu_sat_1_s,tu_sat_1_e,tu_sat‌_2_s,tu_sat_2_e,tu_sat_3_s,tu_sat_3_e,tu_sun_1_s";
$param_values = "'','{$newTurnName}','1','{$newTurnType}',{$param_string_values}";

$insert_query = mysql_query("INSERT into turn_conf( {$param_name_list} ) values ({$param_values})");

edited May 6, 2014 at 10:43

answered May 6, 2014 at 9:43

gafreax

4103 silver badges13 bronze badges

14 Comments

Biomehanika Over a year ago

I am about to learn about prepared statements and try this method in case of success Ill tick. ill keep you informed, thank you for your attention :)

gafreax Over a year ago

thank you! give me a feedback if u have success or not:D

Biomehanika Over a year ago

I have choosen the first option, ant it gives me this error: 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 ':00,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,N' at line 1

Biomehanika Over a year ago

This is the query: $insert = mysql_query("INSERT into turn_conf(tu_id,tu_name,tu_status,tu_fk_tt_id,tu_mon_1_s,tu_mon_1_e,tu_mon_2_s,tu_mon_2_e,tu_mon_3_s,tu_mon_3_e,tu_tue_1_s,tu_tue_1_e,tu_tue_2_s,tu_tue_2_e,tu_tue_3_s,tu_tue_3_e,tu_wed_1_s,tu_wed_1_e,tu_wed_2_s,tu_wed_2_e,tu_wed_3_s,tu_wed_3_e,tu_thu_1_s,tu_thu_1_e,tu_thu_2_s,tu_thu_2_e,tu_thu_3_s,tu_thu_3_e,tu_fri_1_s,tu_fri_1_e,tu_fri_2_s,tu_fri_2_e,tu_fri_3_s,tu_fri_3_e,tu_sat_1_s,tu_sat_1_e,tu_sat_2_s,tu_sat_2_e,tu_sat_3_s,tu_sat_3_e,tu_sun_1_s) values('','$newTurnName','1','$newTurnType'," . implode(",",$params). "))");

gafreax Over a year ago

change implode(",",$params) with "'" . implode("','",$params). Let me know if it works

|

Collectives™ on Stack Overflow

Insert NULL values on DB (method)

2 Answers 2

Comments

14 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

14 Comments

Your Answer

Sign up or log in

Post as a guest

Related