CODEWITHSUNDEEP
mysqlcodeigniternull
Nils BAY
Nils BAY

Reputation: 35

How to insert a NULL into a database with CodeIgniter?

I have the same problem, and do not understand the answer #5343141 titled "The proper way to insert a NULL into a database with CodeIgniter". I tried all given answers, without success.

This is my MySQL table:

The 2 last columns:

  `id_etablissement_commanditaire` int(10) unsigned DEFAULT NULL,
  `id_etablissement_payeur` int(10) unsigned DEFAULT NULL,

that makes problem, do have "NULL", so they accept NULL value (tried with phpMyAdmin, it works).

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

SET time_zone = "+00:00";

CREATE TABLE IF NOT EXISTS `contacts` (
  `id_contact` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `service` varchar(50) NOT NULL,
  `civilite` set('Monsieur','Madame') NOT NULL,
  `prenom` varchar(50) NOT NULL,
  `nom` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `tel_fixe` varchar(14) NOT NULL,
  `tel_mobile` varchar(14) NOT NULL,
  `fax` varchar(14) NOT NULL,
  `id_etablissement_commanditaire` int(10) unsigned DEFAULT NULL,
  `id_etablissement_payeur` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id_contact`),
  KEY `id_etablissement_payeur` (`id_etablissement_payeur`),
  KEY `id_etablissement_commanditaire` (`id_etablissement_commanditaire`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=28 ;

ALTER TABLE `contacts`
  ADD CONSTRAINT `contacts_ibfk_2` FOREIGN KEY (`id_etablissement_payeur`) REFERENCES `etablissements_payeurs` (`id_etablissement_payeur`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `contacts_ibfk_3` FOREIGN KEY (`id_etablissement_commanditaire`) REFERENCES `etablissements_commanditaires` (`id_etablissement_commanditaire`) ON DELETE NO ACTION ON UPDATE NO ACTION;

This is my CodeIgniter controller code :

1) I need to initialize $_POSTs to '' if they do not exist.

if ( ! isset( $_POST['action'] ) )                          $_POST['action'] = '';

if ( ! isset( $_POST['id_contact'] ) )                      $_POST['id_contact'] = '';
if ( ! isset( $_POST['service'] ) )                         $_POST['service'] = '';
if ( ! isset( $_POST['civilite'] ) )                        $_POST['civilite'] = '';
if ( ! isset( $_POST['prenom'] ) )                          $_POST['prenom'] = '';
if ( ! isset( $_POST['nom'] ) )                             $_POST['nom'] = '';
if ( ! isset( $_POST['email'] ) )                           $_POST['email'] = '';
if ( ! isset( $_POST['tel_fixe'] ) )                        $_POST['tel_fixe'] = '';
if ( ! isset( $_POST['tel_mobile'] ) )                      $_POST['tel_mobile'] = '';
if ( ! isset( $_POST['fax'] ) )                             $_POST['fax'] = '';

if ( ! isset( $_POST['id_etablissement_commanditaire'] ) ) $_POST['id_etablissement_commanditaire'] = NULL;
if ( ! isset( $_POST['id_etablissement_payeur'] ) ) $_POST['id_etablissement_payeur'] = NULL;

For the last 2 if, I tried to initialize to '' and NULL, but none works.

2) Insert into database :

$donnees = array(
'service'=> $_POST['service'],
'civilite'=> $_POST['civilite'],
'prenom'=> $_POST['prenom'],
'nom'=> $_POST['nom'],
'email'=> $_POST['email'],
'tel_fixe'=> $_POST['tel_fixe'],
'tel_mobile'=> $_POST['tel_mobile'],
'fax'=> $_POST['fax'],
'id_etablissement_commanditaire'=> $_POST['id_etablissement_commanditaire'] ,
'id_etablissement_payeur'=> $_POST['id_etablissement_payeur']);

$this->db->insert('contacts', $donnees);

If I add an empty record CodeIgniter says :

Error Number: 1452

Cannot add or update a child row: a foreign key constraint fails (`france-medical-transport`.`contacts`, CONSTRAINT `contacts_ibfk_2` FOREIGN KEY (`id_etablissement_payeur`) REFERENCES `etablissements_payeurs` (`id_etablissement_payeur`) ON DELETE NO ACTION )

INSERT INTO `contacts` (`service`, `civilite`, `prenom`, `nom`, `email`, `tel_fixe`, `tel_mobile`, `fax`, `id_etablissement_commanditaire`, `id_etablissement_payeur`) VALUES ('', '', '', '', '', '', '', '', '', '')

Upvotes: 1

Views: 3832

Answers (2)

Nils BAY
Nils BAY

Reputation: 35

A good way to do this is :

$this->db->set('id_etablissement_commanditaire' , $_POST['id_etablissement_commanditaire']  , FALSE );
$this->db->set('id_etablissement_payeur'        , $_POST['id_etablissement_payeur']         , FALSE );

$this->db->insert('contacts');

This way, the last argument FALSE, means, NULL values won't be escaped, then I have NULL and not 'NULL'.

It works.

Nota : FALSE remove '', but it removes `` as well.

Upvotes: 0

Chris Barcroft
Chris Barcroft

Reputation: 572

Try changing your DB insert script to not include the columns at all if they have no value:

$donnees = array(
'service'=> $_POST['service'],
'civilite'=> $_POST['civilite'],
'prenom'=> $_POST['prenom'],
'nom'=> $_POST['nom'],
'email'=> $_POST['email'],
'tel_fixe'=> $_POST['tel_fixe'],
'tel_mobile'=> $_POST['tel_mobile'],
'fax'=> $_POST['fax']);

if ($this->input->post('id_etablissement_commanditaire')) $donnees['id_etablissement_commanditaire'] = $this->input->post('id_etablissement_commanditaire');

if ($this->input->post('id_etablissement_payeur')) $donnees['id_etablissement_payeur'] = $this->input->post('id_etablissement_payeur');

$this->db->insert('contacts', $donnees);

This will allow MySQL to insert the default NULL value without getting confused by any values you're trying to pass in, like an explicit NULL or an empty string.

Upvotes: 1

Related Questions