Beiträge von cottton

tbl user -- successfully registered users
id                 int     primary key auto increment
email            string     unique email
password_hash    string     the hashed password
nickname        string     unique nickname
is_active        bool     indicates if th user is active
created_at        ...
updated_at         ...
-- uidx email; uidx nickname

-- note: would use tbl user_login to store logins (id, user_id, password_hash, is_active, created_at, updated_at)
-- note: missing user email confirmation at all

user_invitation -- users invite new users (1:n)
id                     int             primary key auto increment
user_id                int             fk (foreign key) user.id
invitation_token    string             the unique invitation token|hash
is_active            bool             indicates if th user-invitation is active
valid_until            datetime        the datetime string until the invitation is valid
redeemed_at            null|datetime    the datetime string the invitation has been successfully accepted
created_at            ...
updated_at             ...
-- uidx invitation_token


-----

INPUT
data = // POST data of new user to create 
invitationToken = // POST data of invitation token provided

-----

// CHECK INPUT

if !strlen(invitationToken)                            throw invalid invitation exception --invalid token

// todo: check new user data ... like email validation, already exists by nick and email, ....

-----

CHECK INVITATION

invitation = ... SELECT * FROM user_invitation WHERE invitation_token = :invitation_token LIMIT 1; 
// limit 1 to load the 1st always in case of duplicates (duplicates gets ignored that way)

// check if invitation actually exists
if !is_array(invitation) or !invitation.id             throw invalid invitation exception --not exist

// check if invitation is active
if !(bool)invitation.is_active                        throw invalid invitation exception --disabled

// check if invitation is still valid
if invitation.valid_until < NOW                     throw invalid invitation exception --expired

// check if invitation has been already redeemed
if strlen(invitation.redeemed)                        throw invalid invitation exception --already redeemed

---

hostUserId = invitation.user_id

---

CHECK HOST

// check if user who invited the new user is valid
host = ... SELECT * FROM user WHERE user_id = :host_user_id LIMIT 1; 
//limit 1 to load the 1st always in case of duplicates (duplicates gets ignored that way)

// check if host actually exists
if !is_array(host) or !host.id                         throw invalid invitation exception --host not exist

// check if host is active
if !(bool)host.is_active                            throw invalid invitation exception --host disabled

// todo: check if host (user) is allowed to invite new users

---

REGISTER NEW USER

try 
    // insert new user
    INSERT INTO ... 
    
    // set redeemed_at datetime to now
    UPDATE user_invitation SET redeemed_at = NOW WHERE invitation_token = :invitation_token; 
    // no limit 1 - on dublicates all get redeemed on purpose

    // todo: would start new user email confirmation process here

    return success

catch 
    error handling ...
    return failure

tbl model
id    model_id    model_name    show_id    collection    datum
--warum ist id nicht die model_id? 
--was ist eine show_id? Vom Bsp aus "001" - sagt mir nix.

tbl gallerie
id    m_show_id    gallerie
--würde namen ausschreiben - statt m_show_id model_show_id (wobei ich erwarten würde, dass model_show_id auf tbl model column id (model.id) zeigt)
--was ist (in) gallerie?

tbl bilder
id    m_show_id    bilder (order bild?)
--siehe m_show_id hinweis bei gallerie

CREATE TABLE `user` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(18) NOT NULL
);
CREATE TABLE `beitrag` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NOT NULL,
  `text` VARCHAR(18) NOT NULL
);
CREATE TABLE `beitrag_pictures` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `beitrag_id` INT UNSIGNED NOT NULL,
  `image` VARCHAR(255) NOT NULL
);
-- not adding idx or fk here

INSERT INTO `user` SET `username` = 'cottton';
INSERT INTO `beitrag` SET `user_id` = 1, `text` = 'text 1';
INSERT INTO `beitrag` SET `user_id` = 1, `text` = 'text 2';
INSERT INTO `beitrag` SET `user_id` = 1, `text` = 'text 3';
INSERT INTO `beitrag_pictures` SET `beitrag_id` = 2, `image` = '1.png';
INSERT INTO `beitrag_pictures` SET `beitrag_id` = 2, `image` = '2.png';
INSERT INTO `beitrag_pictures` SET `beitrag_id` = 2, `image` = '3.png';

select * from user;
+----+----------+
| id | username |
+----+----------+
|  1 | cottton  |
+----+----------+
select * from beitrag;
+----+---------+--------+
| id | user_id | text   |
+----+---------+--------+
|  1 |       1 | text 1 |
|  2 |       1 | text 2 |
|  3 |       1 | text 3 |
+----+---------+--------+
select * from beitrag_pictures;
+----+------------+-------+
| id | beitrag_id | image |
+----+------------+-------+
|  1 |          2 | 1.png |
|  2 |          2 | 2.png |
|  3 |          2 | 3.png |
+----+------------+-------+

SELECT
    `b`.`id`,
    `b`.`user_id`,
    `b`.`text`
FROM `beitrag` AS `b`
WHERE `b`.`user_id` = 1;
+----+---------+--------+
| id | user_id | text   |
+----+---------+--------+
|  1 |       1 | text 1 |
|  2 |       1 | text 2 |
|  3 |       1 | text 3 |
+----+---------+--------+

