Need some logic with PHP to loop through some mysql db results

Question

I have the following results returned via a mysql query:

tag_id   article_id (asc)     name         tag_slug
    56        69487           Exploration  exploration
    10        69488           Events       events
    32        69488           Military     military
    28        69489           Arts         arts
     3        69489           Religion     churches
    36        69490           Forestry     forestry
     8        69490           Industry     industry
    40        69490           Man-Made     man-made
    42        69490           Politics     politics

I need to loop through the results and create a string that would include the tag_slugs associated with each set of article id's. The name and tag_id columns aren't necessary for this part of the code.

For example ...

69487 would have a string that is: '<exploration>'

69488 would have a string that is: '<events><military>'

69489 would have a string that is: '<arts><churches>'

69490 would have a string that is: '<forestry><industry><man-made><politics>'

...and a column named tags would be updated in the db with these strings and the respective article_id.

My attempt below works, kind of, but always leaves off the last db update. I'm sure there has to be a better more logical way but right now I cannot.

$previous_article_id = 0;

while( $row = $result->fetch_assoc() )
{
    if ( $row['article_id '] != $previous_article_id  && $previous_article_id  != 0 )
    {
        $sql = "
             UPDATE
                    ".ARTICLE_TABLE."
             SET
                    tags = '".$tags."'
             WHERE
                    id = ".$previous_article_id."
         ";

         $db->query($sql) OR sql_error($db->error.$sql);

         $tags = '';
    }

    if ( $row['article_id '] == $previous_article_id  || $previous_article_id  == 0 )
    {
        $tags .= '<'.$row['tag_slug'].'>';
    }

    $previous_article_id  = $row['article_id '];
}

Yes, I know it should be PDO and the codes a bit crazy, but I'm working on a friends brothers website so not much authority to change it.

You mean tags is a seperate field & it's value should be <forestry><industry><man-made><politics> say for article_id 69490? — Jenson M John
– Jenson M John, Commented Nov 2, 2014 at 10:34
Are you sure your code is printing both the <events> and <military> tag for id 69488? Cause it looks like it isn't, or I am missing something. — Malcolm Kindermans
– Malcolm Kindermans, Commented Nov 2, 2014 at 10:40
@Jenson M John Yes, and tags is another column in the same table — Mark
– Mark, Commented Nov 3, 2014 at 4:23
@Malcolm Kindermans You are right, only the events tags is printed. Should be both events and military. Code doesn't do as intended. — Mark
– Mark, Commented Nov 3, 2014 at 4:25

Malcolm Kindermans · Accepted Answer · 2014-11-02 10:44:48Z

1

First of all, my compliments for how you have explained your problem and what you have tried. Your code is also well formatted. I often see some crap posts here. So my compliments.

I should change the whole stuff to the following:

$article_tags = array();
while( $row = $result->fetch_assoc() )
{
    $article_tags[$row['article_id']][] = $row['tag_slug'];
}

foreach( $article_tags as $article_id => $tag_array )
{
    $tags = '';
    foreach( $tag_array as $tag )
    {
        $tags .= '<' . $tag . '>';
    }

    $sql = "
             UPDATE
                    ".ARTICLE_TABLE."
             SET
                    tags = '".$tags."'
             WHERE
                    id = ".$article_id."
         ";

    $db->query($sql) OR sql_error($db->error.$sql);
}

answered Nov 2, 2014 at 10:44

Malcolm Kindermans

3371 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mark Over a year ago

This is perfect. I knew there had to be a better way. I'm not a programmer by trade and foreach loops that contain more foreach loops, and arrays all mixed together tend to keep me up too late at night. Thanks for the help, and the kind comments :)

mgaido · Accepted Answer · 2014-11-02 10:41:10Z

0

Maybe the cleaner way to do that with PHP is to create an associative array having your article_ids as keys and the string containing the related tags as values. Eg.

 $myArray[$row['article_id ']].='<'.$row['tag_slug'].'>';

Then, at the end you just iterate with a foreach loop over it and insert the data in your database.

answered Nov 2, 2014 at 10:41

mgaido

3,0553 gold badges22 silver badges40 bronze badges

Comments

geoandri · Accepted Answer · 2014-11-02 10:45:09Z

0

You can more easily, in my opinion,fetch this results from the database by using GROUP_CONCAT function

  SELECT  article_id, CONCAT('<',GROUP_CONCAT(tag_slug separator '><'),'>')
  from table GROUP BY article_id
  ORDER BY article_id DESC

In case you have duplicates on your tag_slug you can use DISTINCT operator inside GROUP_CONCAT function

answered Nov 2, 2014 at 10:45

geoandri

2,4282 gold badges18 silver badges30 bronze badges

Collectives™ on Stack Overflow

Need some logic with PHP to loop through some mysql db results

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related