0
\$\begingroup\$

I have 3 tables such as books, tags, book_tags. my objective is to insert books info along with tags (tags can be multiple or single). I have two methods one is insert books info 1st and then add tags for that last added book. my code is working but I'm not sure this is the best way to do it

public function addTag($tags, $book_id)
{
    // Loop tags to insert
    foreach ($tags as $tag) {
        // Init query
        $this->db->query('INSERT INTO tags (tag) VALUES (:tag) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id)');

        // Bind values
        $this->db->bind(':tag', $tag);

        // Execute query
        $this->db->execute();

        // Get last tag id
        $last_tag_id = $this->db->lastId();

        $this->db->query('INSERT INTO books_tag (book_id, tag_id) VALUES (:book_id, :tag_id)');

        // Add tag to books
        $this->db->bind(':book_id', $book_id);
        $this->db->bind('tag_id', $last_tag_id);

        // Execute query
        $this->db->execute();
    }
}

Table Schema

Books Table

id : title
 1 : maths
 2 : science
 3 : HP

Tag Table

id : tag
 1 : a
 2 : b
 3 : c

Boook_tag Table

id : book_id : tag_id
 1 :    1    :   2
 2 :    1    :   3 
 3 :    2    :   1
 4 :    3    :   1
 5 :    3    :   2
\$\endgroup\$
  • \$\begingroup\$ Please provide the schema for the 3 tables mentioned. We can't see if your code is efficient without knowing what the DB looks like. \$\endgroup\$ – Mast Jul 9 '18 at 7:26
  • \$\begingroup\$ i have added schema and sample data \$\endgroup\$ – Looper Jul 9 '18 at 7:33
  • \$\begingroup\$ I doubt it works this way. Did you ever try to insert an existing tag? \$\endgroup\$ – Your Common Sense Jul 9 '18 at 7:59
  • \$\begingroup\$ ah sorry i havent updated that code, now its updated \$\endgroup\$ – Looper Jul 9 '18 at 8:07
1
\$\begingroup\$

The main problem with your code is your database class. It is stateful, which is a call for disaster by itself, but it also prevents you from using such an advantage of prepared statements as multiple execution (acknowledgement: both links to my PDO tutorial articles).

So in my example I would assume that $this->db contains not a homegrown wrapper but a vanilla PDO instance.

$tag_stmt = $this->db->prepare('INSERT INTO tags (tag) VALUES (:tag) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id)');
$btag_stmt = $this->db->prepare('INSERT INTO books_tag (book_id, tag_id) VALUES (:book_id, :tag_id)');

foreach ($tags as $tag) {

    // Execute query
    $tag_stmt->db->execute(['tag' => $tag]);

    // Get last tag id
    $last_tag_id = $this->db->lastId();

    // Execute query
    $btag_stmt->execute(['book_id' => $book_id, 'tag_id' => $last_tag_id]);
}

as you can see, we can prepare every query only once, and then in a loop just execute them with the actual data.

\$\endgroup\$
  • \$\begingroup\$ yah that's true, I could not able to execute 2 query at once, I found this MVC tutorial online, so where the binding happening? and could you please show me a tutorial the best MVC pattern classes for php#pdo \$\endgroup\$ – Looper Jul 9 '18 at 12:03
  • \$\begingroup\$ and what do you mean by "homegrow wrapper", i have just started everything.. \$\endgroup\$ – Looper Jul 9 '18 at 12:04
  • \$\begingroup\$ @Looper "homegrown" means that YOU have manually written that portion of the code. \$\endgroup\$ – mickmackusa Jul 10 '18 at 4:22

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.