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\$
4
  • \$\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, 2018 at 7:26
  • \$\begingroup\$ i have added schema and sample data \$\endgroup\$
    – Sam
    Jul 9, 2018 at 7:33
  • \$\begingroup\$ I doubt it works this way. Did you ever try to insert an existing tag? \$\endgroup\$ Jul 9, 2018 at 7:59
  • \$\begingroup\$ ah sorry i havent updated that code, now its updated \$\endgroup\$
    – Sam
    Jul 9, 2018 at 8:07

1 Answer 1

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\$
3
  • \$\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\$
    – Sam
    Jul 9, 2018 at 12:03
  • \$\begingroup\$ and what do you mean by "homegrow wrapper", i have just started everything.. \$\endgroup\$
    – Sam
    Jul 9, 2018 at 12:04
  • \$\begingroup\$ @Looper "homegrown" means that YOU have manually written that portion of the code. \$\endgroup\$ Jul 10, 2018 at 4:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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