0

Goodevening everyone. I have combined two tables. For better understanding i made them smaller and changed descriptions to be easier.

lets call them A and B

A table has 3 columns

Id   Name    Car_Brands
1    Peter     1,2
2    John      1
3    Maria     3

B table has 3 columns

Id2   CarBrand    Color
1       Fiat       Red
2       Seat       White
3       Audi       Blue

Inside php, with the LEFT JOIN Command i have a table with 5 columns, all 3 of first table and Id2,CarBrand of second (if i query the SQL command its working fine)

$QueryTables = "SELECT A.Id,A.Name,A.Car_Brands,B.Id2,B.CarBrand
    FROM A LEFT JOIN B ON A.Car_Brands = B.Id2";
mysqli_query($DbCon,"SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'");
$Results = mysqli_query($DbCon,$QueryTables);

Inside html i create a table to show the results

 <tbody>
    <?php while($CarsList = mysqli_fetch_assoc($Results)) { ?>
    <tr>
        <!-- Table Column Names -->
        <td> Per.ID </td>
        <td> Name </td>
        <td> Car-Brand(s) </td>
        
        <!-- Results -->
        <td> <?php echo $Results["Id"]; ?>
        <td> <?php echo $Results["Name"]; ?>
        <td> 
        
        <!-- We have multiple possible cars to each owner so we make a loop check -->
        <?php 
        $CarIDs = $Results["Car_Brands"];
        $CarIDs = explode(',',$CarIDs);
        $value = $CarsList["Id2"];
        foreach($CarIDs as $value){
            $Car = $CarsList['CarBrand'];
            echo $Car;
            echo "<br>";
        ?>
        </td>
    </tr>
    </tbody>

What i see as a result is

Per.Id   Name    Car_Brand(s)
1        Peter     Fiat
                   Fiat (<-i want to see here Seat)
2        John      Fiat
3        Maria     Audi

What am i doing wrong ? (PS I tried to simplify the question and the variables so forgive me for any fast transformation issues :) )

DFd
  • 97
  • 8
  • `A.Car_Brands = B.Id2` should be `FIND_IN_SET(B.id2, A.Car_Brands)` – Barmar Feb 16 '22 at 19:45
  • If i use FIND IN Set like this i get TOTALY WRONG TABLE results. i think my problem is inside the foreach loop somewhere – DFd Feb 16 '22 at 19:51
  • Works as desired: https://www.db-fiddle.com/f/sKmCAfNNVQh3WCBacPvWAx/1 – Barmar Feb 16 '22 at 19:57
  • @Barmar Thank you but my values are not static . They are coming from inputs and they can be different every time. I need to find the problem in my code which is not showing the Second Brand. I'm not experienced developer and i could have made a really easy and stupid mistake :/ – DFd Feb 16 '22 at 20:00
  • You should use `ORDER BY` to get all the rows for the same person together. – Barmar Feb 16 '22 at 20:00
  • Nothing in that solution requires the values to be static. – Barmar Feb 16 '22 at 20:01
  • There's nothing in your query that uses input data, it's just joining the two tables. – Barmar Feb 16 '22 at 20:01
  • If you want to get all the brand names in the same row, you need `GROUP_CONCAT(B.CarBrand)` and `GROUP BY A.Id`. – Barmar Feb 16 '22 at 20:04
  • Without grouping, each brand name is in a different row, so you shouldn't have that `foreach` loop. – Barmar Feb 16 '22 at 20:05

0 Answers0