Filter data using multiple columns in mysql

Question

The data in my table looks like this

ID  |   BID     | PID   |   TID
1   |   101     | 10    |   123
2   |   102     | 10    |   123
3   |   103     | 10    |   123
4   |   104     | 10    |   012
5   |   105     | 20    |   789
6   |   106     | 20    |   789
7   |   107     | 30    |   003

I have a field for the user. Where the user can choose the PID they want to show. I need to be able to filter the PID and the TID linked to the BID.

I mean for example when I have 5 BID for TID 123 I would like all of those to show.

And when the user enters 10 on the PID it will only show:

ID  |   BID     | PID   |   TID
1   |   101     | 10    |   123
2   |   102     | 10    |   123
3   |   103     | 10    |   123

Update:

I added another field for the user to select the TID. This is my query

SELECT 
BID, 
TID,
PID

FROM 
Table1

WHERE 
PID = "1" AND
TID = "123"

If I use 'AND' and the user only enters 1 on the PID and doesn't touch the TID, there's no output just like this

ID  |   BID     | PID   |   TID

If the user specifies the correct TID and enters for example 1 on the PID and 123 on the TID the output will be: THIS IS THE CORRECT OUTPUT

ID  |   BID     | PID   |   TID
1   |   101     | 10    |   123
2   |   102     | 10    |   123
3   |   103     | 10    |   123

Now if the user enters 2 on the PID and leaves 123 on the TID the output will be like this

ID  |   BID     | PID   |   TID

Basically, the user have to fill the 2 fields for it to have the correct output.

My question is, how can I filter it correctly? Where when the user enters the PID and doesn't specify the TID it will still have the correct output.

If you notice on the data

ID  |   BID     | PID   |   TID
4   |   104     | 10    |   012

This record have the same PID with ID's 1,2 and 3 but different in TID. I only need to return the values for the BID specified for the specific TID's like with the TID (123) I need to return those items only.

I'm sorry if my explanation wasn't clear enough for some of you guys, but I hope that having more description will make you guys atleast understand my problem. Thank You.

Your question doesn't make sense. If the chooses PID 10, why is it only showing TID = 123? — Barmar
– Barmar, Commented Jun 3, 2014 at 1:40
If the user enters just one column, you just filter that one column. If he enters two column, you filter both columns with AND. — Barmar
– Barmar, Commented Jun 3, 2014 at 17:11

Patrick Collins · Accepted Answer · 2014-06-03 01:38:48Z

1

Use a WHERE clause:

SELECT * FROM table WHERE TID=123

answered Jun 3, 2014 at 1:38

Patrick Collins

10.7k5 gold badges36 silver badges74 bronze badges

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

Comments

Michael Adamission · Accepted Answer · 2014-06-03 01:39:28Z

1

I would comment but I don't have enough reputation.

Can you show your current mysql query for that table with column names and we can modify it more clearly for you, but it would be something like this:

BID MySQL Query: "SELECT * FROM Table WHERE TID = 123"
PID MySQL Query: "SELECT * FROM Table WHERE PID = 10"

To dynamically filter you would replace the 123 and 10 with values from the user. We need to know what type of server side code you're using as well. PHP I take it? Post sample code of that as well.

answered Jun 3, 2014 at 1:39

Michael Adamission

4933 silver badges9 bronze badges

2 Comments

mrjoltcola Over a year ago

+1 looks right to me, so lets do something about that "don't have enough reputation" :D

sleepsleepsleep90731 Over a year ago

@madamission i added the query i'm using and added more description. is that better?

Collectives™ on Stack Overflow

Filter data using multiple columns in mysql

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related