Firdaus Archive

If you need to handle multiple detail records for a single header record in your stored procedure, you can modify the procedure to accept a JSON array of detail records. This way, you can pass multiple details in a single call to the stored procedure.

### Updated Stored Procedure

Here’s how you can create a stored procedure that accepts multiple detail records in JSON format:

#### Table Structure Example

Assuming the same table structure as before:

```sql

CREATE TABLE header_table (

    id INT AUTO_INCREMENT PRIMARY KEY,

    header_info VARCHAR(255),

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

CREATE TABLE detail_table (

    id INT AUTO_INCREMENT PRIMARY KEY,

    header_id INT,

    detail_info VARCHAR(255),

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (header_id) REFERENCES header_table(id)

);

```

#### Stored Procedure

Here’s the updated stored procedure:

```sql

DELIMITER //

CREATE PROCEDURE SaveOrUpdateHeaderDetail(

    IN p_header_id INT,

    IN p_header_info VARCHAR(255),

    IN p_details JSON

)

BEGIN

    DECLARE v_header_exists INT;

    -- Check if the header exists

    SELECT COUNT(*) INTO v_header_exists

    FROM header_table

    WHERE id = p_header_id;

    -- If header exists, update it

    IF v_header_exists > 0 THEN

        UPDATE header_table

        SET header_info = p_header_info,

            created_at = NOW()

        WHERE id = p_header_id;

    -- If header does not exist, insert a new header

    ELSE

        INSERT INTO header_table (header_info)

        VALUES (p_header_info);

        -- Get the last inserted header ID

        SET p_header_id = LAST_INSERT_ID();

    END IF;

    -- Clear existing details for the header

    DELETE FROM detail_table WHERE header_id = p_header_id;

    -- Insert new details from JSON

    INSERT INTO detail_table (header_id, detail_info)

    SELECT p_header_id, detail_info

    FROM JSON_TABLE(p_details, '$[*]' COLUMNS (detail_info VARCHAR(255) PATH '$')) AS jt;

END //

DELIMITER ;

```

### Explanation

1. **Parameters**:

   - `p_header_id`: The ID of the header to update or `NULL` for a new header.

   - `p_header_info`: The information for the header.

   - `p_details`: A JSON array containing detail records.

2. **Check for Header Existence**: The procedure checks if the header exists.

3. **Update or Insert Header**: If the header exists, it updates the header information; if not, it inserts a new header and retrieves the new `header_id`.

4. **Clear Existing Details**: Before inserting new details, it deletes any existing details associated with the `header_id`. This ensures that you only keep the latest details.

5. **Insert New Details**: It uses `JSON_TABLE` to extract detail information from the JSON array and insert it into the `detail_table`.

### Usage

You can call this stored procedure like this:

```sql

CALL SaveOrUpdateHeaderDetail(

    NULL, 

    'New Header Info', 

    '["Detail 1", "Detail 2", "Detail 3"]'

);

```

Or to update an existing header with new details:

```sql

CALL SaveOrUpdateHeaderDetail(

    1, 

    'Updated Header Info', 

    '["Updated Detail 1", "Updated Detail 2", "Updated Detail 3"]'

);

```

### Note

- Ensure that your MySQL version supports JSON functions (MySQL 5.7 and later).

- Adjust the data types and lengths according to your actual table definitions.

- If you want to keep existing details and just add new ones, you can modify the logic accordingly.

______________________________________________________________________

To create a stored procedure in MySQL that can save or update data in both a header table and a detail table, we first need to define the structure of the tables. Below is an example of how to create the tables, the stored procedure, and how to call it from PHP using PDO.

### Step 1: Create the Tables

```sql

CREATE TABLE header (

    id INT AUTO_INCREMENT PRIMARY KEY,

    header_info VARCHAR(255),

    header_info2 VARCHAR(255),

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    created_by VARCHAR(255),

    modified_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    modified_by VARCHAR(255)

);

CREATE TABLE detail (

    id INT AUTO_INCREMENT PRIMARY KEY,

    header_id INT,

    detail_info VARCHAR(255),

    detail_info2 VARCHAR(255),

    detail_info3 VARCHAR(255),

    detail_info4 VARCHAR(255),

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    modified_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (header_id) REFERENCES header(id) ON DELETE CASCADE

);

```

