[SOLVED] SQL query in loop

[karenn1]

Hey everyone,

 

I have 4 queries that look like this:

 

$sql = "SELECT Device_Code, Chan1_Reference FROM devices WHERE Chan1_Reference = 'CW1002/W'";
$sql = "SELECT Device_Code, Chan2_Reference FROM devices WHERE Chan2_Reference = 'CW1002/W'";
$sql = "SELECT Device_Code, Chan3_Reference FROM devices WHERE Chan3_Reference = 'CW1002/W'";
$sql = "SELECT Device_Code, Chan4_Reference FROM devices WHERE Chan4_Reference = 'CW1002/W'";

 

The reference number will be a value I grab from a form input field. What I need is a loop of some kind that will take that reference number and check to which Channel it matches to, as per the queries above.

 

Is it possible to have a for loop that will go through those four statements and stop until one returns a row? That statement is then the one I use for other queries and data.

 

 

Thanks!

 

Karen

[ober]

Will it only be in one?

 

$sql = "SELECT Device_Code, Chan1_Reference FROM devices WHERE Chan1_Reference = 'CW1002/W' OR Chan2_Reference = 'CW1002/W' OR Chan3_Reference = 'CW1002/W' OR Chan4_Reference = 'CW1002/W'";

 

By the way, it looks like you don't have your database normalized.  Tsk tsk.

[Maq]

Try this:

 

for($x=1;$x{
   $chan = "Chan{$x}_Reference";
   $sql = "SELECT Device_Code, $chan FROM devices WHERE $chan = 'CW1002/W'";
   $result = mysql_query($sql) or die(mysql_query());
      if(mysql_num_rows($result) > 0))
      {
         while($row=mysql_fetch_assoc($result))
         {
            $device_code[] = $row['Device_Code'];
            $chan[] = $row[$chan];
         }
         break;
      }
}

print_r($device_code);
print_r($chan);

[taquitosensei]

I would adjust your table layout to make things easier in the Future

instead of

Device_Code, Chan1_Reference, Chan2, Chan3, etc

 

Do

Device_Code, Channel, Reference

Then you can do a single query

$sql="select Device_Code, Channel, Reference from devices where Reference='CW1002/W'"; 

I just saw the previous posters comment about normalizing, this is what he was talking about.

 

 

 

[Maq]

ober is right, you should definitely look into Database Normalization, it will prevent problems like these.

[karenn1]

Thank you all for your replies! I agree with the comments regarding database normalization. Unfortunately, I didn't create this database. It was given to us by the client. They structured it in this way to fit in with an intricate water metering system so I have no control over that. I have to change my coding to fit with it.

 

Ober, your statement using OR in the WHERE clause works perfect! Thanks again!

 

 

Karen

[karenn1]

Maq, I tried your loop but I get the following error:

 

[] operator not supported for strings

 

Then it refers to this line:

 

$chan[] = $row[$chan];

 

Any ideas?

[Maq]

If ober's works then don't worry about it.  I guess I misunderstood your question because in your example you selected different columns for different ChanX_Reference that were corresponding to your WHERE clause.  I tried to create a dynamic loop that would break out if any of the queries got a return > 0.  ober's selected the same column every time, which you said works, which is why I'm confused...

[karenn1]

Thanks for the reply Maq. I tried your loop just for interest sake. It was actually what I initially looked for but Ober's code also works fine.

 

Thanks for your help!