Page 1 of 1

Using a loop within a loop

Posted: Thu Sep 25, 2003 3:08 pm
by chriso
I'm creating a list of certications a person can choose from. When printing out the list, I don't want the certifications they already have to be included in the list. What's the best way to do this? So far I have the following code which isn't doing well:

Code: Select all

// this table is a list of the certifications available
$cert_array = @mysql_query('SELECT * FROM certs ORDER BY CERTS');
if (!$cert_array) {
die('<h2>Error retrieving certs from table!</h2><br>' . 
'Error: ' . mysql_error());
}

// this table is a list of certs a member already has.
$sql_query = @mysql_query("SELECT faccerts.CID FROM faccerts, certs WHERE faccerts.FID='$fid' AND faccerts.CID=certs.ID ORDER BY CID");
if (!$sql_query) {
echo ("Error in sql query: " . mysql_error());
}


/* Here is where we begin the outer loop to check each certification with the faculty member's certifications so that only those certifications the faculty member does not have are printed
*/
while ($faccerts = mysql_fetch_array($sql_query)) {
mysql_fetch_array($cert_array);
	while ($certs = mysql_fetch_array($cert_array)) {
		if ($certs[0] == $faccerts[0]) {
			unset($certs[1]);
		}
	} // This array is not getting reset back to the top. How do I do this?
} // This ends the loop to check for certifications already held by member.

//Here's where we now print the certifications.
while ($certs=mysql_fetch_array($cert_array)) {
	$cert = htmlspecialchars($certs[1]);
	$cid = $certs[0];
	echo ('<tr>
	<td><input type="checkbox" name="selected[]" id="selected" value='.$cid.'></td>
	<td>'.$cert.'</td></tr>');
}
What I'm trying to do is to go through the list of certification's array and delete the array elements which the faculty member already has, then, print out the remaining elements. Is there a better way to do this or, if I'm on the right track, how do I reset the $certs array pointer back to the top so it will go through the list for each row in the outer loop?

Thanks so much

Chris.

Posted: Thu Sep 25, 2003 5:33 pm
by Leviathan
try this: select certs.* from certs left join faccerts on certs.id = faccerts.cid where faccerts.fid = '$fid' and faccerts.cid is null

Posted: Thu Sep 25, 2003 5:38 pm
by chriso
Leviathan wrote:try this: select certs.* from certs left join faccerts on certs.id = faccerts.cid where faccerts.fid = '$fid' and faccerts.cid is null
Sorry for my ignorance but does this replace both queries, and the rest of my script is ok :?:

What does this query do? I don't understand what roll the "null" plays.

Thanks for your patience with this newbie (me) :oops:

Posted: Thu Sep 25, 2003 5:48 pm
by Leviathan
This is one single query that should (assuming I have the right syntax) give you the result set you want. Left join means take everything from the left table and any records matching the join condition from the right table, or null if there's no particular match. Since all you want is the records for which there's no match, we use is null.

Test this out by putting it into mySql or phpMyAdmin before you change your code; that's a good way to verify that your queries work (pick a test faculty member and hardcode faccerts.fid for your testing).

Posted: Thu Sep 25, 2003 6:09 pm
by chriso
Here's what I wrote:

Code: Select all

$cert_array = @mysql_query("select certs.* from certs left join faccerts on certs.id = faccerts.cid where faccerts.fid='$fid' and faccerts.cid is null");
if (!$cert_array) {
die('<h2>Error retrieving certs from table!</h2><br>' . 
'Error: ' . mysql_error());
}

echo ('<table width="75%" align="center" cellpadding="4"><br><ul>');
while ($newcerts = mysql_fetch_array($cert_array)) { 
echo ('<tr><td><li>'.$newcerts[0].' <br>'); 
}
It doesn't print anything. Let me explain how the tables are set up.

<b>certs</b> contains two columns. ID, and CERTS. CERTS is the title of the certification such as CNE, MCP, MCSE, CCNA, etc.

<b>faccerts</b> contains three columns. ID, FID, CID. Where FID corresponds to the faculty ID in the faculty table and CID corresponds to certs.ID.

So, say for example certs contains the following:
ID CERTS
1 CCNA
2 MCSE
3 MCNE
4 CNI
5 CIW
6 ODBA
7 CCNP
8 MCP

Then, say faccerts contains:
ID FID CID
1 5 2
2 3 4
3 2 4
4 5 1
5 5 5
6 3 3

If I the faculty member whose FID is 5 wanted to add another cert, then when they clicked the add cert button they would only see the following as selections:

MCNE
CNI
OBDA
CCNP
MCP

You may have already gathered this is what I meant so please don't think I'm trying to insult your intelligence. I'm amazed that you provided in a single line what I was trying to accomplish in a couple of dozen.

Also, is php/mysql case sensitve? I used lower case in my script but the tables are in upper case.

Thanks again.

We are trying to do something very similar

Posted: Fri Sep 26, 2003 12:27 pm
by jmb
See my post: http://www.devnetwork.net/forums/viewtopic.php?t=12996

If I solve my problem I will let you know, please do the same for me.

Thanks,
James

Posted: Fri Sep 26, 2003 1:34 pm
by Leviathan
Here's your query along with an explanation. I tested it on your sample data, and it works

select certs.* from certs left join faccerts on certs.id = faccerts.cid and faccerts.fid = 5 where faccerts.id is null

First, I'm using certs.* so that we can return the id for the certification. Since id exists in both certs and faccerts, MySQL needs to know it's only getting fields from certs. We left join with faccerts, taking each row in the left table (certs) and crossing it with the right table. We limit the right table to faculty with id 5. This has to be done in the join condition, not the where clause, since the join condition restricts the set we're crossing with. A left join includes everything in the left table crossed with everything in the right table (subject to the join condition), with a null for every left table record that's not matched in the right table. Without the where clause, this query gives us a list of all 8 certifications, with 3 of them matched to a faculty member and the rest null. Then, we simply look at choices where faccerts.id is null (in other words, there is no record in faccerts that joins with certs and has faculty 5), and we're done.

Posted: Mon Sep 29, 2003 10:34 am
by chriso
Wow!! Thank you very much for your help and explaination. I'm finding that soooo much can be done just through the "select" statement, IF, one knows how to configure the statement correctly. I learn a little more each time. :D

I greatly appreciate your willingness to teach those of us who are new to php. I'm sure you see things over and over again. I admire your patience. I do try to search on my topic first to see if my question has already been answered so you don't end up repeating yourself.

Kind regards,
Chris.