[RESOLVED] loop, but need to exclude previously found

[schwim]

Hi there everyone!

I am writing a banning thing. It needs to ban by username, email and IP. For IP, I need to query the tracking table. It's got thousands of entries and each user_id can have hundreds of entries. I understand how to loop to find all the entries for a given user_id, but I don't know how to only do the banning entry once for each ip. Here's my rudimentary loop:

$query_ip = "SELECT ip FROM tracking WHERE user_id = '$selected_id'";
$result_ip = mysql_query ($query_ip) or die('MySQL error: Failed to retrieve ips for user from tracking <br />' . mysql_error() . '<hr />Query: ' . $query_ip);
$num_ip=mysql_num_rows($result_ip);
if($num_ip != 0){
	while ($row_ip = mysql_fetch_assoc ($result_ip)) {
		$logged_ip = $row_ip['ip'];

	/* Insert IP into banlist.  I need to do a check against already entered IP's, prior to this.  Maybe an array? */
	$query_insip="INSERT INTO banlist (ip) VALUES ('$logged_ip')";
	$results_insip=($query_insip) or die('MySQL error: Failed to insert new IP for banlist <br />' . mysql_error() . '<hr />Query: ' . $query_insip);
}
}

So this would just keep entering the same IP over and over again since the user has tons of entries with the same IP. How would I handle a check like this?

Thanks for your time!

[Weedpacket]

First of all, why are you still using the MySQL extension? [thread=10387467]It's been deprecated for some time now.[/thread]
Also, keeping in mind some of the principles of relational databases (like the idea that a SELECT statement generates a relation from existing relations), you only need the one statement:

INSERT INTO banlist (ip) SELECT DISTINCT ip FROM tracking WHERE user_id='$selected_id'

[schwim]

Hi there weed and thanks very much for the help.

First off, I've changed the script to use mysqli. Thank you very much for the link and warning.

Secondly, I have a question about the code you've posted. If I needed to insert 'bob' into username, '61 into user_id and NOW() into bantime in the banlist entry for every found IP as well, how would I write that?

$query_ban="INSERT INTO banlist (user_id, username, bantime, ip) VALUES ('$user_id', '$username', 'NOW()', (SELECT DISTINCT ip FROM tracking WHERE user_id='$selected_id')";

Something like this, perhaps?

[Weedpacket]

SELECT DISTINCT '$user_id', '$username', now(), ip FROM ....