Home>

There are 3 tables with a connection to many.

(Project) -> There are all projects.

Create Table `Project`
  `ID` Int (11) not null,
  `Name` Varchar (255) Not Null,
  `Description` Varchar (255) Not Null Default 'No description',
  `image` Text
  `Link` Varchar (255) not null
) ENGINE= Innodb Default Charset= UTF8;

(Tags) -> There are tags.

Create Table `Tags` (
  `ID` Int (11) not null,
  `Tag_Name` Varchar (255) Not Null
) ENGINE= Innodb Default Charset= UTF8;

(P_TAGS) -> There are a project ID and tag ID.

Create Table` P_TAGS` (
  `Project_id` Int (11) Not Null,
  `tag_id` Int (11) not null
) ENGINE= Innodb Default Charset= UTF8;
-
-Table indexes` Project`
-
Alter Table` Project`
  Add Primary Key (id`);
-
-Table indexes `P_TAGS`
-
Alter Table` P_TAGS`
  Add Primary Key (`Project_id`,` tag_id`),
  Add key `tag_id` (` tag_id`);
-
-Table Tags indexes`
-
Alter Table `Tags`
  Add Primary Key (id`);
-
-auto_increment for saved tables
-
-
-Auto_Increment for Table` Project`
-
Alter Table` Project`
  Modify` Id` Int (11) not null auto_increment, auto_increment= 75;
-
-Auto_Increment for Table `Tags`
-
Alter Table `Tags`
  Modify` Id` int (11) not null auto_increment, auto_increment= 12;
-
-restrictions on the external key of the saved tables
-
-
-Limitations of the external key of the Table `P_TAGS`
-
Alter Table` P_TAGS`
  Add Constraint` P_TAGS_IBFK_1` Foreign Key (`Project_id`) References` Project` (`id`),
  Add Constraint` P_TAGS_IBFK_2` Foreign Key (`Tag_id`) References` Tags` (`id`);
COMMIT;

You need to insert a new project with defined tags to the database .` For implementation, I tried to make such a scheme

After that, you need to compare the tag ID that we entered the tag ID that is in the database and already on the basis of these this to make the insert in the P_TAGS table.

separately the cycles work, how to connect them together I do not know.

If there is some easy way to do this, I will be glad to learn.

//Take Max Number. In the $ IDCOUNT [0] [0] is the number that will be the project ID
        $ STH= $ dbh->
Prepare ("SELECT MAX (ID) from Project");
        $ sth->
Execute ();
        $ IDCOUNT= $ STH->
fetchall (pdo :: fetch_num);
        //Take all tags (ID, NAME)
        $ STH= $ dbh->
Prepare ('SELECT * from Tags');
        $ sth->
Execute ();
        $ Tags= $ STH->
Fetchall (PDO :: Fetch_assoc);
        //Make the cycle, learn that we have 11 tags
        For ($ i= 0; $ i <
 Count ($ tags); $ i ++) {
        Echo $ Tags [$ i] ['id']. ' | '. $ tags [$ i] [' tag_name '].' <
BR >
';
        }
        //Make the cycle, learn that we fit 3 tags +
        $ proj_tags= $ _get ["tags"];
        $ proj_tags= explode (",", $ proj_tags);
        For ($ i= 0; $ i <
 Count ($ proj_tags); $ i ++) {
        $ proj_tags [$ i];
}

Welcome to this Club in Russian! Text information is better attached in the form of text: a) read easier; b) can be copied; B) The search is open. You can fix the text of the question by clicking the rule below the text of the question

aleksandr barakin2021-07-22 12:12:12

Thanks for the advice, but I spent 3 hours on code formatting and he simply did not let me send a message. Therefore, I had to do so, sorry for such design.

SniffRx2021-07-22 12:21:40

Let's figure it out in order. "You need to compare the ID tag that we entered" (c) you have not selected anywhere anywhere. You are in the GET parameter how do you transmit ID or tag names?

KordDEM2021-07-22 12:23:51

If it does not work nicely arrange, insert the code without registration. It will be given in a normal appearance. To edit, click Edit.

aleksandr barakin2021-07-22 12:29:29

@Korddem, I apologize, it was necessary to clarify at the very beginning. Values ​​are transmitted to $ _GET parameter: Site.ru /script.php? Tags= tag, tag, tag ...

