John Davidson

Message:

I am very very new to PHP (and Codeigniter as well). I am trying to build a small web app with codeigniter and I have the following the situation.

• I am using Codeigniter 3 & Bootstrap 3


• I am using Codeigniter's Query Builder Class to delete items from the database


• I have two table in the database named movie & category


• movie has a column named category_id with a foreign key reference to the category table's primary key category_id with constraints "ON DELETE RESTRICT"

Now if I want to delete such a category which has a reference to the movie table, then the DELETE operation will fail which is normal. But I am having issue to handle such error in PHP.

Here is my PHP Codeigniter code to delete a query.

public function deleteCategory()

  {

    if (isset($_POST['cat-id'])) {

      $result = $this->db->delete('category', array('category_id' => $_POST['cat-id']));

      if ($result) {

        var_dump("Deleted Successfully");

      } else {

        var_dump("Failed To Delete");

      }

    }

  }

This code works fine when there is no foreign key constraints. But it shows the following error when there is a foreign key constrains

Error Number: 1451

Cannot delete or update a parent row: a foreign key constraint fails
(demoapp.movie, CONSTRAINT server_ibfk_1 FOREIGN KEY
(category_id) REFERENCES category (category_id))

DELETE FROM category WHERE category_id = '1'

Filename: C:/xampp/htdocs/demoapp/system/database/DB_driver.php

Line Number: 691

I followed several suggestions from other stackoverflow's answer but it didn't work for me.

So, my question is how to handle this situation in Codeigniter? Also it will be helpful to know how to deal with Codeigniter's other query build classes error as well.

Thanks in advance.

---

My Answer:
and I want to retrieve data from a MySQL database using the Query Builder class. Here is an example of how you can use the Query Builder to get results from a database table:

php
$this->load->database();
$query = $this->db->get('users');
$result = $query->result();

foreach ($result as $row) {
echo $row->username;
echo $row->email;
}


In this example, we first load the database library in Codeigniter. Then, we use the `get()` method of the Query Builder class to retrieve all records from the `users` table. We store the result in the `$query` variable and then use the `result()` method to get an array of objects representing the rows in the result set.

We then loop through the result array and access the properties of each object to display the `username` and `email` fields of each user.

I hope this helps you get started with retrieving data from a MySQL database using Codeigniter's Query Builder class. Let me know if you have any further questions!