Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- modul:m290:learningunits:lu09:theorie:03 [2024/11/05 13:37] – vdemir
+++ modul:m290:learningunits:lu09:theorie:03 [2024/11/05 14:51] (aktuell) – vdemir
@@ Zeile 6: / Zeile 6: @@
   - How to perform the CRUD operations on the server
   - How to perform the CRUD operations by using PoSTMAN
+  -
 ===== Sources =====
   * {{ :modul:m290:learningunits:lu09:theorie:mysecondserver.zip | SampleServer that can perform CRUD}}
@@ Zeile 14: / Zeile 13: @@
 At last chapter of our database journey we want to perform all CRUD operations within the server AND the client (POSTMAN). For reasons of efficiency we are going to start with the R = READ of CRUD.
-==== R = READ: Fetching list of data ====
+==== R - READ: Fetching list of data ====
 First of all, we want to display the list of users, which are store in the table users. The following code show how to get that list from the database.
@@ Zeile 30: / Zeile 29: @@
   });
-{{:modul:m290:learningunits:lu09:theorie:crud_r1.png?800 | c_R_ud performance in the presentation (POSTMAN) and logic layer (Node Server)}
+{{:modul:m290:learningunits:lu09:theorie:crud_r1.png?800| C**R**UD performance in the presentation (POSTMAN) and logic layer (Node Server}}
-==== R = READ: Getting one spefific row of data ====
+==== R - READ: Getting one spefific row of data ====
 If we want one speficific individual to be displayed, we need to make some changes, such as filtering a user_id, as shown in the following code below.
@@ Zeile 53: / Zeile 52: @@
   });
-{{:modul:m290:learningunits:lu09:theorie:crud_r2.png?800|c_R_ud performance in the presentation (POSTMAN) and logic layer (Node Server)}
+{{:modul:m290:learningunits:lu09:theorie:crud_r2.png?800|C**R**UD performance in the presentation (POSTMAN) and logic layer (Node Server}}
-==== Deleting on user from the table ====
+==== D - Deletion of one specific record ====
-// DELETE user method
+If we want to delete one specific row of data, we need another method to perform that.
-app.delete('/user/:id', (req, res) => {
-    const userId = req.params.id;
-    const deleteQuery = 'DELETE FROM users WHERE user_id = ?';
+  app.delete('/user/:id', (req, res) => {
+      const userId = req.params.id;
+      const deleteQuery = 'DELETE FROM users WHERE user_id = ?';
+      db.query(deleteQuery, [userId], (err, result) => {
+          if (err) {
+              res.status(500).send('Error deleting user');
+          } else if (result.affectedRows === 0) {
+              res.status(404).send('User not found');
+          } else {
+              res.send(`User with ID ${userId} deleted successfully`);
+          }
+      });
+  });
-    db.query(deleteQuery, [userId], (err, result) => {
+{{:modul:m290:learningunits:lu09:theorie:crud_d.png?800|How to perform a deletion }}
-        if (err) {
-            res.status(500).send('Error deleting user');
+==== U - Update of one specfic row ====
-        } else if (result.affectedRows === 0) {
+Well, if we want to update one row of data, we need the http method PUT.
-            res.status(404).send('User not found');
-        } else {
+  app.put('/user/:id', (req, res) => {
-            res.send(`User with ID ${userId} deleted successfully`);
+      const userId = req.params.id;
-        }
+      const { username, email } = req.query; // Get username and email from query parameters
-    });
+      // Ensure that at least one field is provided
-});
+      if (!username && !email) {
-==== Vocabulary ====
+          return res.status(400).send('At least one field (username or email) must be provided for update.');
-^English ^ Deutsch ^
+      }
-| ...| ...|
+      // Construct the update query
+      const updateQuery = 'UPDATE users SET username = ?, email = ? WHERE user_id = ?';
+      const values = [username || null, email || null, userId]; // Use null for any field not provided
+      db.query(updateQuery, values, (err, result) => {
+          if (err) {
+              res.status(500).send('Error updating user');
+          } else if (result.affectedRows === 0) {
+              res.status(404).send('User not found');
+          } else {
+              res.send(`User with ID ${userId} updated successfully`);
+          }
+      });
+  });
+  {{:modul:m290:learningunits:lu09:theorie:crud_u.png?800| Update of one row of data}}
+==== C - CREATE: Insert a new row into the db ====
+Finally, with the INSERT operation, we complete our CRUD requirements.
+  // insert a row
+  app.post('/user', (req, res) => {
+      const { username, email } = req.query; // Get username and email from query parameters
+      // Ensure that both fields are provided
+      if (!username || !email) {
+          return res.status(400).send('Username and email are required for insertion.');
+      }
+      // Construct the insert query
+      const insertQuery = 'INSERT INTO users (username, email) VALUES (?, ?)';
+      const values = [username, email]; // Use provided fields for insertion
+      db.query(insertQuery, values, (err, result) => {
+          if (err) {
+              console.error('Error inserting user:', err);
+              return res.status(500).send('Error inserting user');
+          }
+         // Respond with the ID of the newly created user
+          res.status(201).send(`User added with ID: ${result.insertId}`);
+      });
+  });
+{{:modul:m290:learningunits:lu09:theorie:crud_c.png?600| How to insert a row into a database table}}
 ----
 [[https://creativecommons.org/licenses/by-nc-sa/4.0/|{{https://i.creativecommons.org/l/by-nc-sa/4.0/88x31.png}}]] Volkan Demir