Stopping duplicate of printing name of user PHP SQL

Question

Everything in my code works properly but I need if someone has more than one job, do not duplicate person name just in person job field echo all jobs belong to person. This is my code:

<fieldset class="fdex" >
        <legend><span class="style4">لیست مشاغل</span></legend>

<?php
    $db_host = 'localhost';
    $db_name = 'site';
    $db_table= 'tablesite';
    $db_user = 'root';
    $db_pass = '';


    $con = mysql_connect($db_host,$db_user,$db_pass) or die("خطا در اتصال به پايگاه داده");
    $selected=mysql_select_db($db_name, $con) or die("خطا در انتخاب پايگاه داده");
    mysql_query("SET CHARACTER SET  utf8");

    $dbresult=mysql_query("SELECT tablesite.name,
                              tablesite.family,
                              tablesite.phone_number,
                              tablesite.email,
                              job_list.job_name,
                              relation.comments
                       FROM  $db_table
                       INNER JOIN relation
                       on tablesite.id_user=relation.user_id
                       INNER JOIN job_list
                       on relation.job_id=job_list.job_id",$con);

    while($amch=mysql_fetch_assoc($dbresult)) {
        echo "* نام: "."&nbsp&nbsp&nbsp".$amch["name"]." ".
            $amch["family"]."&nbsp&nbsp&nbsp"."* عنوان خدمت: ".
            $amch["job_name"]."&nbsp&nbsp&nbsp"."* شماره تماس: ".
            $amch["phone_number"]."&nbsp&nbsp&nbsp"."* ایمیل: ".
            $amch["email"].'<br>'.
            $amch["comments"].'<hr/>';
}
?>

</fieldset>

my code echo as this:(i do not want this way)

*name: jason irani *job:doctor *tel:same *email: same

*name: jason irani *job:software engineer *tel:same *email: same

As you see that will duplicate a record twice. I want if any one has more than one job, act as this:

*name: jason irani *job:doctor AND software engineer *tel:same *email: same

I want merge records that them email and tell is same.

How can I correct this issue?

Answer 1

You could create an array to hold the names of the users and if the name does not exist in the array add it and then set a particular variable.

$names=array();

while($amch=mysql_fetch_assoc($dbresult)) {

    $name='';
    if( !in_array( $amch["name"], $names ) ){
        $names[]=$amch["name"];
        $name=$amch["name"];    
    }

    echo "* نام: "."&nbsp&nbsp&nbsp".$name." ".
        $amch["family"]."&nbsp&nbsp&nbsp"."* عنوان خدمت: ".
        $amch["job_name"]."&nbsp&nbsp&nbsp"."* شماره تماس: ".
        $amch["phone_number"]."&nbsp&nbsp&nbsp"."* ایمیل: ".
        $amch["email"].'<br>'.
        $amch["comments"].'<hr/>';
}

As this original approach didn't suit, perhaps you could try to accomplish the grouping in the SQL directly. I tried a similar query locally and it appeared to work more or less as you desire but it is of course untested with your data.

$sql="select distinct 
    t.`name`, 
    t.`family`, 
    t.`phone_number`, 
    t.`email`, 
    ( select group_concat( ' ', t1.`job_name` ) from `$db_table` t1 where t1.`name`=t.`name` and t1.`family`=t.`family` ) as 'jobs',
    t.`comments`
    from `$db_table` t
    inner join `relation` r on r.`user_id`=t.`id_user`
    inner join `job_list` j on j.`job_id`=r.`job_id`";

$dbresult=mysql_query( $sql, $con );

Answer 2

This task can be solved in different ways with different logic.

Regardless of the solution chosen, it all depends on how you build your database and code logic.

A few comments before going to the solution

• I would have loved to have seen a more structured database and better code design. Both make your final results much better and smoother.
• I previously mentioned using at least MySQLi for a few reasons. One of those is that mysql_* is deprecated and not even supported on my working environment. Therefore, I used MySQLi for the example/solution.
• Another main reason is so much unsafe code is cut and paste from the web using non-sanitized, user-supplied input directly from the forms. MySQLi / PDO are better equipped in dealing with risky input if used correctly. See How can I prevent SQL-injection in PHP?
• Since my IDE does not support arabic/farsi I cannot use those in the example.
• My Answer is based on the limited knowledge from your question and does not mean it is the best approach. It is a concept and starting point from which you will gain in-depth knowledge.

The solution intro

First, we know that for each client you have one or more jobs. We allow ourselves therefore to call each client in a loop. Next, we return all jobs belonging to each client in an inner loop.

The code

<?php
$db_hostname = 'localhost';
$db_database = 'site';
$db_username = 'root';
$db_password = '';

// Create connection
$conn = new mysqli($db_hostname, $db_username, $db_password, $db_database);
mysqli_set_charset($conn, "utf8");

// Check connection
if ($conn->connect_error)
    die("Connection failed: " . $conn->connect_error);

// Statement to get all clients that have a job and the comments belong to them
$sql_clientName = "
SELECT tablesite.id_user,
          tablesite.name,
          tablesite.family,
          tablesite.phone_number,
          tablesite.email,
          relation.comments
FROM  tablesite
    INNER JOIN relation
    ON tablesite.id_user=relation.user_id
   INNER JOIN job_list
    ON relation.job_id=job_list.job_id
GROUP BY tablesite.name;";

// Statement to get a specific client's job info by User id
$sql_clientJob = "
SELECT job_list.job_name, relation.comments
FROM tablesite
  INNER JOIN relation
    ON tablesite.id_user=relation.user_id
  INNER JOIN job_list
    ON relation.job_id=job_list.job_id
WHERE id_user = ?;";

$stmt = $conn->prepare($sql_clientName);
$stmt->execute();
$output = $stmt->get_result();
$stmt->close();

// Go through all clients and print them out
echo "
<table width='900px'>
<tr>
    <td>Name</td><td>Family</td><td>E-mail</td><td>Jobs</td><td>Comments</td>
</tr>
";

while ($row = $output->fetch_array(MYSQLI_ASSOC))
{
    echo "
    <tr>
        <td>" . $row['name'] . "</td><td>" . $row['family'] . "</td><td>" . $row['email'] . "</td>
    ";

    // We call statement once
    $stmt1 = $conn->prepare($sql_clientJob);
    $stmt1->bind_param("i", $row['id_user']);
    $stmt1->execute();
    $output1 = $stmt1->get_result();

    // Fetch the job name belong to the client
    echo "<td>";
    while ($row1 = $output1->fetch_array(MYSQLI_ASSOC))
    {
        echo $row1['job_name'] . ", ";
    }
    echo "</td>";
    echo "<td>" . $row['comments'] . "</td>";
    echo '</tr>';
}
echo "</table>";
$stmt1->close();

UPDATE
After looking at your mysql dump, I have been able to correct the mysql statement and it works as per your request. But remember, my role is to answer as narrowly as possible the question, while also sprinkling in a few opinions to help on design, etc. More than that becomes a full project or redesign.

I have also added arabic/farsi support and what you see in the output is from your database dump.

Output will look like this

Answer 3

Save prevoius name and check it before echo:

$prev_name = "";
while($amch=mysql_fetch_assoc($dbresult)){
if($prev_name != $amch['name']){
  echo "* نام: "."&nbsp&nbsp&nbsp".$amch["name"]." ".$amch["family"]."&nbsp&nbsp&nbsp"."* عنوان خدمت: ".$amch["job_name"]."&nbsp&nbsp&nbsp"."* شماره تماس: ".$amch["phone_number"]."&nbsp&nbsp&nbsp"."* ایمیل: ".$amch["email"].'<br>'
.$amch["comments"].'<hr/>';
} else{
    echo "&nbsp&nbsp&nbsp"."* عنوان خدمت: ".$amch["job_name"]."&nbsp&nbsp&nbsp"."* شماره تماس: ".$amch["phone_number"]."&nbsp&nbsp&nbsp"."* ایمیل: ".$amch["email"].'<br>'
.$amch["comments"].'<hr/>';
  }
$prev_name = $amch["name"];
}
?>

And also like @RiggsFolly said in the comment add ORDER BY tablesite.name to your query.