How foreach in two table with php

Question

It is Table from Phpmyadmin.

Table: post

----------------------------------------
|    id   |    cat_id     |    name    | 
----------------------------------------
|    1    |    3          |    Alex    |
|    2    |    1,2        |    Mona    |
|    3    |    1,2,3      |    Sarah   |
----------------------------------------

Table: cat

-----------------------
|    id   |    level   | 
-----------------------
|    1    |    PHP    |
|    2    |    #C     |
|    3    |    JAVA   |
-----------------------

Out: (localhost/post.php?id=3)

----------------------------------------
|    id   |    name    |    level      |
----------------------------------------
|    3    |    Sarah   | PHP,#C ,JAVA  |
----------------------------------------
Name: Sarah - LEVEL: PHP,#C,Java

I adopted code given in this example:

how do i remove a comma off the end of a string? and How to use implode function in foreach loop

I changed the code with the link above and it is as follows: (foreach)

$id = $_GET['id'];
$query = "SELECT * FROM post WHERE id='$id' "; 
$result = mysqli_query($db, $query);
while($row = mysqli_fetch_array($result)) {
    echo ' Name: '; echo $row["name"]; 
    echo ' - LEVEL:';
    $cats =  $row["cat_id"];
    foreach($cats as $cat) {
        echo $row["level"];
    }
}

Hence when I go to: http://localhost/post.php?id=3, It gives error:

Name: alex
LEVEL:
Warning: Invalid argument supplied for foreach() in C:\xampp\...  "foreach($cats as $cat){"

What could be the reason and what am I doing wrong?

It should be `foreach(explode(",", $cats) as $cat) {` as `cat_id` is a list of id's. BUT this doesn't fetch you the `level` column from any other table, so `echo $row["level"];` will try and get `level` from the `post` table. — Nigel Ren, Dec 23 '19 at 11:23
This would normally be 3 tables, it's a 'many to many' relationship (I.e. a post can have many categories, a category can have many posts. Search for this term and you'll find many resources [like this random example](https://www.learnhowtoprogram.com/ruby-and-rails/ruby-database-basics/database-schema-and-relationship-types)). You'll be much better off doing it the normal way, otherwise trivial tasks (like finding all posts in a category) become needlessly complex/inefficient. — AD7six, Dec 23 '19 at 11:28
Does this answer your question? [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) — Dharman, Dec 23 '19 at 12:08
The comment from ADSix is really important. Most of the time, when you think of writing several values in a single column you can presume you've got a DB design issue. — Laurent S., Dec 23 '19 at 12:16

score -2 · Accepted Answer · answered Dec 23 '19 at 11:37

as it combination of two tables, we cann't get level value directly. You need to get level value by another query. I hope below code will works to you.

$id = $_GET['id'];
$query = "SELECT * FROM post WHERE id='$id' "; 
$result = mysqli_query($db, $query);
while($row = mysqli_fetch_assoc($result)) 
{
   echo ' Name: '.$row["name"]; 
   $cats =  explode(",",$row["cat_id"]);
   $level='';
   foreach($cats as $cat) 
   {
       $lvlQry = mysqli_query($db, "Select level from cat where id=$cat");
       $lvlRes = mysqli_fetch_assoc($lvlQry);
       $level. = $lvlRes['level'];
   }
   echo ' - LEVEL:'.$level;
}

How foreach in two table with php

1 Answers1