PHP SQL Concat issue

Question

Cheers,

I want to concat multiple rows to one column

Right now, my output is

CaseID   Name        Number    Register date    filename    Actions 
2        John Doe    3/2020    2020-01-20       test1.pdf    delete
2        John Doe    3/2020    2020-01-20       test2.pdf    delete
3        Jane Doe    5/2020    2020-01-20       test3.pdf    delete

I want something like this:

CaseID   Name        Number    Register date    filename    Actions 
    2    John Doe    3/2020     2020-01-20      test1.pdf    delete
                                                test2.pdf

    3    Jane Doe    5/2020    2020-01-20       test3.pdf    delete

All-day I have tried different SQL statements, but nothing works. I'm stuck, I have no idea how to resolve it because I need to use LEFT JOIN for 2 tables:

$sql = "SELECT * FROM files as f
         LEFT JOIN cases AS c on f.id_case_f = c.id_case
         LEFT JOIN customers as cs on c.id_customer = cs.id_cust
         WHERE case_number LIKE '%$case_number%'";
$result = $db -> query($sql);

I have tried with GROUP_CONCAT I have used the statement below, but it doesn't work.

    <?php
$db = mysqli_connect("localhost", "root", "", "testdb");
if ($db -> connect_error){

    if($_REQUEST['submit']){
        $case_number = $_POST['case_number'];


        if(empty($case_number)){
           $make = '<h4>You must type a word to search!</h4>';
       }else{
           $make = '<h4></h4>';
           $sql = "SELECT id_case_f, GROUP_CONCAT(filename SEPARATOR ', ') FROM files
           LEFT JOIN cases AS c on f.id_case_f = c.id_case
           LEFT JOIN customers as cs on c.id_customer = cs.id_cust
           WHERE case_number LIKE '%$case_number%'
           GROUP BY id_case_f;";

           $result = $db -> query($sql);

           if($make = mysqli_num_rows($result) > 0)
               { echo '<table>
           <tr><th>CaseID</th>
           <th>Name </th>
           <th>Number </th>
           <th>Register date</th>
           <th>filename</th>
           <th>Actions</th></tr>';

           while($row = mysqli_fetch_assoc($result))
           {
              echo "<tr><td>". $row["id_case"] ." </td><td>". $row["name"] . " </td>
              <td>". $row["number"] ."</td> <td> "  . $row["register_date"] ." </td>
              <td> ". $row["filename"] ." </td><td><a href=#>delete</a></td></tr> ";
          }
          echo "</table>";
      }
      else
      {
        echo'<table "><tr><th>Try again! </th></tr></table>';

        print ($make);
    }
    mysqli_free_result($result);
    mysqli_close($db);
}
}
?>

Also, I have used a loooot of other combinations, but unfortunately, nothing worked. :(

If it is necessary I can add the entire PHP code, but this issue is regarding the select statement

Can someone help me with this? Thank you

LE: Errors:

Notice: Undefined index: id_case in /// on line 196
Notice: Undefined index: name
Notice: Undefined index: number
Notice: Undefined index: register_date
Notice: Undefined index: filename

Do you have any error or just result doesn't appear like you want? — Simone Rossaini
– Simone Rossaini, Commented Jan 20, 2020 at 15:33
Check your last SQL as it probably should be GROUP BY id_case_f and without the "; on the WHERE... clause. — Nigel Ren
– Nigel Ren, Commented Jan 20, 2020 at 15:39
@SimoneRossaini - I have updated my first post with the error messages. Nigel - I have tried with that, still no working and "; was a copy-paste mistake - sorry.. Still not working :( — DarkSoul
– DarkSoul, Commented Jan 20, 2020 at 15:42
can you post all code connect/fetch row, and are you sure about name of all label?(post db name structure or photo) — Simone Rossaini
– Simone Rossaini, Commented Jan 20, 2020 at 15:44

DarkW · Accepted Answer · 2020-01-20 18:21:26Z

1

SQL GROUP_CONCAT with LEFT JOIN to multiple relative rows

You can try with multiple SELECT statements. Should work.
Also try to use WHERE EXISTS - https://www.w3schools.com/sql/sql_exists.asp

answered Jan 20, 2020 at 18:21

DarkW

285 bronze badges

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

Collectives™ on Stack Overflow

PHP SQL Concat issue

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related