Hier een uitgebreid doch versimpeld voorbeeld van INSERT ON DUPLICATE UPDATE.
De idee is om een uniek accountnummer aan te maken en te reserveren alvorens de verdere gegevens in te voeren en te bewaren.
Als een leeg record niet wordt gebruikt omdat de invoer is afgebroken, kan je het gereserveerde accountnummer zelf gebruiken en daar je eigen gegevens aan toevoegen.Weliswaar is dat een omweg, maar toch.
De nadruk ligt op het aanmaken van het unieke accountnummer mbv MySQL in spCreateAccount().
Wat het doet :
Een leeg record met uniek accountnummer wordt aangemaakt : spCreateAccount && sfMakeAccountNumber
Met een form worden andere gegevens ingevoerd en bewaard met spUserInsert
Procedure spGetDiagnostics doet voor als een uniek record al bestaat.
Andere procedures laten de lege records zien en wissen die.
Maak eerst een lege database aan ( upsert , wordt in connect.php gebruikt ) en voer dan onderstaand SQL bestand uit dat een tabel en de procedures en functie toevoegt.
Aan het eind worden die uitgevoerd.
Fouten voorbehouden.
Zelf ideeën toevoegen en/of uitwerken.
-- Adminer 4.8.1 MySQL 10.6.11-MariaDB-0ubuntu0.22.04.1 dump
SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
SET NAMES utf8mb4;
DELIMITER ;;
DROP FUNCTION IF EXISTS `sfMakeAccountNumber`;;
CREATE FUNCTION `sfMakeAccountNumber`() RETURNS char(15) CHARSET utf8mb4 COLLATE utf8mb4_general_ci
BEGIN
SET @modulo = 26;
SET @nu = NOW();
SET @jaar = DATE_FORMAT( @nu, '%y' ) - 20;
SET @maand = MONTH(@nu);
SET @dag = MOD( DAYOFMONTH( @nu ), @modulo );
IF @dag = 0 THEN
SET @dag = 1;
END IF;
#SET @wofm = FLOOR( ( DAYOFMONTH( @nu ) - 1) / 7) + 1;
SET @uur = HOUR( @nu );
IF @uur = 0 THEN
SET @uur = 1;
END IF;
SET @minuut = MOD( MINUTE( @nu ) , @modulo );
IF @minuut = 0 THEN
SET @minuut = 1;
END IF;
SET @seconde = MOD( SECOND( @nu ) , @modulo );
IF @seconde = 0 THEN
SET @seconde = 26;
END IF;
SET @micro = MICROSECOND( SYSDATE(4) );
IF @micro = '' THEN
SET @micro = '9999';
END IF;
SET @micro = LPAD( @micro, 4, 0 );
SET @base = 64;
SET @cjaar = CHAR( @base + @jaar );
SET @cmaand = CHAR( @base + @maand );
SET @cdag = CHAR( @base + @dag );
#SET @cwofm = CHAR( @base + @wofm );
SET @cuur = CHAR( @base + @uur );
SET @cminuut = CHAR( @base + @minuut );
SET @cseconde = CHAR( @base + @seconde );
SET @account = CONCAT ( @cjaar, @cmaand, @cdag, @cuur, @cminuut, @cseconde, @micro, 'Z' );
RETURN @account;
END;;
DROP PROCEDURE IF EXISTS `spCheckEmptyAccounts`;;
CREATE PROCEDURE `spCheckEmptyAccounts`()
SELECT *
FROM users
WHERE naam IS NULL
ORDER BY registratiedatum;;
DROP PROCEDURE IF EXISTS `spCreateAccount`;;
CREATE PROCEDURE `spCreateAccount`()
BEGIN
SET @errno = 0;
REPEAT
SET @account = sfMakeAccountNumber();
INSERT IGNORE INTO users (account, registratiedatum, naam, adres, woonplaats) VALUES ( @account, NOW(), NULL, NULL, NULL);
GET DIAGNOSTICS @num_conditions = NUMBER;
SET @nummer = @num_conditions;
GET DIAGNOSTICS CONDITION 1 @errno = MYSQL_ERRNO;
UNTIL @errno = 0 END REPEAT;
SELECT @account AS account;
END;;
DROP PROCEDURE IF EXISTS `spDeleteEmptyAccounts`;;
CREATE PROCEDURE `spDeleteEmptyAccounts`()
DELETE FROM users WHERE naam IS NULL;;
DROP PROCEDURE IF EXISTS `spGetDiagnostics`;;
CREATE PROCEDURE `spGetDiagnostics`()
BEGIN
INSERT IGNORE INTO users (account, registratiedatum, naam, adres, woonplaats) VALUES ( 'BBFAUHJ28851K', NOW(), NULL, NULL, NULL);
GET DIAGNOSTICS @num_conditions = NUMBER;
SET @nummer = @num_conditions;
GET DIAGNOSTICS CONDITION 1 @errno = MYSQL_ERRNO;
If @errno = 1062 THEN
SELECT @errno AS fout;
ELSE
SELECT @nummer;
END IF;
END;;
DROP PROCEDURE IF EXISTS `spUserInsert`;;
CREATE PROCEDURE `spUserInsert`(IN `faccount` char(15), IN `fnaam` char(50), IN `fadres` char(50), IN `fwoonplaats` char(50))
BEGIN
INSERT INTO
users ( account, registratiedatum, naam, adres, woonplaats )
VALUES ( faccount, now(), fnaam, fadres, fwoonplaats )
ON DUPLICATE KEY UPDATE
naam = values(naam),
adres = values(adres),
woonplaats = values(woonplaats);
END;;
DELIMITER ;
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`account` char(15) NOT NULL,
`registratiedatum` char(20) NOT NULL,
`naam` char(50) DEFAULT NULL,
`adres` char(50) DEFAULT NULL,
`woonplaats` char(50) DEFAULT NULL,
PRIMARY KEY (`account`),
UNIQUE KEY `id` (`id`),
KEY `naam` (`naam`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- 2022-12-27 09:48:04
# demo how it works
TRUNCATE TABLE users;
CALL spGetDiagnostics();
CALL spCheckEmptyAccounts();
CALL spGetDiagnostics();
CALL spCreateAccount();
CALL spCheckEmptyAccounts();
SELECT @account;
SET @naam = 'Fred';
SET @adres = 'Plein 54';
SET @woonplaats = 'Ergens';
CALL spUserInsert(@account, @naam, @adres, @woonplaats);
SELECT * FROM users WHERE account = @account;
SELECT * FROM users;
CALL spDeleteEmptyAccounts();
SELECT * FROM users;
Dit zijn de PHP bestanden.
connect.php om te verbinden
<?php
$SQL_host = 'localhost';
$SQL_user = 'root';
$SQL_password = 'password';
$SQL_port = '3306';
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$db = new MySQLi( $SQL_host, $SQL_user, $SQL_password, '', $SQL_port );
$db->set_charset("utf8mb4");
$db->query("SET NAMES utf8mb4 COLLATE utf8mb4_general_ci");
$dbase = 'upsert';
$db->select_db( $dbase );
?>
menu.php
<style>
#menu {
font-family: Copperplate;
font-size: 1.200em;
}
#menu a {
text-decoration: none;
}
</style>
<p id="menu">
<a href="UserInput.php">Input</a> |
<a href="UserUpdate.php">Update</a> |
<a href="UserShowAll.php">All</a>
</p>
<hr />
index.php om te beginnen
<?php
include('menu.php');
?>
<h3>Upsert Demo</h3>
UserInput.php om in te voeren
<?php
include('menu.php');
include ('connect.php'); // = new mysqli();
$query = "CALL spCreateAccount();";
$result = $db->query( $query );
if ( $result->num_rows > 0 ):
$row = $result->fetch_object();
echo '<pre>' . print_r( $row, TRUE ) . '</pre>';
$account = $row->account;
endif;
?>
<style>
p, input {
font-size: 120%;
}
</style>
<form method="post" action="UserPost.php">
<p>Account <input type="text" name="account" value="<?php echo $account; ?>" readonly /></p>
<p>Naam <input type="text" name="naam" value="" required /></p>
<p>Adres <input type="text" name="adres" value="" required /></p>
<p>Woonplaats <input type="text" name="woonplaats" value="" required /></p>
<p><input type="submit" name="submit" value="submit" /></p>
</form>
UserPost.php om te verwerken en het resultaat te zien
<?php
include('menu.php');
echo '<pre>' . print_r( $_POST, TRUE ) . '</pre>';
if ( $_POST['submit'] ):
include('connect.php');// = new mysqli();
$faccount = $db->real_escape_string( trim( $_POST['account'] ) );
$fnaam = $db->real_escape_string( trim( $_POST['naam'] ) );
$fadres = $db->real_escape_string( trim( $_POST['adres'] ) );
$fwoonplaats = $db->real_escape_string( trim( $_POST['woonplaats'] ) );
if ( !empty($faccount) && !empty($fnaam) && !empty($fadres) && !empty($fwoonplaats) ):
$query = 'CALL spUserInsert("' . $faccount . '", "' . $fnaam . '", "' . $fadres . '", "' . $fwoonplaats . '")';
echo '<p>' . $query . '</p>';
$result = $db->query( $query );
$query = 'SELECT * FROM users WHERE account = "' . $faccount . '"';
$result = $db->query( $query );
$row = $result->fetch_object();
echo '<pre>' . print_r( $row , TRUE ) . '</pre>';
else:
echo '<p style="color:red;">Er ontbreken gegevens</p>';
endif;
endif;
?>
UserUpdate.php om te bewerken
<?php
include('menu.php');
?>
<style>
p, input {
font-size: 120%;
}
</style>
<?php
if ( isset( $_GET['account'] ) ):
include ('connect.php'); // = new mysqli();
$account = $_GET['account'];
$query = 'SELECT * FROM users WHERE account = "' . $account . '";';
echo '<p>' . $query . '</p>';
$result = $db->query( $query );
if ( $result->num_rows > 0 ):
$row = $result->fetch_object();
echo '<pre>' . print_r( $row, TRUE ) . '</pre>';
$account = $row->account;
$naam = $row->naam;
$adres = $row->adres;
$woonplaats = $row->woonplaats;
?>
<form method="post" action="UserPost.php">
<p>Account <input type="text" name="account" value="<?php echo $account; ?>" required /></p>
<p>Naam <input type="text" name="naam" value="<?php echo $naam; ?>" required /></p>
<p>Adres <input type="text" name="adres" value="<?php echo $adres; ?>" required /></p>
<p>Woonplaats <input type="text" name="woonplaats" value="<?php echo $woonplaats; ?>" required /></p>
<p><input type="submit" name="submit" value="submit" /></p>
</form>
<?php
else:
echo '<p>Account niet gevonden</p>';
endif;
else:
echo '<p>Accountnummer ontbreekt</p>';
endif;
?>
