Newbie ? about implementing keywords for articles

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
I'm looking for some advice on my implementation of linking a
predefined list of keywords to articles. My keyword table in MySQL has
an id, category, and keyword column. My thinking was that by grouping
the keywords into categories I could later search for keywords more
efficiently by narrowing down the search. Is this a justified design
choice for a list of about 100 keywords?

Also, I'm sending the DB a SELECT query for each individual keyword,
which I have a feeling is really inefficient. If I had an array of
keyword strings in $keywords, is there a way to search for them all
with a single execution of a prepared statement?

Many thanks for any suggestions!

function link_to_keywords(&$keywords, $article_id) {
    $keyword_id = 0;
    $insert_stmt = $this->stmt_init();
    $select_stmt = $this->stmt_init();

    $select_query = "
        SELECT id
        FROM keywords
        WHERE category = ? AND keyword = ?
    $insert_query = "
        INSERT INTO articles_keywords
        (article_link, keyword_link)
        VALUE(?, ?)

    if (
        $select_stmt->prepare($select_query) &&
    ) {
        // process $keyrds[$category]
        foreach ($keywords as $category => $subarray) {
            // process $keywords[$category][$keyword]
            foreach ($subarray as $keyword) {
                $select_stmt->bind_param('ss', $category, $keyword);
                $select_stmt->execute() or die($select_stmt->error);
                // if a match is found, insert connection record into the
article_keywords table
                if ($keyword_id != NULL) {
                    $insert_stmt->bind_param('ii', $chal_id, $keyword_id);
                    $insert_stmt->execute() or die($insert_stmt->error);


Re: Newbie ? about implementing keywords for articles

clumsy_ninja wrote:
Quoted text here. Click to load it

This isn't really a PHP question.  Try a MySQL group - such as

Quoted text here. Click to load it

Yes, this is a little bit PHP and a little MySQL - but again, check
comp.databases.mysql. You want the IN argument of a WHERE clause.

Quoted text here. Click to load it

Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.

Site Timeline