A Select query linked to a while loop

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
cone13cone
Forum Newbie
Posts: 24
Joined: Fri Mar 20, 2009 8:32 pm

A Select query linked to a while loop

Post 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.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: A Select query linked to a while loop

Post 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
cone13cone
Forum Newbie
Posts: 24
Joined: Fri Mar 20, 2009 8:32 pm

Re: A Select query linked to a while loop

Post 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?
Post Reply