CodeΕνεργή εγγραφή Igniter: Εισαγωγή, Ενημέρωση, Διαγραφή ερωτήματος

Data is the bloodline of most applications. It needs to be stored so that it can be analyzed to provide insights and facilitate business decisions. The data is usually stored in a database. Among the chief concerns when interacting with the database are security, ease of access, and database vendor-specific implementations of Structured Query Language (SQL).

Active Record is a design pattern that makes it easy to interact with the database in a secure and eloquent way. The Active Record has the following advantages:

• Insert, update, and delete records with simple method chains.
• Submits user input securely using parameters.
• Σας επιτρέπει να εργάζεστε με πολλαπλές μηχανές βάσης δεδομένων, όπως π.χ MySQL and SQL Server without rewriting the application code.
• CodeIgniter uses drivers specific to each database engine in the background.

How to Use Active Record: Example

In this tutorial, we will use a sample database with two tables, one with orders and the other with details. This tutorial assumes you have a MySQL database installed and running. Run the following scripts to create the tutorial database:

CREATE SCHEMA ci_active_record;

USE ci_active_record;

CREATE TABLE `order_details` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`order_id` int(11) DEFAULT NULL,
`item` varchar(245) DEFAULT NULL,
`quantity` int(11) DEFAULT '0',
`price` decimal(10,2) DEFAULT '0.00',
`sub_total` decimal(10,2) DEFAULT '0.00',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT = 1;

CREATE TABLE `orders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date` timestamp NULL DEFAULT NULL,
`customer_name` varchar(245) DEFAULT NULL,
`customer_address` varchar(245) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT = 1;

The code above creates a database named ci_active_record and two tables, orders and order_details. The relationship between the two tables is defined by the column id in orders and order_id in order_details.

CodeΡύθμιση παραμέτρων βάσης δεδομένων ανάφλεξης

We will now configure our application to communicate with this database. Open the database configuration file located in application/config/database.php and locate the following lines:

'hostname' => 'localhost',
'username' => '',
'password' => '',
'database' => '',

Update the code above to the following:

'hostname' => 'localhost',
'username' => 'root',
'password' => 'letmein',
'database' => 'ci_active_record',

Σημείωση: you will need to replace the username and password with the ones that match your MySQL διαμόρφωση.

Εκτός από τις λεπτομέρειες διαμόρφωσης της βάσης δεδομένων, πρέπει επίσης να πούμε CodeIgniter to load the database library when it loads.

Βήμα 1) Open the following file: application/config/autoload.php.

Βήμα 2) Locate the $autoload array key libraries and load the database library as shown below:

$autoload['libraries'] = array('database');

Εδώ:

• The code above loads the database library when the application starts.

CodeΕνεργή εγγραφή εισαγωγής ανάφλεξης

For testing purposes, we will create a controller and define routes to interact with our application via Active Record. Create a new file application/controllers/ActiveRecordController.php and add the following code:

<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class ActiveRecordController extends CI_Controller {
public function store_order(){
$data = [
'date' => '2018-12-19',
'customer_name' => 'Joe Thomas',
'customer_address' => 'US'
];

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

echo 'order has successfully been created';
}
}

Εδώ:

• $data = […] defines an array variable data that uses database table column names as array keys and assigns values to them.
• $this->db->insert(‘orders’, $data); calls the insert method of the database library, passing in the table name orders and the array variable $data. This line generates the SQL INSERT statement using the array keys as the field names and the array values as the values to be inserted.

Now that we have created the controller method, we need to create a route that we will call to execute it. Open application/config/routes.php and add the following line:

$route['ar/insert'] = 'activerecordcontroller/store_order';

Εδώ:

• We define a route ar/insert that calls the store_order method of the ActiveRecordController.

Let us now start the web server to test our method. Run the following command to start the built-in PHP server:

cd C:\Sites\ci-app
php -S localhost:3000

Εδώ:

• The command above browses to the project directory and starts the built-in server at port 3000.

Φορτώστε τα ακόλουθα URL into your browser: http://localhost:3000/ar/insert. Θα έχετε το ακόλουθο αποτέλεσμα:

order has successfully been created

Ανοίξτε το MySQL tutorial database and check the orders table. You will see the newly created row, as shown in the image below:

CodeΕνεργή εγγραφή επιλογής ανάφλεξης

In this section, we will see how to read the records in the database and display them in the web browser as an unordered list. Add the following method to the ActiveRecordController:

public function index() {
$query = $this->db->get('orders');

echo "<h3>Orders Listing</h3>";
echo "<ul>";

foreach ($query->result() as $row) {
echo "<li>$row->customer_name</li>";
}

echo "</ul>";
}

Εδώ:

• $query = $this->db->get(‘orders’); runs the select query against the orders table, selecting all the fields.
• echo “<h3>Orders Listing</h3>”; displays an HTML heading.
• echo “<ul>”; prints the opening tag for an unordered HTML list.
• foreach ($query->result() as $row) {…} loops through the results returned from the database, and echo “<li>$row->customer_name</li>”; prints the customer_name.

Before you load the URL, you can add a couple more records to the database. Let us now define a route for the SELECT query. Open application/config/routes.php and add the following route:

$route['ar'] = 'activerecordcontroller';

Εδώ:

• The route ar points to the index method of the ActiveRecordController class. This is the default, which is why we did not specify the index method, as we did for the route that inserts records.

Υποθέτοντας ότι ο διακομιστής ιστού λειτουργεί ήδη, φορτώστε τα ακόλουθα URL: http://localhost:3000/ar. You should see results similar to the following in your web browser:

CodeΕνεργή εγγραφή ενημέρωσης ανάφλεξης

In this section, we will talk about how to use Active Record to update the database. Suppose we want to update the customer name Joe Thomas to Joe. Add the following method to the ActiveRecordController class:

public function update_order() {
$data = [
'customer_name' => 'Joe',
];
$this->db->where('id', 1);
$this->db->update('orders', $data);
echo 'order has successfully been updated';
}

Εδώ:

• $data = […] defines the fields and values we wish to update in the database table.
• $this->db->where(‘id’, 1); sets the where clause of the update query.
• $this->db->update(' orders', $data); δημιουργεί το ερώτημα ενημέρωσης SQL και το εκτελεί στη βάση δεδομένων μας.

The code above produces the following SQL statement:

UPDATE orders SET customer_name = 'Joe' WHERE id = 1;

Let us now update application/config/routes.php and add the following route:

$route['ar/update'] = 'activerecordcontroller/update_order';

Αποθηκεύστε τις αλλαγές και φορτώστε τα παρακάτω URL στο πρόγραμμα περιήγησης ιστού:

Let us now display the database records and see if the change has taken effect.

As you can see from the image above, the first record has been updated from Joe Thomas to Joe.

CodeΔιαγραφή ενεργής εγγραφής από το Igniter

We will now delete a record from the database. We will delete the record with the id of 3. Add the following method to the ActiveRecordController:

public function delete_order() {
$this->db->where('id', 3);
$this->db->delete('orders');

echo 'order has successfully been deleted';
}

Εδώ:

• $this->db->where(‘id’, 3); sets the where clause.
• $this->db->delete(‘orders’); deletes the database row in the orders table based on the criteria set using the where clause.

To execute the code above, load the following URL στο πρόγραμμα περιήγησής σας στο διαδίκτυο: http://localhost:3000/ar/delete.

Συχνές Ερωτήσεις