MySQL rows deleted on varchar column

See also questions close to this topic

• MySQL If else construct syntax

  Im having some trouble with using the if else construct in sql triggers in MySQL. The tables below are just used for testing.

  First Table:

    CREATE TABLE `test2`.`t1` (
    `c1` VARCHAR(5) NOT NULL,
    `c2` VARCHAR(45) NULL,
    `c3` VARCHAR(45) NULL,
    PRIMARY KEY (`c1`));
  

  My second table:

      CREATE TABLE `test2`.`t2` (
    `cc1` VARCHAR(5) NOT NULL,
    `cc2` VARCHAR(45) NULL,
    `cc3` VARCHAR(45) NULL,
    PRIMARY KEY (`cc1`));
  

  and the third:

  CREATE TABLE `test`.`t3` (
    `ccc1` VARCHAR(5) NOT NULL,
    `ccc2` VARCHAR(45) NULL,
    `ccc3` VARCHAR(45) NULL,
    PRIMARY KEY (`cc1`));
  

  c1 is the primary key for both cc1 and ccc1.

  The trigger I am using is:

  DROP TRIGGER IF EXISTS `test`.`t1_AFTER_UPDATE`;
  
  DELIMITER $$
  USE `test`$$
  CREATE DEFINER=`root`@`localhost` TRIGGER `t1_AFTER_UPDATE` AFTER UPDATE ON 
  `t1` FOR EACH ROW 
  BEGIN
  DECLARE start varchar(1);
  set start=substring((c2),1,1);
  
   IF(start='S') THEN
       UPDATE t2
       SET cc2 = NEW.c2  
       WHERE cc1 = NEW.c1;
   else
  
      UPDATE t3
      SET ccc2 = NEW.c2  
      WHERE ccc1 = NEW.c1;
   END IF;
    END$$
    DELIMITER ;
  

  Basically, if the user updates t1.c2, then t2.cc2 OR t3.ccc2 must be updated. If t1.c2 of the NEW.c2 starts with the letter S, then t2.cc2 must be updated, else t3.ccc2 must be updated.

  The query executes without a hitch but wont let me update the value of c2:

  Executing:
  UPDATE `test2`.`t1` SET `c2` = '24' WHERE (`c1` = 'M123');
  
  Operation failed: There was an error while applying the SQL script to the database.
  ERROR 1054: 1054: Unknown column 'c1' in 'field list'
  SQL Statement:
  
• How to compare with time range with a MySQL Table

  I have this table in my MySQL database:

     ID   First_Name  Date_Serve     Time_Serve   RFID_Tag    Amount_Serve
     --------------------------------------------------------------
      1     John      17-11-2018      10:00:00    hdgdYun8JH   Small
      2     George    18-11-2018      11:00:00    kdjfHluhHB    Big
  

  and have this code for MySQL query:
  

  Private Sub TextBox3_TextChanged(sender As Object, e As EventArgs) Handles TextBox3.TextChanged
      Dim str As String = "Server=localhost;Port=3306;Database=testdb;Uid=root;Pwd=password123"
  
      Using con As New MySqlConnection(str)
          Dim query As String = 
              "select * from testdata where rfid_tag='" & TextBox3.Text & 
              "' and Date_Serve= '" & Date.Now.ToString("yyyy-MM-dd ") & 
              "' and Time_Serve= '" & Date.Now.ToString("HH:mm:ss ") & 
              "' or amount_serve=''"
  
          Dim cm As New MySqlCommand(query, con)
          con.Open()
  
          Dim rd As MySqlDataReader = cm.ExecuteReader()
  
          ' Check if any rows exist
          If rd.Read() Then
              If rd.GetString(3) = "small" Then
                  MessageBox.Show("small")
              ElseIf rd.GetString(3) = "Big" Then
                  MessageBox.Show("big")
              ElseIf rd.GetString(3) = "Midium" Then
                  MessageBox.Show("Mid")
              End If
          End If
      End Using
  End Sub
  

  What I am looking for to do is with time to make like delay 15 minutes from time_serve.
  For example if the time is 10:00:00 AM need to serve him his amount at 10 AM until 10:15 AM.
  How can I achieve this?

  With my code, I can serve him at exact time but I need something like a range.
• How to create 2 PDO database connection instances PHP

  I currently have a class which creates one database connection, however I would like to create another connection too. I have tried copying the class structure but just renaming the variables and functions however that doesn't work and it seems like it doesn't detect where my new PDO connection is as I get error Uncaught Error: Call to a member function prepare() on null in. What is the best approach to take in my case when creating 2 database connections?

  config.php:

  <?php
  
  class Database
  {   
      private $host = "localhost";
      private $db_name = "database1";
      private $username = "root";
      private $password = "";
      public $conn;
  
      public function dbConnection()
      {
  
          $this->conn = null;    
          try
          {
              $this->conn = new PDO("mysql:host=" . $this->host . ";dbname=" . $this->db_name, $this->username, $this->password);
              $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);   
          }
          catch(PDOException $exception)
          {
              echo "Connection error: " . $exception->getMessage();
          }
  
          return $this->conn;
      }
  }
  
  ?>
  

  class.user.php:

  class USER
  {   
  
      private $conn;
  
      public function __construct()
      {
          $database = new Database();
          $db = $database->dbConnection();
          $this->conn = $db;
      }
  
      public function runQuery($sql)
      {
          $stmt = $this->conn->prepare($sql);
          return $stmt;
      }
  }