PHP get MySQL result from two tables and combine them

Question

I have two tables with fields.

Right now I can get all the data I need in a two queries. But how can I combine the result or should I say mix them?

Here's my code:

    $query = "SELECT * FROM product";
    $select_product = $db->query($query);

    while($row = $db->fetch_object($select_product)) {
        $status = $row->status;
        if($status == 'published') {
            $product_id = $row->id; 
            $product_title = $row->title;
            $product_image = $row->image;   

            echo '<div class="item">';
            echo '<div class="title"><a href="/product/' . $product_id . '">' . $product_title . '</a></div>';

            echo '</div>';
        }
    }

    $query = "SELECT * FROM recipe";
    $select_recipe = $db->query($query);

    while($row = $db->fetch_object($select_recipe)) {
        $status = $row->status;
        if($status == 'published') {
            $recipe_id = $row->id;  
            $recipe_title = $row->title;
            $recipe_image = $row->image;    

            echo '<div class="item">';
            echo '<div class="title"><a href="/recipe/' . $recipe_id . '">' . $recipe_title . '</a></div>';

            echo '</div>';
        }
    }

As you can see from the code, the html item are in loops and display all the data from products and in a separate html, it displays the recipe loop item, but my goal is do it like this and add the image, of course:

   <div class="product">
      <div class="product_title">'the_value'</div>
      <div class="product_image">'the_value'</div>
   </div>
   <div class="recipe">
      <div class="recipe_title">'the_value'</div>
      <div class="recipe_image">'the_value'</div>
   </div>
   <div class="product">
      <div class="product_title">'the_value'</div>
      <div class="product_image">'the_value'</div>
   </div>
   <div class="recipe">
      <div class="recipe_title">'the_value'</div>
      <div class="recipe_image">'the_value'</div>
   </div>
   ... and so on ...

So far I've tried getting the data by using UNION clause in a single query,

$query = "SELECT * FROM product UNION SELECT * FROM recipe";

but I'm receiving an error:

Warning: mysqli_fetch_object() expects parameter 1 to be mysqli_result, boolean given

Thanks.

EDIT2: Here are the tables:

Table name: recipe

fields: id, title, image

Table name: product

fields: id, title, image

All I want is to display the data mixed. Right now I'm displaying like this: recipe/recipe/recipe/product/product/product

and the goal is display like this:

recipe/product/recipe/product