### Step 2: Create the Stored Procedure

```sql

DELIMITER //

CREATE PROCEDURE SaveOrUpdateHeaderAndDetails(

    IN p_id INT,

    IN p_header_info VARCHAR(255),

    IN p_header_info2 VARCHAR(255),

    IN p_created_by VARCHAR(255),

    IN p_modified_by VARCHAR(255),

    IN p_details JSON

)

BEGIN

    DECLARE v_header_id INT;

    -- Check if the header exists

    IF p_id IS NULL THEN

        -- Insert new header

        INSERT INTO header (header_info, header_info2, created_by, modified_by)

        VALUES (p_header_info, p_header_info2, p_created_by, p_modified_by);

        SET v_header_id = LAST_INSERT_ID();

    ELSE

        -- Update existing header

        UPDATE header

        SET header_info = p_header_info,

            header_info2 = p_header_info2,

            modified_by = p_modified_by

        WHERE id = p_id;

        SET v_header_id = p_id;

    END IF;

    -- Clear existing details for the header

    DELETE FROM detail WHERE header_id = v_header_id;

    -- Insert new details

    SET @json = p_details;

    INSERT INTO detail (header_id, detail_info, detail_info2, detail_info3, detail_info4)

    SELECT v_header_id, detail_info, detail_info2, detail_info3, detail_info4

    FROM JSON_TABLE(@json, '$[*]' 

        COLUMNS (

            detail_info VARCHAR(255) PATH '$.detail_info',

            detail_info2 VARCHAR(255) PATH '$.detail_info2',

            detail_info3 VARCHAR(255) PATH '$.detail_info3',

            detail_info4 VARCHAR(255) PATH '$.detail_info4'

        )

    ) AS details;

END //

DELIMITER ;

```

### Step 3: Example of Calling the Stored Procedure

You can call the stored procedure using the following SQL command:

```sql

CALL SaveOrUpdateHeaderAndDetails(

    NULL, -- For new header, use NULL

    'Header Info 1',

    'Header Info 2',

    'User 1',

    'User 1',

    '[{"detail_info": "Detail 1", "detail_info2": "Detail 2", "detail_info3": "Detail 3", "detail_info4": "Detail 4"}, {"detail_info": "Detail 5", "detail_info2": "Detail 6", "detail_info3": "Detail 7", "detail_info4": "Detail 8"}]'

);

```

### Step 4: PHP Example Using PDO

Certainly! Below is a complete PHP example that demonstrates how to use PDO to call the stored procedure we created earlier. This example includes error handling and assumes you have already set up your MySQL database and tables as described.

### PHP Example to Call the Stored Procedure

```php

<?php

// Database connection parameters

$host = 'localhost'; // Change if necessary

$db = 'your_database'; // Replace with your database name

$user = 'your_username'; // Replace with your database username

$pass = 'your_password'; // Replace with your database password

$charset = 'utf8mb4';

// Set up the Data Source Name (DSN)

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";

$options = [

    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,

    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,

    PDO::ATTR_EMULATE_PREPARES   => false,

];

try {

    // Create a new PDO instance

    $pdo = new PDO($dsn, $user, $pass, $options);

    // Prepare the JSON data for details

    $details = json_encode([

        ['detail_info' => 'Detail 1', 'detail_info2' => 'Detail 2', 'detail_info3' => 'Detail 3', 'detail_info4' => 'Detail 4'],

        ['detail_info' => 'Detail 5', 'detail_info2' => 'Detail 6', 'detail_info3' => 'Detail 7', 'detail_info4' => 'Detail 8']

    ]);

    // Prepare the stored procedure call

    $stmt = $pdo->prepare("CALL SaveOrUpdateHeaderAndDetails(?, ?, ?, ?, ?, ?)");

    // Bind parameters

    $headerId = null; // Use null for a new header, or provide an existing ID to update

    $headerInfo = 'Header Info 1';

    $headerInfo2 = 'Header Info 2';

    $createdBy = 'User 1';

    $modifiedBy = 'User 1';

    // Execute the stored procedure

    $stmt->execute([$headerId, $headerInfo, $headerInfo2, $createdBy, $modifiedBy, $details]);

    echo "Header and details saved/updated successfully.";

} catch (PDOException $e) {

    // Handle any errors

    echo "Error: " . $e->getMessage();

}

?>

```

