PHP merge 2 array variables into 1

Question

I hope, I formulated my title right. I want to understood how to merge to 2 arrays into 1 array.

Simple example of what I want to do:

I have 2 database tables, like blog_posts and users.

I want to select data from blog_posts and foreach post I want to select information for "addedby" like username, user_image, etc.

With echo I managed to just show values like <?php echo $user['username']; ?> and <?php echo $blog['title']; ?>

I want to return those values, like in mvc pattern. How I was reading from different topics, I have to store data into arrays, to return them, with one array there's no problem, but I don't understand how can I return them both.

public function getBlogPostList(){
    try{

        $sortby = "SELECT * FROM blog_posts";

        $stmt = $this->connCommunity->prepare("$sortby");
        $stmt->execute();
        $result = $stmt->fetchAll();

        foreach($result as $post){

            $addedby = $post['addedby'];
            $stmt2 = $this->connUsers->prepare("SELECT * FROM users WHERE id=('$addedby')");
            $stmt2->execute();
            $result2 = $stmt2->fetchAll();

            $blog_post = array();
            foreach($result as $post) {
                $blog_post[] = $post;
            }

            foreach($result2 as $user) {
                array_merge($blog_post, $user);
            }
        }

        return $blog_post;

    }
    catch(PDOException $e) {
        echo $e->getMessage();
    }
}

And displaying arrays like:

<?php

    foreach ($BlogModel as $BlogModel)
    {
        echo '<tr><td><a href="index.php?id='.$BlogModel['id'].'">'.$BlogModel['id'].'</a></td><td>'.$BlogModel['title'].'</td><td>'.$BlogModel['username'].'</td></tr>';
    }


?>

You can select all that information in one query if you do a little research on the sql JOIN syntax. — RiggsFolly, Oct 09 '18 at 15:41
This `foreach($result as $post) { $blog_post[] = $post; }` is inside another foreach which is also processing `$result` the second will consume all the array so the outer loop will run only once — RiggsFolly, Oct 09 '18 at 15:42
Also variables don't need to be quoted, `$this->connCommunity->prepare($sortby)` and to use prepared statements effectively you should parameterize them (if you were to use this approach, but `join`s would be better anyway). — user3783243, Oct 09 '18 at 15:42
@RiggsFolly I think sql JOIN won't work, because data are located into 2 different databases, so I connect them seperently with connUsers and connComunity like here ($stmt2 = $this->connUsers) — Car Paradise, Oct 09 '18 at 15:49
Not so, look at this answer https://stackoverflow.com/questions/8895892/connecting-multiple-database-and-join-query-across-database-in-php — RiggsFolly, Oct 09 '18 at 15:51

sietse85 · Accepted Answer · 2018-10-09T15:52:37.147

0

First of all start with a simpler query which does all the work.

This query example assumes the column with the userId in the blog_posts table is user_id, replace this one with the correct column. Replace my example databasenames communitydb and usersdb with your correct database names.

$sortby = "SELECT * FROM communitydb.blog_posts p LEFT JOIN usersdb.users u ON u.id = p.user_id";

After this you only need to return the first $stmt->fetchAll(); and you are done. So a lot of code in your function getBlogPostList() is not necessary.

With the loop you can then loop through the results. It could be you have duplicated columnnames when joining tables, if that is the case you should define which columns you want from your users table as well. such as u.id as userid

edited Oct 09 '18 at 15:52

answered Oct 09 '18 at 15:49

sietse85

1,488
1
10
26

Thanks for answer, but.. I think sql JOIN won't work, because data are located into 2 different databases, so I connect them seperently with connUsers and connComunity like here ($stmt2 = $this->connUsers) – Car Paradise Oct 09 '18 at 15:50
one caveat is that the first connection user must also have read access to the 2nd database usertable please keep that in mind – sietse85 Oct 09 '18 at 16:00
Thanks, It helps a lot. Just a one single question more. I have identical data table column names. Like I have "id" in blog_posts and "id" in users. Now when I try to show blog_post "id" it returns all the time user id. – Car Paradise Oct 09 '18 at 16:01
i already said that in my answer ;) i will help you a little `SELECT p.*, u.id as userid, u.name as username FROM ` – sietse85 Oct 09 '18 at 16:01
Thank you a lot. Now I understood. – Car Paradise Oct 09 '18 at 16:11

PHP merge 2 array variables into 1

1 Answers1