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
customer id is the id for the customer
agent id is the service agent
brief_is is a descritpion
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:
- Who adds the breief
- 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
精彩评论