CODEWITHSUNDEEP
phpsql
KPO
KPO

Reputation: 880

How to get data and match with table variables?

I have encountered a "unique" problem. I have two tables, one for clients and second for their users. The third table is a comments table. So when a comment is added to the third table, the posting persons id and the organizations manager whom the comment is pertaining to, his id is stored. Now when i have to show the comments i am not sure how to get the appropriate names as they are stored in two different tables.

Any ideas on how to do this?

Some code that i manage to create:

   $query="SELECT v_comments.v_comment_id, d_names.d_name,
   v_comments.visit_reply, v_comments.just_date FROM v_comments 
   LEFT JOIN d_names ON v_comments.d_id = d_names.d_id 
   WHERE v_id = '$vid' and v_comments.s_id='$sid' ORDER BY v_comments.id DESC";  
   $result=mysql_query($query);

   $num=mysql_numrows($result);

   mysql_close();

  echo "";

   $i=0;
   while ($i < $num) {

$reply=mysql_result($result,$i,"v_reply");
$replyid=mysql_result($result,$i,"v_comment_id");
$dname = mysql_result($result, $i, "d_name");


echo "";

echo "";

 $i++;
  }

Upvotes: 1

Views: 91

Answers (3)

Zheng Chen
Zheng Chen

Reputation: 41

This basically is what join operation do in databse. Say you have one table for clients, which has one primary key 'ClientId', one string attribute 'Name', and maybe some other infos which is not important in this question.

Similarly, you have another 'Managers' table, which has primary key 'ManagerId', and his/her name attributes 'Name'.

You also have a Comments table where you have 'ClientId', 'ManagerId', and 'Comment' as you described.

Now to show the actual name of the comments related to you can use following SQL

SELECT Clients.Name, Managers.Name, Comments.Comment 
FROM Comments
INNER JOIN Clients
    ON Clients.ClientId = Comments.ClientId
INNER JOIN Managers
    ON Managers.ManagerId = Comments.ManagerId

Upvotes: 1

joelhardi
joelhardi

Reputation: 11169

Do you just need to add a join to the other table, and SELECT the other name field?

This should work if the other table is called "s_names" (I had to guess at that):

SELECT v_comments.v_comment_id, d_names.d_name, s_names.s_name,
       v_comments.visit_reply, v_comments.just_date
FROM v_comments
LEFT JOIN d_names ON v_comments.d_id = d_names.d_id 
LEFT JOIN s_names ON v_comments.s_id = s_names.s_id 
WHERE v_id = '$vid' and s_id='$sid' ORDER BY v_comments.id DESC;

Upvotes: 1

Flipper
Flipper

Reputation: 2609

What you could do is in the comments table set a field called id.

Then when inserting a comment add a letter in front corresponding to the type of user. For example:

Clients: c5

Users: u5

Then when you are displaying the comment just separate the letter and number and search the corresponding table based on the letter.

Upvotes: 1

Related Questions