Adding data to the database

Good afternoon!

I have a question. There are 3 tables in the database: user, itemsand useritems. I need to make a query to the usertable and find out if there is such a user in the database, if there is, then we display his and all the items that he has, and if the user does not exist with this , then we simply display a message that the user has been added to the database and automatically add an item to him.

In the table useritemsI have 3 fields id, uid, and itmidand now I can’t figure it out , how to add a user there and assign any item from the itemstable to him. Since when I click on the button, only the user is added to the table user.

INSERT INTO `user` (`uname`) VALUES ('$nambreUsuario');

I have no idea how to add an item and a user to the useritemstable.
And at the same time, it is necessary that both in the usertable and in the useritemstable, the user has the same id.


Answer 1, authority 100%

Before you can query a database, you need to properly design the database. In particular, establish relationships between tables. To do this, you need to use additional fields in the tables – foreign keys. In your case, as far as I understand, in the useritemstable, this is the uidfield. When creating it, after the list of fields with their description, add FOREIGN KEY(uid).

The easiest way, provided that you have everything built there correctly, is this:

  • The page contains text fields (user information)
  • dropdown list with items. Since there is a table for items, and the user only needs to select an item, this list is formed as a result of selecting all items from the item table. At the same time, it is very important to store the id of the item as well, for example, you can do this
    <option value="id_predmet">
  • Then after adding a user, you need to do the following:
    — Select the newly created entry from the user table. This must be done, because the useritems table should store not the user himself, but his id, but as far as I understand, you have it auto increment, i.e. generated automatically. You can use the following code to select
    SELECT uid FROM users WHERE last name=’the last name the user entered in the text field when adding the last person’ AND first name=’the first name entered in the text field’ etc.

  • Add a new entry to the useritems table with the received uid and the id_predmet selected in the list

Of course, this method is not the fastest, you can also use complex nested queries, but first you need to understand their syntax


Answer 2, authority 33%

Very interesting. You know how to add a record to the user table, but you don’t know how to add a record to the useritems table. Is this your sense of humor?=) If not, then the answer is: just like in the user table. If I were you, I would create 3 tables in the same way.

SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @[email protected]@SQL_MODE, SQL_MODE='TRADITIONAL';
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb`;
CREATE TABLE IF NOT EXISTS `mydb`.`user` (
 `iduser` INT(11) NOT NULL AUTO_INCREMENT ,
 `name` VARCHAR(45) NOT NULL ,
 PRIMARY KEY (`iduser`) ,
 UNIQUE INDEX `ix_user_name` () )
ENGINE=InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;
CREATE TABLE IF NOT EXISTS `mydb`.`item` (
 `iditem` INT(11) NOT NULL AUTO_INCREMENT ,
 `name` VARCHAR(45) NOT NULL ,
   PRIMARY KEY (`iditem`) )
ENGINE=InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;
CREATE TABLE IF NOT EXISTS `mydb`.`user_has_item` (