Jump to content

[SOLVED] SQL query in loop


Recommended Posts

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.






Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Try this:


   $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))
            $device_code[] = $row['Device_Code'];
            $chan[] = $row[$chan];


Link to comment
Share on other sites

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

instead of

Device_Code, Chan1_Reference, Chan2, Chan3, etc



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.




Link to comment
Share on other sites

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!




Link to comment
Share on other sites

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...

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.