Looping through values based on other values in PHP

Question

I have the following SQL to fetch from a WordPress database outside WordPress:

SELECT p.post_name, m.meta_key, m.meta_value FROM wp_postmeta m INNER JOIN wp_posts p ON m.post_id = p.post_name WHERE p.post_type = 'something' LIMIT 0, 100;

post_name in wp_posts contains an id for the specific post, and matches post_idin wp_postmeta. meta_key contains field names, such as date, place, etc. meta_value contains the values for each of these fields.

So each post has a set of fields (meta_key) that each have a value (meta_value).

I'm trying to loop through these for each post, but nothing I've tried has worked. For now I have this, which shows all the data, but it loops through the meta_values instead of showing the meta_values for each post_id.

$data = $result->fetch_assoc();
if ($result = mysqli_query($conn, $sql)) {
  while ($row = $result->fetch_assoc()) { ?>
  <li>
  <?php
    echo $row["meta_value"];        
  ?>  
  </li>
  <?php
    } $result->close();
  }
  ?>

This sort of gives me:

<li>Post 1, meta value 1</li>
<li>Post 1, meta value 2</li>
<li>Post 2, meta value 1</li>
<li>Post 2, meta value 2</li>

But I need

<li>Post 1, meta value 1 — Post 1, meta value 2</li>
<li>Post 2, meta value 1 — Post 2, meta value 2</li>

Do you have any ideas?

Update based on comment from @ChristianF. foreach didn't work, so I tried using while instead.

The results are somewhat weird, where it seems eg. meta value 1 from post 1 is repeated lots of times. There are lots of empty <li> elements as well, so I'm guessing the code somehow prints a <li> element for each row with the post_id, even though not all meta_values need to be printed. I've included a couple of them using if statements. I've inserted the generated HTML below the code.

while ($row = $result->fetch_array()) {
if (!isset ($oldID)) {
    $oldID = $row['post_id'];
}

if ($oldID != $row['post_id']) {
    // Removing the last 3 characters from the output,
    // as we don't have any more items after this one.
    $postOut = substr ($postOut, 0, -4)."</li>\n<li>";
}

if ($row['meta_key'] == 'who_what') {
    $postOut .= $row['meta_value'];
} else if ($row['meta_key'] == 'place') {
    $postOut .= $row['meta_value'];
}

echo $postOut;
}

The result is huge, and it also seems to cut off some of the values. I haven't included the entire result.

<li></li>
</li>
<li></li>
</li>
</li>
<li>Meta_value from who_what key</li>
</li>
</li>
<li></li>
<li></li>
</li>
</li>
<li></li>
</li>
<li>Full meta_value from place key</li>
</li>
</li>
<li></li>
</li>
<li>Meta_value from place key missing last four characters</li>
<li></li>
</li>
</li>
<li></li>
</li>
<li>Meta_value from place key missing last four characters</li>
</li>
<li></li>
</li>
</li>
<li></li>
</li>
<li>Meta_value from place key missing last four characters</li>
</li>
</li>
<li></li>
</li>
</li>
<li></li>

ChristianF · Accepted Answer · 2016-10-04 14:26:57Z

1

You need to have a variable, in which you're storing the post ID. Then check this for each row, and if it's different from the current one print a new row.
Something like this in other words:

// Start output with a list element.
$postOutput = '<li>':

foreach ($res->fetchArray () as $row) {
    // Prime the old ID, so that we can check against it later on.
    if (!isset ($oldID)) {
        $oldID = $row['post_id'];
    }

    // First, we need to end the old list item if we have a new post.
    if ($oldID != $row['post_id']) {
        // Removing the last 3 characters from the output,
        // as we don't have any more items after this one.
        $postOut = substr ($postOut, 0, -3)."</li>\n<li>";
    }

    // Adding the list element with the expectation of more
    // content to be atted to it later on.
    $postOut .= "Post #, meta ".$row['meta'].' - ';
}

answered Oct 4, 2016 at 14:26

ChristianF

2,06111 silver badges14 bronze badges

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

4 Comments

hoegenhaug Over a year ago

Hi @ChristianF, and thank you. I've updated the original post with some new code based on yours, which, unfortunately didn't work entirely, but I think it's the right direction.

ChristianF Over a year ago

@hoegenhaug: There is a typo in either line 3 or 6 in your updated example, I suspect line 6. The missing data is because you're using substr(), but you haven't added any separators between the elements. Thus you remove the last 4 characters of the last element instead.

hoegenhaug Over a year ago

Oh yeah, it should be post_id, not m.post_id. How would I add separators between the elements?

ChristianF Over a year ago

@hoegenhaug: String concatenation, just like what I've done in the last line of my example code. The substr() call needs to be adjusted to match the number of bytes you add.

Collectives™ on Stack Overflow

Looping through values based on other values in PHP

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related