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?