1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

How to insert into multiple tables with a cross reference table

Discussion in 'PHP' started by FoxIX, Jul 31, 2020.

  1. #1
    Hi all,

    I am having trouble trying to work out how to insert data into my tables using a cross reference table. The set up I have is as follows:

    CARD
    card_id (primary key)
    title
    content

    TAG
    tag_id (primary key)
    tag

    CARD_TAG
    card_id
    tag_id
    (both primary, referencing CARD.card_id and TAG.tag_id respectively)

    (Cards can have many tags. Tags can be associated with many cards)

    With dummy data in the tables, I am okay with retrieving the id's from CARD_TAG and then retrieving the relevant data from the other two tables, but I can't work out (after lots of searching) how to INSERT the data.

    The only way that I can currently think of how to do it:

    1. Store the card in CARD
    2. Get the last_id() of that card from CARD
    3. Iterate through the tags, storing them in TAG


    Then I get a bit confused, so all I can think is:

    4. Iterate back through the tags, retrieving the id's
    5. Iterate through the tags again, storing the card id with each tag

    There will only ever be one card stored at a time but that could have multiple tags.

    Would this be the way to do it? Or is there a better way?
     
    Solved! View solution.
    FoxIX, Jul 31, 2020 IP
  2. sarahk

    sarahk iTamer Staff

    Messages:
    28,494
    Likes Received:
    4,457
    Best Answers:
    123
    Trophy Points:
    665
    #2
    This is a classic HABTM (has_and_belongs_to_many) situation. Most systems will delete all the records from the CARD_TAG table before they start because it's onerous to search for tags that have been removed and quicker just to delete the lot and insert the ones we know exist.
    1. save the card, store the card_id number
    2. delete all entries in CARD_TAG for that card
    3. for each card_tag
      1. search to get the id from the TAG table
      2. if not then insert, use last_id() to get the tag_id
      3. insert into CARD_TAG using the card_id & tag_id
     
    sarahk, Aug 1, 2020 IP
  3. JEET

    JEET Notable Member

    Messages:
    3,825
    Likes Received:
    502
    Best Answers:
    19
    Trophy Points:
    265
    #3
    Why don't you use a structure like this one:

    CARD table
    cardID
    title
    content


    TAG table
    tagID
    cardID
    tag

    So when you get a new input for "cards", then all you have to do is this:

    "insert into CARD ( title, content ) values ( '$title', '$content' )";
    $cardID= getLastInsertID();

    $data="";
    foreach( $tags as $v ){
    $data.="( '$catID', '$v' ),";
    }
    $data= trim($data, ",");
    "insert into TAG ( catID, tag ) values $data ";

    There is no need for that third table here, the one which is storing just tagID and cardID

    Store the cardID in the tag table itself.
     
    JEET, Aug 1, 2020 IP
  4. sarahk

    sarahk iTamer Staff

    Messages:
    28,494
    Likes Received:
    4,457
    Best Answers:
    123
    Trophy Points:
    665
    #4
    That's not normalised design and tags should be their own table.
     
    sarahk, Aug 1, 2020 IP
  5. JEET

    JEET Notable Member

    Messages:
    3,825
    Likes Received:
    502
    Best Answers:
    19
    Trophy Points:
    265
    #5
    Tags are still in their own table, only cardID is the extra column in tags table.

    I cannot see the purpose of a third table, unless modifying the existing 2 table structure is messing other things or breaking existing queries.

    In the ePowerPress software I have in my signature, I have this 3 table structure, but I needed that because a tag is stored only "once" in tags table.
    And various posts which are using the same tag, they simply create a link using postID and tagID in this third table.

    In case of this user, they are not storing tag only once. They simply want to store tags of cards in an easy to access way. 2 table structure works very well for that, much easier to manage than the 3 table structure.
     
    JEET, Aug 2, 2020 IP
  6. FoxIX

    FoxIX Well-Known Member

    Messages:
    211
    Likes Received:
    10
    Best Answers:
    1
    Trophy Points:
    140
    #6
    After reading this I realised that I probably haven't explained it very well as I don't see number 2 coming into it. Basically this is something that's just for me where each card will be new so there won't be any entries for that card in the card_tag table yet, so what I hope to achieve is:

    1. Write the card title and description and give it some tags.
    2. Store the card title and description in the card table.

    Then;

    a. Be able to retrieve the card title and description based on a search for a particular tag.
    b. Display the card(s) that have the tags.

    What I'm unsure of is how best to store the tags to make it easy to search for related cards.


    I think I can see where you are coming from here, but to ensure I'm understanding it correctly, I'll put what I think you mean.

    1. Store the card title and description in the card table.
    2. Store the id of the card together with an array of the tags in the tags table.

    Then to display them;

    a. Search through the tags table for a particular tag and store the cards id into an array.
    b. Display the card(s) from the cards table using the created array.

    I hope this helps explain things a bit better. I have only ever used single tables before. Using multiple tables is very confusing and I just can't seem to get my head around it despite reading multiple articles and watching many instructional videos. I can understand the theory behind normalisation but without being able to put it into practise I can't have that "Aha!" moment to see what I am doing right or wrong.
     
    FoxIX, Aug 2, 2020 IP
  7. #7
    I think I get your point now.
    I'd use something like this for this purpose:

    CardsTable
    cardID primary auto increment
    title
    description

    tagsTable
    tagID primary auto increment
    cardID
    tag

    Now suppose your card entry is this:
    $_POST=array(
    'title'=> 'cardName',
    'description'=> 'cardDescription',
    'tags'=> 'tag1, tag2'
    );

    $cardName= addslashes($_POST['title']);
    $cardDesc= addslashes($_POST['cardDesc']);
    $tags= explode( ',', $_POST['tags'] );

    "insert into cardsTable (title, description) values ( 'cardName', '$cardDesc' ) ";
    $cardID = get last insert ID here

    $data="";
    foreach( $tags as $v ){
    $v=addslashes($v);

    $data.=" ('$cardID', '$v'),";
    }//foreach ends

    $data= trim($data, ",");
    "insert into tagsTable (cardID, tag) values $data ";

    Suppose user now opened page for card ID "1"

    "select c.*, group_concat(t.tag) as tags
    from cardsTable as c
    left join tagsTable as t
    on c.cardID = t.cardID
    where c.cardID = '1' ";

    This will give you resultset as:
    $result = array(
    'cardID' => '1',
    'title'=> 'card name',
    'description'=> 'card description',
    'tags' => 'tag1, tag2'
    );

    Now to search related cards with same tags:
    $tags = $result['tags'];

    "select c.*, group_concat(t.tag) as tags
    from cardsTable as c
    left join tagsTable as t
    on c.cardID = t.cardID
    where c.cardID != '1' and
    t.tag in ('$tags')
    group by c.cardID limit 10 ";


    Have not tested this, but I think it will work.
     
    JEET, Aug 2, 2020 IP
    FoxIX likes this.
  8. FoxIX

    FoxIX Well-Known Member

    Messages:
    211
    Likes Received:
    10
    Best Answers:
    1
    Trophy Points:
    140
    #8
    Thank you!

    I understand how that would work and it does cover what I am after. It's also a nice (smaller) step up from using a single table, giving me experience of working with two tables. I think this will give me a better chance of understanding how multiple tables would work together when I come up with an idea that will need a cross reference table. :D

    Thank you again @JEET. I'll mark your answer down as best answer as it certainly answers my question.
     
    FoxIX, Aug 2, 2020 IP
    JEET likes this.
  9. JEET

    JEET Notable Member

    Messages:
    3,825
    Likes Received:
    502
    Best Answers:
    19
    Trophy Points:
    265
    #9
    @FoxIX
    Thanks, you are welcome
     
    JEET, Aug 2, 2020 IP
  10. deathshadow

    deathshadow Acclaimed Member

    Messages:
    9,732
    Likes Received:
    1,998
    Best Answers:
    253
    Trophy Points:
    515
    #10
    @JEET, this is 2020 not 2006... what's with the crapping variables into the query strings and all that addslashes rubbish?

    Let's say you're coming from a form like this:

    
    <form action="addCard.php" method="post">
    	<h2>Add Card</h2>
    	<fieldset>
    		<label>
    			Title:<br>
    			<input type="text" name="title" required><br>
    		</label><label>
    			Content:<br>
    			<textarea name="content" required><br>
    		</label><label>
    			Tags: <em>(comma delimited)</em><br>
    			<input type="text" name="tags" placeholder="tag1,tag2,etc"><br>
    		</label>
    		<button>Create Card</button>
    	</fieldset>
    </form>
    
    Code (markup):
    And let's assume $db is a connected PDO object. I'll use PDO, nowhere do I see which API or SQL engine is being used... also why is the case backwards?!? Let's also assume that all your 'id' are proper index/auto-increment, for a structure like this:

    
    CREATE TABLE cards {
    	id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    	title VARCHAR(128),
    	content BLOB
    }
    
    CREATE TABLE tags {
    	id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    	name VARCHAR(32)
    	INDEX (name)
    }
    
    CREATE TABLE card_tags {
    	card_id BIGINT UNSIGNED,
    	tag_id BIGINT UNSIGNED
    	INDEX (card_id)
    }
    Code (markup):
    The PHP would go a little something like this:

    
    if (
    	empty($_POST['title']) || 
    	empty($_POST['content'])
    ) {
    
    	// resend form as invalid
    	
    } else { // form data valid
    
    	$stmtInsertCard = $db->prepare('
    		INSERT INTO card {
    			title, content
    		} VALUES (
    			?, ?
    		}
    	');
    	$stmtInsertCard->execute[$_POST['title'], $_POST['content']);
    	
    	if (!empty($_POST['tags'])) {
    	
    		$card_id = $db->lastInsertId();
    		$tags = explode($_POST['tags'], ',');
    		
    		$stmtFindTagId = $db->prepare('
    			SELECT id
    			FROM tags
    			WHERE name = ?
    		');
    		$stmtInsertTag = $db->prepare('
    			INSERT INTO tags (
    				name
    			) VALUES (
    				?
    			)
    		');
    		$stmtHookTagToCard = $db->prepare('
    			INSERT INTO card_tags (
    				card_id, tag_id
    			) VALUES (
    				?, ?
    			)
    		');
    		
    		foreach ($tags as $tagName) {
    		
    			$tagName = trim($tagName);
    			$stmtFindTagId->execute([$tagName]);
    			
    			if (!($tag_id = $stmtFindTagId->fetchColumn())) {
    				$stmtInsertTag->execute([$tagName]);
    				$tag_id = $db->lastInsertId();
    			}
    			
    			$stmtHookTagToCard->execute([$card_id, $tag_id]);
    		
    		} // foreach $tags
    		
    	} // if $_POST['tags']
    	
    } // form data valid
    			
    
    Code (markup):
    Warning, untested code, so there may be typo's. Still gives you the general idea.

    If you're working from an array of array of the same values/indexes, just put a for loop around the above and replace $_POST with your inner array's name.

    To break it down, first off leverage prepared queries so the logic inside your loops is cleaner/simpler. It also auto-sanitizes so there's none of the dipshit outdated crapping variables into query strings with the addslashes trash going on. Again this is 2020, NONE of that idiocy belongs in any code written after around 2006.

    First add the new card. If there are tags we grab the id of the new card, split the tags by comma, then PDO->prepare the three queries we'll need for the tags. One to test if the tag already exists and if so grab its id, one for if they don't exist to add a new tag, then one to associate the tag to the card.

    Loop through the tags, trim excess whitespace off them, then see if we can find if it already exists. If it doesn't exist create a new one and grab its id, then once we have the tag ID we plug it into the one-to-many table.

    That about what you're trying to do? Again guessing wildly since you said neither API or SQL engine.
     
    deathshadow, Aug 3, 2020 IP
    JEET and sarahk like this.
  11. JEET

    JEET Notable Member

    Messages:
    3,825
    Likes Received:
    502
    Best Answers:
    19
    Trophy Points:
    265
    #11
    @deathshadow
    No, thats not what I am doing.
    I am not doing all those "if tag exists" checks.
    That is the benefit of the 2 table structure, reduced queries.

    In this structure,
    tagID primary auto
    cardID int
    tag varchar

    There is no need to check if a tag exists or not.
    When a new card is added, all tags along with it are added to the table.

    $data="";
    foreach( $tags as $v ){
    $v=addslashes($v);
    $data.=" ('$cardID', '$v'),";
    }//foreach ends
    $data= trim($data, ",");

    This code will prepare a single query input like this:
    $data="( '1', 'tag1' ),( '1', 'tag2' )";

    Which is sent to database in one query like this:
    "insert into tagTable ( cardID, tag ) values $data ";


    Secondly, my code is an example code, not meant to be copied.
    One can modify it to use PDO or whatever else they want.
    I use mysqlI and I will keep using mysqli
    I will not use PDO.

    PDO is a non-database specific driver.
    Which means code can be imported to ms-sql, my-sql, or other supported PDO databases.
    This also means that PDO is limited in its functionality.
    Functions for ms-sql would be different from functions of my-sql
    Which means that if I have to use PDO, I will need to stop using certain functionality provided by my-sql, which is not supported by PDO, but is supported by mysqli.
    So when I know that I am using my-sql engine, then why should I select a non-database specific driver?

    I do not see any harm in sending variables in the query itself, as long as the variable has been escaped.
    The whole point of escaping variables is to make sure that a hacker cannot insert a query in form data, which gets sent to database.

    $form_data=
    username' (hacker query here)

    sql=
    select * from table where username='$form_data'


    With no escaping this becomes
    select * from table where username='username' (hacker query here)
    hacker's query got executed

    with escaped $form_data same becomes
    select * from table where username='username\' (hacker query here)'
    hacker query is not executed

    If someone uses a "variable in query" approach, and does not uses escaping methods, then that is a problem,
    otherwise not.


    For a very long time I have been trying to find one single example where
    PDO query is more secure than
    mysqli escaped variable query,
    and I have not found any yet.
     
    JEET, Aug 3, 2020 IP
  12. deathshadow

    deathshadow Acclaimed Member

    Messages:
    9,732
    Likes Received:
    1,998
    Best Answers:
    253
    Trophy Points:
    515
    #12
    So how are you avoiding the INSERT on existing issue? Are you slopping the tag names into the same table making it larger/slower? How do you query posts by tagname?

    By keeping a separate smaller table with indexes, it's faster as you gain more and more posts/tags. Just as separating out the name where you'd have less tags than posts with tags is a good idea for the same reasons.

    This is 2020, if you're doing addslashes you're not using PHP right in most cases.


    Again, slopping variables into the query string where they have ZERO business being.

    @deathshadowSecondly, my code is an example code, not meant to be copied.[/quote]
    The code of your "ePowerPress" would beg to differ.

    One can modify it to use PDO or whatever else they
    want.
    I use mysqlI and I will keep using mysqli
    I will not use PDO.

    Utter gibberish. You don't know what you're talking about. Whilst yes, PDO can do multiple drivers, there is NOTHING to stop you from using PDO as your API for just mySQL. Just make your dsn only start with "mysql" and there's zero huffing difference from mysqli apart from cleaner prepare/execute implementation.

    It's slow, inefficient, and redundant to how we've been told to do it for near on 14 years!!!

    Which both mysqli and PDO provide automagically if you use prepare/execute. Again as I said via PM, you're talking like you're still writing for PHP 4, not PHP 5.2/later.

    Because this:

    Is outdated/outmoded crap.

    Check this... assuming $db is a connected PDO object:

    
    $stmt = $db->prepare('
      SELECT *
      FROM table
      WHERE username = ?
    ');
    $stmt->execute($_POST['username']);
    
    Code (markup):
    Completely injection-proof because the query and data are sent SEPARATELY to the server. It's the more efficient format we're supposed to have been using for near-on a decade and a half. Again, this isn't 2006, stop replicating ancient techniques and what already can be done for you by PHP and mySQL. You don't need to waste extra variables escaping a blasted thing anymore, the mechanism exists to do it FOR YOU!

    Hell, that's half the damned reason WHY the old mysql_ functions were killed off. (the other half being the connection always being global scope)

    Which is why we were told back in 2006 TO STOP DOING THAT!!!

    Only time to escape

    Also, if you're not using PDO you should probably be using mysqli::real_escape_string and not addslashes, since addslashes is NOT meant for SQL in the first place.


    You're NOT supposed to be using it in mysqli either!!!

    Assuming $db is a connected mysqli object
    
    $stmt = $db->prepare('
      SELECT *
      FROM table
      WHERE username = ?
    ');
    $stmt->bindParam('s', $_POST['username']);
    $stmt->execute();
    
    Code (markup):
    Prepare/execute is not just a PDO thing.

    If you're manually doing addslashes you're wasting memory making extra copies of the values in RAM for nothing, ignoring the mechanism we've been provided, ignoring half the blasted reason mysql_ was killed off, and in general have no business working with SQL from PHP in the first huffing place. You're even wasting memory and processing time with the need for string addition or variable-in-string processing.

    Why are you defending using more memory and wasting more code whilst playing up PHP 4 style development? Good gravy, you probably don't even POEM when you have to multi-query, do you?

    I mean seriously, this:
    Are you using LIKE or something to try and extract matching tags? I mean if you know the tag ID (which you'd have from client-side) what's your equivalent of:

    SELECT card_id
    FROM card_tags
    WHERE tag_id = ?

    (in practice I'd have a join there to pull the associated cards)

    -- edit -- more specifically:

    SELECT card.id, card.title
    FROM card
    JOIN card_tags ON card.id = card_tags.card_id
    WHERE card_tags.tag_id = ?

    Your single write would just make it harder to do a lookup by tags whilst having zero indexing efficiency. Kind of the whole point of having tags is to be able to look up / categorize BY tags. I don't see how yours would do that in a manner that would be worth even doing.

    How would you pull a list of all tags from your approach with no repeats? How would you pull all "cards" that all have the same tag? I mean, it's why many-to-one relations (as in relational databases) exist.
     
    Last edited: Aug 3, 2020
    deathshadow, Aug 3, 2020 IP
    JEET likes this.
  13. JEET

    JEET Notable Member

    Messages:
    3,825
    Likes Received:
    502
    Best Answers:
    19
    Trophy Points:
    265
    #13
    @deathshadow
    PDO is way slower in performance than mysqli,
    and there is no security benefit in using PDO over mysqli.
    You however, keep talking about mysqli as if its some kind of ancient depricated method.
    Its "mysql" procedural approach which is depricated by PHP, not mysqli.
    https://code.tutsplus.com/tutorials/pdo-vs-mysqli-which-should-you-use--net-24059

    Mysqli queries are much faster than PDO queries.

    About ePowerPress,
    There are just 5 functions which you need to change to use a different driver, PDO, or ms-sql or whichever you want.
    I am not going to use a slow PDO when I know for sure that my software is going to be used with my-sql 99% of the times.

    You say that variable in query approach is less secure.
    Show me an example where an "escaped string in query" is less secure than a prepared statement.
    Just one example is all I am asking.


    My code for "insert"
    "insert into tagTable ( cardID, tag ) values $data ";
    Your comment
    Again, slopping variables into the query string where they have ZERO business being.

    You did read the line above that code, right?
    What is in $data?
    This is:
    ( '1', 'tag1' ), ( '1', 'tag2' )


    "insert into table ( cardID, tag ) values
    ( '1', 'tag1' ), ( '1', 'tag2' )
    ";

    How is that wrong?
    How do you propose to do an insert?
    Give example, without passing variables

    Also, in case you forgot,
    mysql having to read 3 tables is more load on CPU and RAM.
    What is cheaper? Hard disk or RAM?
    What affects performance, hard disk consumed or RAM?
    So with the 2 table approach, we consumed more hard disk, but saved on RAM.
    RAM is kept free as much as possible for "multiple processes".

    For your question:
    How do you query posts by tagname?

    Answer to this is the last query in the selected answer.
    "select c.*, group_concat(t.tag) as tags
    from cardsTable as c
    left join tagsTable as t
    on c.cardID = t.cardID
    where c.cardID != '1' and
    t.tag in ('$tags')
    group by c.cardID limit 10 ";

    I know what you are going to write now,
    swapping variables in query.
    To which my response is,
    show me one single example where
    "escaped variable in query" is less secure.

    Instead of letting the database engine do the escaping, I am doing it manually before sending the query to database.
    What is wrong with that?
    Simply saying that its wrong, does not satisfies me.
    Give me a reason why its wrong.
    Show me an example where its less secure, "escaped variable in query"

    10 years back people used to favour XHTML over HTML.
    What happened?

    I am not going to use PDO simply because 20 people shouted at me for not using it.
    Mysqli is much faster, and my software will be used with my-sql engine, so my choice is mysqli and it will remain this way.
     
    Last edited: Aug 4, 2020
    JEET, Aug 4, 2020 IP
  14. JEET

    JEET Notable Member

    Messages:
    3,825
    Likes Received:
    502
    Best Answers:
    19
    Trophy Points:
    265
    #14
    @deathshadow
    read this
    Why you were told to use prepared statements 14 years ago, and is it actually useful...

    https://stackoverflow.com/questions...-statements-for-mysql-in-php-performance-wise

    Note the comment after this one I copied below,
    PDO does not caches prepared statements.

    Someone wrote:
    "Using prepared statements is never really a bad idea"

    Answer given:
    Not true. You can easily fill up the prepared statement cache and exceed max_prepared_stmt_count if you don't know what you're doing, rendering your app useless until connections consuming the prepared statements are closed.

    Prepared statements are specifically designed for tight inner loops in your business logic where you're going to be calling the same basic query over and over again, a good example being a parameterized query such as:

    SELECT name, address, phone from tbl WHERE id = ?

    and you have a different id on each call. That way, it's worth the extra round trip to the db for the prepare because you're probably going to call it hundreds or thousands of times and just change the parameter.

    But you're expected to remove the prepared statement from the cache or close the connection at the end of, say, your stateless script (php, perl, jsp, ruby, etc).

    If you don't remove the prepared statement and you are using a connection pool, you are bound to fill up the cache over time and get a nasty error "Can't create more than max_prepared_stmt_count statements".
    I'm speaking from experience so think about whether you really need prepared statements because you know definitively that you're going to be repeating the same parameterized query over and over in a tight loop.

    If not, what you're probably looking for is letting mysql use its basic query cache which is a different mechanism than the prepared statement list and which behaves like a true LRU cache from my understanding.


    share
     
    JEET, Aug 4, 2020 IP