### Explanation of the Code

1. **Database Connection**: The script starts by defining the database connection parameters and creating a new PDO instance to connect to the MySQL database.

2. **JSON Data Preparation**: The details for the detail table are prepared as a JSON string. This is necessary because the stored procedure expects a JSON input for the details.

3. **Stored Procedure Call**: The script prepares a statement to call the stored procedure `SaveOrUpdateHeaderAndDetails`. It binds the parameters, including the header ID (which is `null` for a new header), header information, created and modified by fields, and the JSON string for details.

4. **Execution**: The stored procedure is executed with the provided parameters. If successful, a success message is displayed.

5. **Error Handling**: If there is an error during the execution, it is caught and displayed.

### Usage

- Make sure to replace the database connection parameters (`$host`, `$db`, `$user`, `$pass`) with your actual database credentials.

- You can change the `$headerId` variable to an existing header ID if you want to update an existing record instead of creating a new one.

- Save this script as a `.php` file and run it on a server with PHP and MySQL support.

This example should give you a good starting point for using stored procedures with PDO in PHP.

link to gist 

https://gist.github.com/ifirdausku/503dec090b2edf54b859f61890ac3d34

https://gist.github.com/ifirdausku/964fc9c105e382496fc570e6bae77785

blackbox ai with my prompt, (try it first)

 - Limit ... Offset...

- Group_Concat

