CODEWITHSUNDEEP
phpsql
DarkSoul
DarkSoul

Reputation: 165

PHP SQL Concat issue

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

Upvotes: 1

Views: 70

Answers (1)

DarkW
DarkW

Reputation: 28

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

Upvotes: 1

Related Questions