SniffRx2021-07-22 12:38:02
  • Answer # 1

    In this code, three large errors, and very common, so it will be useful to consider them.

    • The first and most important, "Next ID" Never do not get in advance. This will lead to errors and damage. IDs always receive only after the string has been added to the database.
    • Second -data Never is not unloaded from the base, if you need to check only a few rows. It is necessary to make a request that only requests specific lines
    • Third -it is very desirable to add transaction to database data not tied to any project.

    respectively, and write a code that helps to avoid all these errors

    $ PDO-&GT;
    beginransaction ();
    //First add a project
    $ STMT= $ PDO-&GT;
    Prepare ("INSERT INTO PROJECT (FIELDS) VALUES (markers)");
    $ STMT-&GT;
    Execute ([data]);
    $ PID= $ PDO-&GT;
    Lastinsertid (); //received the ID of the new project
    //We are looking for ID tags
    $ proj_tags= explode (",", $ _get ["tags"]);
    $ in= STR_REPEAT ('?,', Count ($ proj_tags) -1). '?';
    $ SQL= "SELECT ID FROM TAGS WHERE TAG_NAME IN ($ IN)";
    $ STM= $ DB-&GT;
    Prepare ($ SQL);
    $ STM-&gt;
    Execute ($ proj_tags);
    $ Tag_ids= $ STM-&GT;
    Fetchall (PDO :: fetch_column); //We get ID tags in an array
    //add tags to the project
    $ STMT= $ PDO-&GT;
    Prepare ("INSERT INTO P_TAGS (PID, TID) VALUES (: PID,: TID)");
    $ STMT-&GT;
    bindparam (': pid', $ pid);
    $ STMT-&GT;
    bindparam (': tid', $ tid);
    Foreach ($ Tag_ids AS $ TID) {
        $ STMT-&GT;
    Execute ();
    }
    $ PDO-&GT;
    COMMIT ();
    

    BINDPARAM C TID Put in the cycle) And so it didn't really understand why you need a separate request and even in aboard)

    KordDEM2021-07-22 13:13:23

    Make in the cycle? What for? The request for the ID corrected, there was a typo. Now it will be clearer

    Ипатьев2021-07-22 13:14:18

    Duck $ Tid is the $ Tag_IDS element on a specific passage. He has a trite there is no value at the first execution. And in the future it is not reassigned

    KordDEM2021-07-22 13:16:27

    tag_name=: tname in the query and bindparam (': tid', $ tid); in parameters.

    KordDEM2021-07-22 13:18:13

    A, ugh, saw. Well, yes, without a prior request, you can also. Regarding BindParam Read how it works php.net/manual/en/pdostatement.bindparam.php

    Ипатьев2021-07-22 13:22:50
  • Answer # 2

    In this code, three large errors, and very common, so it will be useful to consider them.

    • The first and most important, "Next ID" Never do not get in advance. This will lead to errors and damage. IDs always receive only after the string has been added to the database.
    • Second -data Never is not unloaded from the base, if you need to check only a few rows. It is necessary to make a request that only requests specific lines
    • Third -it is very desirable to add transaction to database data not tied to any project.

    respectively, and write a code that helps to avoid all these errors

    $ PDO-&GT;
    beginransaction ();
    //First add a project
    $ STMT= $ PDO-&GT;
    Prepare ("INSERT INTO PROJECT (FIELDS) VALUES (markers)");
    $ STMT-&GT;
    Execute ([data]);
    $ PID= $ PDO-&GT;
    Lastinsertid (); //received the ID of the new project
    //We are looking for ID tags
    $ proj_tags= explode (",", $ _get ["tags"]);
    $ in= STR_REPEAT ('?,', Count ($ proj_tags) -1). '?';
    $ SQL= "SELECT ID FROM TAGS WHERE TAG_NAME IN ($ IN)";
    $ STM= $ DB-&GT;
    Prepare ($ SQL);
    $ STM-&gt;
    Execute ($ proj_tags);
    $ Tag_ids= $ STM-&GT;
    Fetchall (PDO :: fetch_column); //We get ID tags in an array
    //add tags to the project
    $ STMT= $ PDO-&GT;
    Prepare ("INSERT INTO P_TAGS (PID, TID) VALUES (: PID,: TID)");
    $ STMT-&GT;
    bindparam (': pid', $ pid);
    $ STMT-&GT;
    bindparam (': tid', $ tid);
    Foreach ($ Tag_ids AS $ TID) {
        $ STMT-&GT;
    Execute ();
    }
    $ PDO-&GT;
    COMMIT ();
    

    BINDPARAM C TID Put in the cycle) And so it didn't really understand why you need a separate request and even in aboard)

    KordDEM2021-07-22 13:13:23

    Make in the cycle? What for? The request for the ID corrected, there was a typo. Now it will be clearer

    Ипатьев2021-07-22 13:14:18

    Duck $ Tid is the $ Tag_IDS element on a specific passage. He has a trite there is no value at the first execution. And in the future it is not reassigned

    KordDEM2021-07-22 13:16:27

    tag_name=: tname in the query and bindparam (': tid', $ tid); in parameters.

    KordDEM2021-07-22 13:18:13

    A, ugh, saw. Well, yes, without a prior request, you can also. Regarding BindParam Read how it works php.net/manual/en/pdostatement.bindparam.php

    Ипатьев2021-07-22 13:22:50
  • Answer # 3

    Somehow so MB?

    $ STMT= $ PDO->
    Prepare ("INSERT INTO PROJECT () VALUES ()");
    $ STMT->
    Execute ();
    $ PID= $ PDO->
    Lastinsertid (); //received the ID of the new project
    $ proj_tags= explode (",", $ _get ["tags"]);
    $ STMT= $ PDO->
    Prepare ("INSERT INTO P_TAGS (PID, TID) SELECT: PID, TAG_ID FROM TAGS WHERE TAG_NAME=: TNAME");
    For ($ i= 0; $ i <
    Count ($ proj_tags); $ i ++) {
        $ STMT->
    bindparam (': pid', $ pid);
        $ STMT->
    BINDPARAM (': TNAME', $ PROJ_TAGS [$ i]);
        $ STMT->
    Execute ();
    }
    
  • Answer # 4

    Somehow so MB?

    $ STMT= $ PDO->
    Prepare ("INSERT INTO PROJECT () VALUES ()");
    $ STMT->
    Execute ();
    $ PID= $ PDO->
    Lastinsertid (); //received the ID of the new project
    $ proj_tags= explode (",", $ _get ["tags"]);
    $ STMT= $ PDO->
    Prepare ("INSERT INTO P_TAGS (PID, TID) SELECT: PID, TAG_ID FROM TAGS WHERE TAG_NAME=: TNAME");
    For ($ i= 0; $ i <
    Count ($ proj_tags); $ i ++) {
        $ STMT->
    bindparam (': pid', $ pid);
        $ STMT->
    BINDPARAM (': TNAME', $ PROJ_TAGS [$ i]);
        $ STMT->
    Execute ();
    }