- Self Join (https://www.educba.com/mysql-self-join/)

- 

 Untuk mengambil data id yang terakhir di insertkan bisa memakai fungsi berikut

LAST_INSERT_ID()

LAST_INSERT_ID() gives you the last autogenerated id on the connection you execute it on, it does not return the last insert id globally produced by the MySQL server.

sauce : https://stackoverflow.com/questions/5835677/last-insert-id-how-it-works-at-multi-users-environment

This is Generated by ChatGPT 2023-11-01

To insert data into a single-row header table and multiple-row detail table using a stored procedure in MySQL, you can follow these steps. In this example, we'll create two tables: header and detail, with a one-to-many relationship, and then create a stored procedure to insert data into both tables.

Assuming you have the following tables:

CREATE TABLE header (

    header_id INT AUTO_INCREMENT PRIMARY KEY,

    header_data VARCHAR(255)

);

CREATE TABLE detail (

    detail_id INT AUTO_INCREMENT PRIMARY KEY,

    header_id INT,

    detail_data VARCHAR(255),

    FOREIGN KEY (header_id) REFERENCES header(header_id)

);

Here's a stored procedure to insert data into both tables:

DELIMITER $$

CREATE PROCEDURE InsertHeaderAndDetails(

    IN headerData VARCHAR(255),

    IN detailDataArray JSON

)

BEGIN

    DECLARE done INT DEFAULT FALSE;

    DECLARE detailValue VARCHAR(255);

    DECLARE cur CURSOR FOR SELECT value FROM JSON_TABLE(detailDataArray, '$[*]' COLUMNS(value VARCHAR(255) PATH '$'));

    

    -- Insert data into the header table

    INSERT INTO header (header_data) VALUES (headerData);

    SET @lastHeaderID = LAST_INSERT_ID(); -- Get the last inserted header ID

    -- Insert data into the detail table

    OPEN cur;

    read_loop: LOOP

        FETCH cur INTO detailValue;

        IF done THEN

            LEAVE read_loop;

        END IF;

        INSERT INTO detail (header_id, detail_data) VALUES (@lastHeaderID, detailValue);

    END LOOP;

    CLOSE cur;

END $$

DELIMITER ;

 

This stored procedure takes two parameters:

headerData: The data to be inserted into the header table.

detailDataArray: A JSON array containing the data to be inserted into the detail table.

You can call the stored procedure like this:

CALL InsertHeaderAndDetails('Header Data', '["Detail 1", "Detail 2", "Detail 3"]');

 

 

 

 

Untuk demo bisa dilihat di https://www.db-fiddle.com/f/pPnVb5qYKDKK3jYmjJUbJT/0

 

MySQL Datediff

Berikut adalah cara menghitung antara 2 tanggal(datetime) menggunakan datediff, anda hanya tinggal perlu copas script dibawah ini dan langsung run di MySQL, hasilnya seperti diatas.

Jika ingin menambahkan fungsi bulan hanya tinggal menambahkan rumus modifikasi yang diambil dari days diatas

 

QR Code

Kali ini saya akan share tutorial Create dan Scan QR Code menggunakan PHP dan langsung terhubung dengan database MySQL, saya menggunakan library dari github, berikut adalah library yang saya pakai

Untuk membuat QR Code saya menggunakan https://github.com/t0k4rt/phpqrcode (PHP)

Untuk scan QR Code saya menggunakan https://github.com/ifirdausku/webcodecamjs (Javascript)

Contoh project yang menggunakan keduanya (Source code create dan scan ijasah)

https://github.com/ifirdausku/scan-create-qr-code-php

Silahkan download dulu jika anda ingin mengikuti tutorial ini, jika ingin langsung download source codenya saya sediakan dibawah

Saya asumsikan anda sudah paham HTML, CSS, basic PHP dan Javascript, karena saya menggunakan bootstrap, jquery dalam tutorial ini.

Aplikasi ini HANYA DAPAT BERJALAN PADA HTTPS, jadi jika anda menjalankannya tidak menggunakan HTTPS pada chrome maka akan gagal karena kamera tidak bisa diakses.

Setelah anda download 2 library diatas, jadikan dalam 1 folder (disini saya membuat folder bernama qr) , lalu masukkan dalam htdocs

Jadikan dalam 1 folder

Setelah itu copy file di bawah ini, lalu simpan dengan nama index.php

Cukup menggunakan

current_timestamp()

pada default di MySQL

Ada banyak cara untuk melakukan select Datetime Column sebagai Date dengan mysql, saya akan membagikan beberapa langkah langkahnya

SELECT * FROM tabel1 WHERE DATE(datetime_field) = '05-11-2018'

datetime_field adalah nama kolom dalam tabel tersebut
format datetime_field bisa menyesuaikan sesuai inputan tanggalnya, bisa DD-MM-YYYY atau YYYY-MM-DD atau bisa dirubah sesuai format tertentu

Cara ini merupakan cara yang paling sederhana namun cara ini memiliki execution time yang lumayan lebih lama daripada cara yang lainnya, jika untuk project sederhana cara ini tidak berpengaruh untuk digunakan, namun jika untuk project yang besar apalagi menggunakan big data maka pengaruhnya akan sangat terlihat signifikan.


Untuk memilih tanggal mundur kebelakang anda bisa menggunakan DATE_SUB atau menambah DATE_ADD

Getting one month ago is easy with a single MySQL function:

SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);

or

SELECT NOW() - INTERVAL 1 MONTH;

Off the top of my head, I can't think of an elegant way to get the first day of last month in MySQL, but this will certainly work:

SELECT CONCAT(LEFT(NOW() - INTERVAL 1 MONTH,7),'-01');

Put them together and you get a query that solves your problem:

SELECT *
FROM your_table
WHERE t >= CONCAT(LEFT(NOW() - INTERVAL 1 MONTH,7),'-01')
AND t <= NOW() - INTERVAL 1 MONTH

Cara diatas bisa di gunakan untuk memilih sejak tanggal 1 di awal bulan

Ada banyak cara untuk melakukan sort order dengan mysql, saya akan membagikan beberapa langkah langkahnya

SELECT * FROM tabel1 ORDER BY CASE WHEN kolom = 'URUTANke1' THEN 1 WHEN kolom = 'URUTANke2' THEN 2 WHEN kolom = 'URUTANke3' THEN 3 WHEN kolom = 'URUTANke4' THEN 4 ELSE 5 END ASC

