find all field from range with greater than value of 0

[EchoFool]

Hey

 

I'm trying to make an efficient query where by i want the query to use a WHERE clause to check a range of fields with similiar names like so:

 

P1ID

P2ID  (all the way up to P10ID)

 

And with that assign it to the SELECT section.

 

Currently i have a tedious method which is

 

SELECT * FROM TABLE WHERE RecordID=$var

 

Then checking each field one by one to see if their value is > 0 (all 10 of them, to me this seems inefficient) so wondered if there was a better automated method that MYSQL has or PHP?

[asmith]

Your table structure is not well for this matter. However I'll show you in your way and I'll offer a better way.

 

Your way:

You can simply do a long where clause query:

SELECT * FROM TABLE WHERE P1ID>0 AND P2ID>0 AND P3ID>0 AND P4ID>0 AND P5ID>0 AND P6ID>0 AND P7ID>0 AND P8ID>0 AND P9ID>0 AND P10ID>0  

 

I don't know your whole table fields, But:

Better way:

Make another table with 3 fields:

 

ID_FIRST_TABLE_ROW

P_IDs

P_IDs_VALUE

 

Now insert the P_ID in the second field and its value in the third.