Page 1 of 1

A Select query linked to a while loop

Posted: Sun Mar 22, 2009 9:54 pm
by cone13cone
I could really use some help with this one. I need to have a select query that has a while loop as part of the WHERE clause... is this possible?

like this

Code: Select all

 
<?php 
$query = "SELECT CID FROM jobs WHERE JID = '$jid'";
$result = mysql_query($query);
while(all_cids_that_match_this_job = mysql_fetch_array($result)) {
    all_cids_that_match_this_job['CID']
}
 
$query = "SELECT CID FROM users WHERE CID != '$all_cids_that_match_this_job'";
$result = mysql_query($query);
 
What is the proper way to do this?
any help and or comments would be greatly enjoyed.

Re: A Select query linked to a while loop

Posted: Sun Mar 22, 2009 10:41 pm
by requinix
Not really, not as you are thinking. SQL isn't quite like most programming languages - many things are possible if you can think about it in a different way.

What you're trying to do now is get a list of all CIDs that don't have a specific JID, then look up all the other CIDs. So aren't you really just getting all the CIDs that don't match the JID?
The fact that the data are in two different tables makes it a little harder but still possible - in one query, even.

Code: Select all

SELECT CID FROM users JOIN jobs USING (CID) WHERE jobs.JID != $jid

Re: A Select query linked to a while loop

Posted: Mon Mar 23, 2009 10:04 am
by cone13cone
I tried that an ended up getting unpredictable results.
I search around and came up with something like this

Code: Select all

 
    $query = "SELECT jobs.CID, jobs.JID, users.CID 
                FROM jobs, users
                WHERE jobs.JID = '$jid' 
                AND jobs.CID != users.CID";
    $result = mysql_query($query, $connection) or die 
    ("Error in query: $query. " . mysql_error());
    while($test_cids = mysql_fetch_array($result)) {    
    echo "<li>".$test_cids['CID']."</li>";
    }
    
 
The only problem is that for each duplicate job i am getting duplicate cids back.. only one cid can be assigned to each instance of a job number.. leaving me with more duplicates each time a job number is duplicated.

what about something like this?

Code: Select all

 
$query = "SELECT jobs.CID, jobs.JID, users.CID 
                FROM jobs, users
                WHERE jobs.JID = '$jid' 
                AND jobs.CID != users.CID";
                              "AND the amount of times the CID occures is equal to the ammount of times the job number occurs."
 
I am thinking something along those lines would weed out duplicates. Anyone know how I could do this or a better way to go about it?