sql query statement conditional to another query

[mbarandao]

Hello:

I'm trying to select specific data from code 2 below, using the variable $clientID queried from code 1 (also below). The question is if code 1 iterates a series of rows containing variables $clientID (say about 10 unique $clientID's); how can I write code 2 to go through each of those unique $clientID's outputted from code 1?

here are the codes:

//code 1 (iteration )
$SQL = "SELECT clientID FROM additional_cars group by clientID";
$r = mysql_query($SQL);
while ($row = mysql_fetch_array($r, MYSQL_ASSOC)) {
$clientID=$row['clientID'];
    
}

//code 2
$get_swork = "select servicearea, date, customerid, clientID from servicesrendered where servicearea=' Oil Change ' and clientID='$clientID' order by id desc limit 1";
 $get_swork_res = mysql_query($get_swork); 

  if (mysql_num_rows($get_swork_res) > 0) 
  { 
     while ( $swork_info = mysql_fetch_array($get_swork_res)) 
    { 
      $servicearea = $swork_info['servicearea']; 
      $date = $swork_info['date']; 
      $customerid = $swork_info['customerid']; 
      $clientID = $swork_info['clientID'];
        
}
	}

Basically, my entire task (which includes more codes than presented here is to go through a series of tables, looking for the existence of certain variables and if true, prepare and send an email.

The question posed above, initiates the first process by checking tables with a where conditional statement.

I hope I'm explaining the task properly!

Any thoughts is appreciated!
Mossa

[ddymacek]

Of course, there is always more than one solution, but,
if there is nothing else to do between step 1 and step 2, just incorporate step 2 in the while loop of step 1.
so code 1:

while ($row = mysql_fetch_array($r, MYSQL_ASSOC)) {
     $clientID=$row['clientID'];
     $get_swork = "select servicearea, date, customerid, clientID from    servicesrendered where servicearea=' Oil Change ' and clientID='$clientID' order by id desc limit 1";
     $get_swork_res = mysql_query($get_swork);

     if (mysql_num_rows($get_swork_res) > 0) {
        while ( $swork_info = mysql_fetch_array($get_swork_res)) {
          $servicearea = $swork_info['servicearea'];
          $date = $swork_info['date'];
          $customerid = $swork_info['customerid'];
          $clientID = $swork_info['clientID'];

        }
        // at this point you have 1 iteration of a 'client' pulled from the above data and you are still in your conditional if of your second query which means that you should have data in your $servicearea, $date, $customerid and $clientID variables above
        // so you could send the email now, assuming you want the above info in it.
      }
   // continue while loop through next client id.
}

if this isn't what you were looking for you could initiate an array in step 1:

$clients = array();
in your while loop
while ($row = mysql_fetch_array($r, MYSQL_ASSOC)) {
    $clientID=$row['clientID'];
    array_push($clients,$clientID);
}

and in step 2
wrap the whole thing in this code:

foreach($clients as $client) {
    $clientID = $client[0];
    //rest of step 2;
}  // close foreach
// note the second solution is untested and may not work cleanly the first time.

enjoy

[mbarandao]

Dymacek, thank you for your continued assistance! I appreciate the fix you provided yesterday on another post of mine.

Concerning this current post, the concept behind the latter option is what I believe my need is. But I have been unsuccessful with my attempts. I'm unable to echo any of the clientID at all. However, with the former option, I'm able to produce the array of clientID but I'm unable to proceed forward with using each unique clientID in the the second query.

My task does not involve sending an email to all records, but I need to go through all clientID's and if other conditions are true in another part of my entire code, then an email is prepared and sent out. Maybe you may want to see my entire code. it is about 350 lines; if so, maybe I can send it through a PM.

Again, thanks the assistance!
Mossa

[karthik_ppts]

You can combine both queries using 'in' Keyword as follows

$get_swork = "select servicearea, date, customerid, clientID from servicesrendered where servicearea=' Oil Change ' and clientID in (SELECT clientID FROM additional_cars group by clientID) order by id desc limit 1";

[mbarandao]

You can combine both queries using 'in' Keyword as follows

$get_swork = "select servicearea, date, customerid, clientID from servicesrendered where servicearea=' Oil Change ' and clientID in (SELECT clientID FROM additional_cars group by clientID) order by id desc limit 1";

Thanks Karthik_pranas for your reply. Your suggestion does reduces my code a bit and is very claver; but, my greater challenge still exist. As written in my initial post, if code 1 (now, with your suggestion, "in(SELECT...)") iterates a series of rows containing variables $clientID (say about 10 unique $clientID's); how can I write code 2 ($get_swork) and pass each unique $clientID through that sql.

[karthik_ppts]

no need to iterate. The inner query serves all unique clientID to your $get_swork query.

[mbarandao]

got it resolved!
final solution here
Thanks for all the assistance...