# Insert and update SQL entries for posts to be accessed by up to 3 other users

At first, I have this HTML I use here:

body {
text-align: center;
}
<form method='post' action=''>
<input type='text' placeholder='post' name='post'>
<div>
<input type='text' placeholder='ref1' name='ref1'>
<input type='text' placeholder='ref1id' name='ref1id'>
</div>
<div>
<input type='text' placeholder='ref2' name='ref2'>
<input type='text' placeholder='ref2id' name='ref2id'>
</div>
<div>
<input type='text' placeholder='ref3' name='ref3'>
<input type='text' placeholder='ref3id' name='ref3id'>
</div>
<input type='submit' name='submit'>
</form>

I receive the data using PHP and send it like this:

<?php
if (isset($_POST['submit'])) {$post = $_POST['post'];$ref1 = $_POST['ref1'];$ref1id = $_POST['ref1id'];$ref2 = $_POST['ref2'];$ref2id = $_POST['ref2id'];$ref3 = $_POST['ref3'];$ref3id = $_POST['ref3id'];$stmt = $conn->prepare($Query)
$stmt->execute([ 'post' =>$post,
'ref1' => $ref1, 'ref1id' =>$ref1id,
'ref2' => $ref2, 'ref2id' =>$ref2id,
'ref3' => $ref3, 'ref3id' =>$ref3id
])

}
?>


Then I apply this SQL Query to INSERT the data:

INSERT INTO posts (post, ref1, ref1id, ref2, ref2id, ref3, ref3id) VALUES (:post, :ref1, :ref1id, :ref2, :ref2id, :ref3, :ref3id)


And this one to UPDATE the data:

UPDATE posts SET post = :post, ref1 = :ref1, ref1id = :ref1id, ref2 = :ref2, ref2id = :ref2id, ref3 = :ref3, ref3id = :ref3id


in/of this MYSQL Table: http://sqlfiddle.com/#!9/7d22d8/1/0

The idea is that the user can make a private post for 3 people only, He writes their name in ref[x] and their passcode which is ref[x]id

Consider the following table:

CREATE TABLE IF NOT EXISTS posts (
id int(6) unsigned NOT NULL,
post varchar(24) NOT NULL,
PRIMARY KEY (id)
) DEFAULT CHARSET=utf8;


We've stripped out the ref columns. Now we add a new table:

CREATE TABLE IF NOT EXISTS post_refs (
posts_id int(6) unsigned NOT NULL,
ref varchar(24) NOT NULL,
ref_id varchar(24) NOT NULL,
PRIMARY KEY (posts_id, ref)
) DEFAULT CHARSET=utf8;


It's possible that the index should contain ref_id rather than ref. You don't really provide enough context to tell.

This is basic normalization. Database tables should not contain columns of duplicate information (e.g. numbered columns). Such columns should be moved into their own table so that you don't have to modify the database structure every time you change the business rule.

To make this work, you need at least two SQL queries, one for each table. You have to get the posts_id after the first one. For updates, you would do so with a SELECT (possibly embedded in the query). For inserts, you could do \$conn->lastInsertId() after inserting the post. For updates, you might want to delete from the post_refs table and then do an insert. E.g. something like

SELECT id AS posts_id FROM posts WHERE post = :post


If no rows, do

INSERT INTO posts (post) VALUES (:post)


If there are rows, do

DELETE FROM post_refs WHERE posts_id = :posts_id


Either way, then do

INSERT INTO post_refs (posts_id, ref, ref_id) VALUES (:posts_id, :ref, :ref_id)


or the multi-row equivalent.

That logic becomes simpler if you know beforehand whether you need to do an update or an insert. Your question implies that you do, but it doesn't show how.

Also consider

CREATE TABLE IF NOT EXISTS post_read_users (
posts_id int(6) unsigned NOT NULL,
users_id int(6) unsigned NOT NULL,
PRIMARY KEY (posts_id, users_id)
) DEFAULT CHARSET=utf8;


Because it's not normally necessary to describe the people that you want to give access. You usually just select from a list, connecting existing users to the post. You seem to be creating the users as you go. So either the person creating has to remember to give the same information each time, or the person reading has to remember multiple passcodes. Either would be an uncommon and overly difficult implementation.