1
\$\begingroup\$

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

\$\endgroup\$
0
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.