Searching mysql for a pattern group

[majocmatt]

What I have is a database filled with inspection reports, each field in the database can only have 1 of 5 values in them (A,V,NV,O,Q). These are simply letters that correspond to a word.

Now there are a lot of inspection fields, around 50 I'm guessing.

I need to loop thru all the fields and find every single one that has either V,O or Q in them, and then I'll need to display those items by name on screen.

Now, I thought I knew what I was doing, but I have been coding all day long, and for some reason can't get creative and come up with a solution for this.

How exactly can I loop all the entries out that have V,O or Q as their data? If a foreach is my answer, maybe thats why I cannot figure it out, for a I am inexperienced with them, could someone give me some direction please?

Thanks,
Matt

[majocmatt]

Anyone have an idea?

[wisewood]

give us an idea what your table structure is like. Will make helping easier.

[majocmatt]

Oh sorry, well my table is like this:

[b]Table:[/b] crane

[b]Columns:[/b] id | date | motor | brake | bearings | wheels | jib | rail | coupling...

there are actually 30 or so columns similar to the latter ones above, all representing parts of a crane. Someone fills out a form, which is an inspection report, to fill these fields, and each inspection report is related to a customer and product via linking tables.

The input for each of these will be simple strings, most of them are one letter (A,V,NV,O,Q), and these values will always be accurate since they are HTML select dropdowns. In short, A and NV values are considered "good" and V,O and Q values are bad.

Now after an insector has filled in the form and submitted it, he and his employer must be able to view this report. So I made a simple ouput report listing all of the parts of the crane and their corresponding inspection values (A,V,etc,etc).

What needs to happen is any parts that contain a "bad" value needs to be printed at the top of the report. So say the motor is bad and receives a V value standing for Code Violation. It needs to be printed at the top of this report, with an apporopriate action to be taken (which is going to be a simple text link that clicks thru to another form requesting a repair which sends to the manufacturer - I can do this part).

So my question is, how could I select every part with a "bad" value (V,O,Q) efficiently, without having to select every column and construct an if statement to see if it has a V,O or Q in it?

[craygo]

Well i did something quick. This will return the field name that has the values you requested

[code]<?
// get the id of the crane to search for
$id = $_GET['id']; //<--- change this to the way you are submitting your form
$i = 0;

$sql = "SELECT * FROM crane WHERE id = '$id'";
  $res = mysql_query($sql) or die (mysql_error());
    while ($i < mysql_num_fields($res)) {
    $meta = mysql_fetch_field($res, $i);
     $sql2 = "SELECT * from crane where $meta->name in ('V', 'O', 'Q')";
     $res2 = mysql_query($sql2) or die (mysql_error());
       while($r = mysql_fetch_array($res2)){
       extract($r);
       echo "$meta->name<br>";
       }
       $i++;
       }
?>[/code]

hope that is what you are looking for

Ray

[majocmatt]

That looks like exactly what I need.

I was completely unaware of the function mysql_fetch_field. I'll give this a try!

Thanks craygo

[majocmatt]

Alright, that code 'almost' works, except its acting a little erratic. It finds the first bad value but then prints it like 10 times then it finds some that have 'good' values, but mostly works except the multiple prints of the same one.

any ideas? i've been playing with it a while.