SELECT
    `b`.`id`,
    `b`.`user_id`,
    `b`.`text`,
    `p`.`image`
FROM `beitrag` AS `b`
LEFT JOIN `beitrag_pictures` AS `p` ON `p`.`beitrag_id` = `b`.`id`
WHERE `b`.`user_id` = 1;
+----+---------+--------+-------+
| id | user_id | text   | image |
+----+---------+--------+-------+
|  2 |       1 | text 2 | 1.png | <-
|  2 |       1 | text 2 | 2.png | <-
|  2 |       1 | text 2 | 3.png | <-
|  1 |       1 | text 1 | NULL  |
|  3 |       1 | text 3 | NULL  |
+----+---------+--------+-------+

SELECT
    `b`.`id`,
    `b`.`user_id`,
    `b`.`text`,
    GROUP_CONCAT(`p`.`image` SEPARATOR ',') AS `image_string`
FROM `beitrag` AS `b`
LEFT JOIN `beitrag_pictures` AS `p` ON `p`.`beitrag_id` = `b`.`id`
WHERE `b`.`user_id` = 1;
+----+---------+--------+-------------------+
| id | user_id | text   | image_string      |
+----+---------+--------+-------------------+
|  2 |       1 | text 2 | 1.png,2.png,3.png | oO?
+----+---------+--------+-------------------+

SELECT
    `b`.`id`,
    `b`.`user_id`,
    `b`.`text`,
    GROUP_CONCAT(`p`.`image` SEPARATOR ',') AS `image_string`
FROM `beitrag` AS `b`
LEFT JOIN `beitrag_pictures` AS `p` ON `p`.`beitrag_id` = `b`.`id`
WHERE `b`.`user_id` = 1
GROUP BY `b`.`id`;
+----+---------+--------+-------------------+
| id | user_id | text   | image_string      |
+----+---------+--------+-------------------+
|  1 |       1 | text 1 | NULL              |
|  2 |       1 | text 2 | 1.png,2.png,3.png |
|  3 |       1 | text 3 | NULL              |
+----+---------+--------+-------------------+

SELECT
    `b`.`id`,
    `b`.`user_id`,
    `b`.`text`,
    GROUP_CONCAT(`p`.`image` SEPARATOR ',') AS `image_string`,
    `u`.`username`
FROM `beitrag` AS `b`
LEFT JOIN `user` AS `u` ON `u`.`id` = `b`.`user_id`
LEFT JOIN `beitrag_pictures` AS `p` ON `p`.`beitrag_id` = `b`.`id`
WHERE `b`.`user_id` = 1
GROUP BY `b`.`id`;
+----+---------+--------+-------------------+----------+
| id | user_id | text   | image_string      | username |
+----+---------+--------+-------------------+----------+
|  1 |       1 | text 1 | NULL              | cottton  |
|  2 |       1 | text 2 | 1.png,2.png,3.png | cottton  |
|  3 |       1 | text 3 | NULL              | cottton  |
+----+---------+--------+-------------------+----------+

$data = [
    ['country' => 'DE', 'city' => 'Berlin'],
    ['country' => 'DE', 'city' => 'Dresden'],
    ['country' => 'DE', 'city' => 'München'],
];
$params = [];
$sql = "INSERT INTO `table_name` (`country`, `city`) VALUES ";
$values = [];
foreach ($data as $array) {
    $params[] = $array['country']; 
    $params[] = $array['city']; 
    $values[] = "(?, ?)";
}
$sql .= implode(', ', $values);
$sql .= ";";
var_dump($sql); 
//$stmt = $pdo->prepare($sql);
//$stmt->execute($params);

// simulate POST input
$_POST['city'] = ['Berlin', 'Dresden', 'München'];
$params = [];
$sql = "INSERT INTO `city` (`city`) VALUES ";
$values = [];
foreach ($_POST['city'] as $city) {
    $params[] = $city; 
    $values[] = "(?)";
}
$sql .= implode(', ', $values);
$sql .= ";";
var_dump($sql); 
//$stmt = $pdo->prepare($sql);
//$stmt->execute($params);

// simulate POST input
$_POST['city'] = ['Berlin', 'Dresden', 'München'];

$params = [];
$sql = "INSERT INTO `city` (`city`) VALUES";

foreach ($_POST['city'] as $city) {
    $params[] = $city; 
    $sql .= " (?)";
}
$sql .= ";";

var_dump($sql); 
//$stmt = $pdo->prepare($sql);
//$stmt->execute($params);

<?php

$fileContentArray = [
    '<!DOCTYPE html>
    <html>
    <head>
    <title>Page Title</title>
    </head>
    <body>
    
    <h1>This is a Heading</h1>
    <p>This is a paragraph.</p>
    
    </body>
    </html>',    
];
$contentToAdd = '
    <script>
        var i = 1;
    </script>
';
foreach ($fileContentArray as &$content) {
    $content = str_replace('<head>', "<head>\r\n{$contentToAdd}", $content);
}

echo var_export($fileContentArray, true) . PHP_EOL;

array (
  0 => '<!DOCTYPE html>
    <html>
    <head>

    <script>
        var i = 1;
    </script>

    <title>Page Title</title>
    </head>
    <body>
    
    <h1>This is a Heading</h1>
    <p>This is a paragraph.</p>
    
    </body>
    </html>',
)