CodeIgniter using MySQL

[eluser]InsiteFX[/eluser]
@toopay
I said that because no one here mentioned the InnoDB.

MySQL defaults to the InnoDB database now when you create a new database.

But you should still specify the Engine=InnoDB for the type.

Just like a lot of users create their database and then wonder why utf8 and utf8_general_ci doe's not work!

And the answer is because when you create a new database MySQL sets the collation to a default of latin1_swedish_ci !

With phpMyAdmin you have to click on the new database before entering any records and then click on the Operations tab, then if you look at the bottom of the page you will see the collation is set to latin1_swedish_ci. To change enter a new one either utf8_general_ci or utf8_unicode_ci click GO!

If the database is already containing records then you need to make a SQL dump change the collation and the use the dump to put them back or they will get corrupted.

InsiteFX

[eluser]MaxEisley[/eluser]
But i cannot delete ID adress or customer ID, all writes error.

[eluser]mihaibaboi[/eluser]
Are you sure you're giving us the whole table structure? If your tables look exactly like you posted at the beginning of the discussion, you should be able to delete the address entry without any problems. If it's dependent on another table that I don't know about, I can't help you.

[eluser]MaxEisley[/eluser]
DELETE FROM `dbs`.`address` WHERE `address`.`address_ID` =1

MySQL show: Documentation
#1451 - Cannot delete or update a parent row: a foreign key constraint fails (`dbs`.`customer`, CONSTRAINT `FK_customer_address` FOREIGN KEY (`addressID`) REFERENCES `address` (`address_ID`))

[eluser]mihaibaboi[/eluser]
This calls for some more research. I'll whip up some test case tonight when I get home from the office.

[eluser]MaxEisley[/eluser]
Full DB is bigger but there is no more used adresa ID so it isn't important to show it. I am from Czech Republic and everything have to translate to Eng

[eluser]mihaibaboi[/eluser]
I see. There's no point i showing the whole DB if it's not relevant. I'll post a working example as soon as I can, and you can compare to what you have to figure out what's wrong.

[eluser]toopay[/eluser]
There something wrong with your database! Try run this SQL query in phpmyadmin or mysql daemon, to correct the foreign key relationship :

Code:

ALTER TABLE `customer` ADD CONSTRAINT `FK_customer_address` FOREIGN KEY (`addressID`) REFERENCES `address` (`addressID`);

For more information, look here

[eluser]MaxEisley[/eluser]
Ok i deleted FK keys cause i think its done badly.

Now i have another problem with last_insert_id();

This is my data_model:

function createaddress ($town, $street, $CP, $PSC) {
$data = array (
'Mesto' => $town,
'Ulice' => $street,
'CP' => $CP,
'PSC' => $PSC
);

$this->db->insert('adresa', $data);
}

function createcustomer ($phone, $email, $name, $surname) {
$data = array (
'Telefon' => $phone,
'Email' => $email,
'Jmeno' => $name,
'Prijmeni' => $surname,
'adresaID' => 'last_insert_id()'
);

$this->db->insert('zakaznik', $data);
}

And this is controller:
$this->data_model->createaddress($this->input->post('Town'), $this->input->post('Street'), $this->input->post('StreetN'), $this->input->post('ZIP'));

$this->data_model->createcustomer($this->input->post('Phone'), $this->input->post('Email'), $this->input->post('FirstN'), $this->input->post('SecondN'));

In one step (function, send form) i want to add all info about customer, address is in another table then customer corresponding with FK (which is not set but I know about that).

last_insert_id() doesnt work because adresaID (FK) has 0 and not ID address from table address.

Where is problem or how can i do that better ?

[eluser]InsiteFX[/eluser]
See if this will work for you.

Code:

function createaddress ($town, $street, $CP, $PSC) {
    $data = array (
    'Mesto' => $town,
    'Ulice' => $street,
    'CP'    => $CP,
    'PSC'   => $PSC
    );
    
    $this->db->insert('adresa', $data);
    return $this-db->last_insert_id();
  }

// assign the return value from above to a string variable then pass it to the method below!
  function createcustomer ($phone, $email, $name, $surname $last_insert_id) {
    $data = array (
    'Telefon'  => $phone,
    'Email'    => $email,
    'Jmeno'    => $name,
    'Prijmeni' => $surname,
    'adresaID' => $last_insert_id
    );
  
    $this->db->insert('zakaznik', $data);
   }

and please use code tags when posting code!

Code:

// remove spaces at end!
[code ]
[/code ]

InsiteFX