If you've tried something and got an error, you should include exactly what you tried and exactly what the error is. Also, is there a specific relation between products and recipes? Or do you just want to alternate between two independent lists? — Patrick Q, Nov 29 '16 at 18:55
Generally you would have a single SQL query which gets the data you want, then you would loop over that data and output it to the page. What you haven't really described is how these two tables relate, what their structure is, and what the expected result would be. It sounds like you're just looking for reference material on either the `UNION` or `JOIN` keywords. — David, Nov 29 '16 at 19:00
Hi, David. The two tables right now doesn't have anything related between. I'm just trying to display all the data from them and display them in a mix rows. Just like a journal website that displays the last news in the frontpage in a mixed order from different categories. — user2519032, Nov 29 '16 at 19:02
@user2519032: In that case what you're looking for is here: http://stackoverflow.com/questions/2973202/mysqli-fetch-array-mysqli-fetch-assoc-mysqli-fetch-row-expects-parameter-1 You need to determine the actual error coming back from the database before you can diagnose the correct that error. — David, Nov 29 '16 at 19:04
You're probably getting that mysql error because a `UNION` requires you to select the same columns from each table. My guess is that that's not happening with your `SELECT *`. But a `UNION` isn't going to do what you want since it just appends on result set to the end of the other. — Patrick Q, Nov 29 '16 at 19:07
Yes, I understand that UNION won't work. I've checked the link, but it didn't help. The error comes from not properly fetching the data. I guess it's more complicated that I thought... — user2519032, Nov 29 '16 at 19:11
Possible duplicate of [Merge every other array php](http://stackoverflow.com/questions/8194327/merge-every-other-array-php). Keep your two separate queries and your question basically becomes the same as this. — Patrick Q, Nov 29 '16 at 19:14
Possible duplicate of merge every other array? That's a different topic. — user2519032, Nov 29 '16 at 19:18
@user2519032: `"The error comes from not properly fetching the data"` - You don't even know what the error message is. This question is an exact duplicate of http://stackoverflow.com/questions/2973202/mysqli-fetch-array-mysqli-fetch-assoc-mysqli-fetch-row-expects-parameter-1 The database is trying to tell you what the problem is, but you're not checking the error message. Get the error message, *then* you can meaningfully begin to correct the error. — David, Nov 29 '16 at 19:20
@user2519032 I beg to differ. Once you realize that having the two queries probably _is_ what you want instead of doing a `UNION`, then it is actually the same question. — Patrick Q, Nov 29 '16 at 19:23
All I learned now is that `UNION` won't work for this kind of situation, So, now I'm back using the two queries. I don't have any error, but still didn't achieved what I want. — user2519032, Nov 29 '16 at 19:31
All I want is to display the results mixed: recipe/product/recipe/product... right now I can display only like this: recipe/recipe/product/product. — user2519032, Nov 29 '16 at 19:33

score 1 · Answer 1 · answered Nov 29 '16 at 20:36

Consider using multidimensional associative arrays built from database fetches and then run a for loop through all items. Notice too the status filter can be written in query.

$query = "SELECT * FROM product where status = 'published'";
$select_product = $db->query($query);

$products = []; 
while($row = $db->fetch_object($select_product)) {
    $products[]['id'] = $row->id;            
    $products[]['title'] = $row->title;
    $products[]['image'] = $row->image;   
}

query = "SELECT * FROM recipe where status = 'published'";
$select_recipe = $db->query($query);

$recipes = [];
while($row = $db->fetch_object($select_recipe)) {
    $recipes[]['id'] = $row->id;            
    $recipes[]['title'] = $row->title;
    $recipes[]['image'] = $row->image;   
} 

for($i = 0; $i < min(count($products), count($recipes)); $i++){
    echo '<div class="item">';
    echo '<div class="title"><a href="/product/' . $products[$i]['id'] . '">' . $products[$i]['title'] . '</a></div>';
    echo '<div class="image"><a href="/product/' . $products[$i]['id'] . '">' . $products[$i]['image'] . '</a></div>';
    echo '</div>';

    echo '<div class="item">';
    echo '<div class="title"><a href="/recipe/' . $recipes[$i]['id'] . '">' . $recipes[$i]['title'] . '</a></div>';
    echo '<div class="image"><a href="/recipe/' . $recipes[$i]['id'] . '">' . $recipes[$i]['image'] . '</a></div>';
    echo '</div>';
}

NOTE: Since recipes and products may be of different length, the above for loops to lowest min() of counts for $products and $recipes, so each <div class="item"> maintains a pair of both. Otherwise, an error will produce for Undefined index. For remaining items, run another loop on the list with higher count afterwards to return the rest.

// REMAINING $products
for($i = min(count($products), count($recipes)); $i < count($products); $i++){
    echo '<div class="item">';
    echo '<div class="title"><a href="/product/' . $products[$i]['id'] . '">' . $products[$i]['title'] . '</a></div>';
    echo '<div class="image"><a href="/product/' . $products[$i]['id'] . '">' . $products[$i]['image'] . '</a></div>';
    echo '</div>';
}

// REMAINING $recipes
for($i = min(count($products), count($recipes)); $i < count($recipes); $i++){
    echo '<div class="item">';
    echo '<div class="title"><a href="/recipe/' . $recipes[$i]['id'] . '">' . $recipes[$i]['title'] . '</a></div>';
    echo '<div class="image"><a href="/recipe/' . $recipes[$i]['id'] . '">' . $recipes[$i]['image'] . '</a></div>';
    echo '</div>';
}

Hi, Parfait. I've checked your code now. Some products are displayed, some not. That goes for the recipes too. I'll respond again as soon as I manage to fix the code. — user2519032, Nov 29 '16 at 20:59
Right now with your code I'm getting too many results (45) and many of them are empty. Currently I got 6 recipes and 9 products in the database. — user2519032, Nov 29 '16 at 21:10
Hmmmm...Interesting. Echo *count($products)* and *count($recipe)*. And how do you know its 45 results? — Parfait, Nov 29 '16 at 21:55
That should be correct since there are 3 divs per section (*item*, *title*, *image*): `(3divs * 6products + 3divs * 6recipes) + 3divs*3remaining products = (18 + 18) + 9 = 36 + 9 = 45 divs` — Parfait, Nov 29 '16 at 22:24
I don't know where is the problem, but the code doesn't work. It shows 45 items, instead of 15. — user2519032, Nov 29 '16 at 22:35
In PHP dump out the arrays: `var_dump($products)` to see contents. Did you run counts? Check page source (usually right clicking browser) and see what PHP echoed to HTML. Don't just count *any* element. — Parfait, Nov 30 '16 at 01:30

score 0 · Answer 2 · edited May 23 '17 at 11:45

0

I don't have enough rep to comment or I would, but you should see this question.

$arr1 = array(1, 3, 5);
$arr2 = array(2, 4, 6);

$new = array();
for ($i=0; $i<count($arr1); $i++) {
    $new[] = $arr1[$i];
   $new[] = $arr2[$i];
}
var_dump($new);

edited May 23 '17 at 11:45

Community

1
1

answered Nov 29 '16 at 19:00

Mike Harrison

1,309
12
17

Also why can't you use CSS to just style them alternately instead of trying to do a bunch of complicated joins or array merges? Seems a bit odd to do it the way you're doing. – Mike Harrison Nov 29 '16 at 19:25

score 0 · Answer 3 · edited May 23 '17 at 11:52

I still maintain that this is effectively a duplicate of Merge every other array php. However, here's the same logic tailored specifically to your case.

So to start with, here's your two queries:

$query = "SELECT * FROM recipe";
$select_recipe = $db->query($query);

$query = "SELECT * FROM product";
$select_product = $db->query($query);

$recipes = $select_recipe->fetch_all();
$products = $select_product->fetch_all();

Your two queries will result in you getting data as if you had done this:

$recipes = array(
    array(
        "id" => 1,
        "title" => "Apple Pie",
        "image" => "applepie.jpg",
        "status" => "published"
    ),
    array(
        "id" => 2,
        "title" => "Pizza",
        "image" => "pizza.jpg",
        "status" => "published"
    ),
    array(
        "id" => 3,
        "title" => "Chicken Soup",
        "image" => "soup.jpg",
        "status" => "published"
    ),
);

$products = array(
    array(
        "id" => 1,
        "title" => "Apples",
        "image" => "apples.jpg",
        "status" => "published"
    ),
    array(
        "id" => 2,
        "title" => "Cheese",
        "image" => "cheese.jpg",
        "status" => "published"
    ),
    array(
        "id" => 3,
        "title" => "Chicken",
        "image" => "chicken.jpg",
        "status" => "published"
    ),
);

Now we need to add the appropriate path prefix to each row:

foreach($recipes as &$recipesRow)
{
    $recipesRow["path"] = "/recipe/";
}

foreach($products as &$productsRow)
{
    $productsRow["path"] = "/product/";
}

Here's the exact function from one of the answers in the linked question:

function array_merge_alternating($array1, $array2)
{
  $mergedArray = array();

  while( count($array1) > 0 || count($array2) > 0 )
  {
    if ( count($array1) > 0 )
      $mergedArray[] = array_shift($array1);
    if ( count($array2) > 0 )
      $mergedArray[] = array_shift($array2);
  }
  return $mergedArray;
}

And here's how you call that function with your data and use the results based on your initial code:

$mergedArray = array_merge_alternating($recipes, $products);

foreach($mergedArray as $row)
{
    $status = $row["status"];
        if($status == 'published') {
            $item_id = $row["id"];  
            $item_title = $row["title"];
            $item_image = $row["image"]; 
            $item_path = $row["path"]; 

            echo '<div class="item">';
            echo '<div class="title"><a href="' . $item_path . $item_id . '">' . $item_title . '</a></div>';

            echo '</div>';
        }
}

Live Example Here

The title recipe and product title has different "href". Also I don't see any way to fetch all the data properly with my fetch_object() like that. The code is incomplete. — user2519032, Nov 29 '16 at 20:12
@user2519032 See the updated answer. I added additional steps so that you are clear what to do. — Patrick Q, Nov 29 '16 at 20:37

score -1 · Answer 4 · answered Nov 29 '16 at 19:12

You would typically do this in a JOIN statement. Since you have not provided the column names, this is somewhat of a stab in the dark, but it would probably look something like this:

SELECT a.`id`, a.`title`, b.`image` FROM `product` AS a LEFT JOIN `recipe` AS b ON a.`id` = b.`id`;

To do this you need to do a couple of things. You need to put a relational index on the id of table recipe so that it's id's are dynamically matched to the corresponding id from product.

Also, you should explicitly request only the needed columns when you run a query. Do not query for *, as it can be a very heavy query and may also disclose information you do not want to provide for that specific case.

PHP get MySQL result from two tables and combine them

4 Answers4