Code causing 50,000+ entries to be created

[cmattoon]

Hey,

I'm writing an application that tracks attendance. There are multiple events ('events' table), in which members that are present will be found. (When an event file is uploaded, it has all members that attended the event, and adds the event entry in 'events', as well as adding each member to the 'attendance' table, with event_id(eid) and the 'did_attend' value as '1'. When the event is edited, it needs to find the other members that AREN'T in the events table, and add them to the 'attendance' table. When I use this code, it apparently keeps adding the same members. Will a LIMIT 1 stop this? I has about 65,000+ entries added before I stopped the execution of the script.

 

Thanks!

 

 

$sql2 = mysql_query("SELECT * FROM members WHERE status=1 ORDER BY mid ASC");
	while($row = mysql_fetch_assoc($sql2)){
		$sql3 = mysql_query("SELECT * FROM attendance WHERE event='$eid' ORDER BY member ASC");
		while($row2 = mysql_fetch_assoc($sql3)){
			if($row2[mid] == $row[mid]){
				// if the member is in the attendance db, skip
			}else{
				//if member is not in attendance, write zero--- causing multiple entries (50,000+)

				$qry = mysql_query("INSERT INTO attendance (member,event,did_attend) VALUES ('$row[mid]','$eid','0')");
				if(!$qry){
					die("SQL: ".mysql_error());
				}else{
					$msg = "<br />Member ".$row[mid]." marked as absent.";
				}
			}
		}
	}

[patrickin]

There is one rule that you should follow that I promise you will fix your problem.  The amount of queries called should not increase as the amount of data increases.

 

Basically this means, NO QUERIES INSIDE OF DYNAMIC LOOPS!