1

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?

6
  • I dont think you do! If someone has 2 jobs then they are most likely to have 2 different emails and 2 different phone number. If they have 10 different jobs you are also going to run out of page width to print this data. So you may want to rethink the layout of the data in this situation Commented Nov 1, 2015 at 10:03
  • @RiggsFolly: that person has same tel and email. is just a person that have two jobs Commented Nov 1, 2015 at 10:07
  • Can you be sure that this will always be that case for all your users? Good programming attempts to think ahead and cover all possible situations out of the box. Commented Nov 1, 2015 at 10:08
  • @RiggsFolly when a user want register, he/she will fill email field too. so always a email belongs to just a unique person. if any one insert a submitted email system shows error. so again email is unique. after register and login in site, in another page user can add his/her jobs. in that form user just choose a job of drop down list and when he will be press the button, information will be sent to database contents his name, family,tel,phone_number and job that he chose and some text about his job. so always email is unique. just name and family may be same Commented Nov 1, 2015 at 10:15
  • @maytham-ɯɐɥıλɐɯ: thanks friend :) Commented Nov 1, 2015 at 10:52

3 Answers 3

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

enter image description here

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

8 Comments

that echo all job in a line. since i have too many users that they has one job or more, but this code echo all jobs for a person :(
I will fix the code so it returns the jobs that belong to the specific client. But you still want to view all clients, is that correct?
check my profile for the email, I do not promise any thing but I would like give it a look, but that won't change the fact that I will update my answer regarding the main question. since I did not have your database base it was not efficient to do it 100% correctly. It could be a big help if you make a dump of mysql database. do not attach to email if you can but upload to the cloud and make attachment
@sajad I am busy today, but will continue look at it later
@sajad now it is finally done please try it and accept my answer. You are wished good luck, let me know if you have new questions.
|
2

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 );

1 Comment

dude my mean is if any one has more than one job, then just one time print his name and print his jobs after his name see: it is worng: *name: jason irani *job:doctor *tel:somthing *email: somtiong *name: jason irani *job:software engineer *tel:somthing *email: somtiong******* see it is true: *name: jason irani *job:doctor and software engineer *tel:somthing *email: somtiong
0

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.

4 Comments

You might want to move the $prev_name = ""; outside the while loop if you want that to wiork
And why follow a ?> with a <?php
It might also be a good idea to add that the will have to ADD an ORDER BY tablesite.family to the query so that the data is retrieved in the correct order for this to work. I assume that field is the Family Name but it may need ORDER BY tablesite.family, tablesite.name to be totally accurate
yes, but here I want show logic how can this question be resolved.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.