开发者

How to do query for an unknown variable?

I know this problably sounds confusing so here is the detailed explanation:

In a table i have the following: customer_id, agent_id, brief_is, added_by

  1. customer id is the id for the customer

  2. agent id is the service agent

  3. brief_is is a descritpion

  4. added_by holds the id of either the customer who added it or the agent who added it.

Now i am able to get results by querying the added_by for the customer id but now i want to also adding a sorting function that will show all results where the added_by is for the customer but added by the agent_id.

Do i need to setup another field for added_for or is there another way to show these results.

Query sample:

    /* Get data. */
$sql="SELECT brief_is, customer_id, agent_id, added_by FROM the_brief 
    WHERE dcustomer_id = '$id'";  
   $result=mysql_query($sql);

   $query = mysql_query($sql) or die ("Error: ".mysql_error());

   if ($result == "")
   {
   echo "";开发者_如何学运维
   }
   echo "";

   $rows = mysql_num_rows($result);

   if($rows == 0)
    {
   print("");

    }
   elseif($rows > 0)
   {
   while($row = mysql_fetch_array($query))
    {

  $cust = $row['customer_id'];
  $agent = $row['agent_id'];
  $brief = $row['brief_is'];


 print("");
 }

  }


If I understood correctly, you want to improve your query by limiting (not sorting) the results to the ones where the_brief entry is added by the agent and not the customer.
If that is the case, then all you have to do is add an extra verification to your query : agent_id=added_by.
Now your query will look like:

SELECT brief_is, customer_id, agent_id, added_by 
FROM the_brief 
WHERE customer_id = '$id'
AND agent_id = added_by

EDIT:
Your comment lead me to the following code.Tell me if it's the right one:

SELECT brief_is, customer_id, agent_id, added_by 
FROM the_brief 
WHERE customer_id = '$id'
AND agent_id != customer_id

you can also use <> instead of !=


The query below will return all rows for a specific customer that were added by an agent:

SELECT customer_id, agent_id, brief_is, added_by
WHERE customer_id=<CUSTOMERID>
AND added_by IN (SELECT agent_id FROM agents)


It is not very clear, it sounds that you are asking for the same thing:

 SELECT brief_is, customer_id, agent_id, added_by FROM the_brief 
    WHERE agent_id= '$id'";

What do you mean added for? If you have to keep two kind of info:

  1. Who adds the breief
  2. Who the brefi refers to

then you must provide more info.

If a customer or agent add a brief for itself only the you don't need an other field.


Found it. all i needed to do is use <> for not equal to customer_id and that will give agent_id

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