SELECT * FROM tabel1 ORDER BY FIND_IN_SET(namakolom,'URUTANke1,URUTANke2,URUTANke3,URUTANke4,URUTANke5,URUTANke6')

SELECT * FROM tabel1 ORDER BY CONCAT(namakolom)

buat fungsi bernama enrkiptorfir() dan dekriptorfir()

lalu fungsi ini dibuatkan dll library seperti di windows, source code dllnya ini harus di encrypt dan hanya bisa di buka sendiri oleh yang buat

contoh isi dllnya adalah di bawah ini

variabel inputannya ini akan di kunci menggunakan encoding seperti base64 (utamanya mau pakai Ascii85) (atau yang lainnya bisa pakai XXE, UUE, binhex, Base32, Base85, Ascii85) dan di gabung jadi tidak hanya 1 fungsi

fungsi enrkiptorfir() hanya encryptor biasa tidak diberikan tanggal expired 

contoh : base64encode ( ' teksyangakandiencryptdisini ' ) maka hasilnya adalah dGVrc3lhbmdha2FuZGllbmNyeXB0ZGlzaW5p

untuk dekriptorfir() ini di buatkan decodingnya seperti fungsi encryptornya hanya saja diberikan batas expired, logika programnya seperti ini

contoh : 

if date < '2025-07-07' then

base64decode ( ' dGVrc3lhbmdha2FuZGllbmNyeXB0ZGlzaW5p ' ) 

maka hasilnya adalah teksyangakandiencryptdisini 

else 

acaklokasihuruf ( ' dGVrc3lhbmdha2FuZGllbmNyeXB0ZGlzaW5p ' ) maka hasilnya 

maka hasilnya adalah dGVrc3lhbmdha2FuZGllbmNyeXB0ZGlzaW5p di pindah pindah saja lokasinya tapi tidak mendecrypt nilai yang diberikan

_____________________________________________________________________________

cara lain adalah dengan metode encrypt hurufnya dipindah posisi sebanyak 2x akan tetapi decryptnya hanya 1x, maka harus memakai decrypt berulang kali 

contoh encryptfir( 'A' ) maka hasil yang tampil bisa jadi 'C'

tapi decryptfir( 'C' ) maka hasil yang tampil bisa jadi 'B', jika ingin mendapatkan hasil A maka lakukan decryptfir( 'C' ) lagi

akan tetapi di barengi dengan validasi tanggal juga, jika tanggal sudah melewati tertentu maka fungsi tidak berfungsi sebagaimana mestinya

____________________________________________________________________________

fungsi ini harus dipakai di tempat yang vital seperti pada saat insert data, dan pada saat update data, atau bisa juga saat load data saja dan yang di kunci hanya value2 tertentu saja seperti tanggal dll

-------------------------------------------

bisa juga dengan menggunakan aes encryption di database 

CREATE FUNCTION `encrypt_credit_card`(credit_card_number VARCHAR(255), encryption_key VARCHAR(255))

RETURNS BLOB

DETERMINISTIC

BEGIN

  RETURN AES_ENCRYPT(credit_card_number, encryption_key);

END;

SELECT AES_DECRYPT(encrypted_column, 'your_secret_key') AS decrypted_data FROM your_table;

fungsi untuk select dari kolom encryptnya, lalu untuk mengunci dan disimpan di database bisa memakai ini 

INSERT INTO users (username, encrypted_credit_card) VALUES ('John Doe', encrypt_credit_card('1234-5678-9012-3456', 'your_secret_key'));

your_secret_key = ini dijadikan text agar load di dll, jika salah maka hasil encryptnya juga akan salah

 

di dll gunakan fungsi ini 

if date < tanggal tertentu 2050-01-01 

your_secret_key yang benar

else

your_secret_key yang salah

end

jika seperti ini maka secret keynya akan benar di tanggal tertentu dan akan salah jika tanggal sudah melewati batas

this is writed at 2025-07-30