Removing mysql_query() from foreach

[perky416]

Hi everyone,

 

On my website people can add their domain names in a text box.

I then use a foreach to validate each domain the text box.

The code is similar to:

foreach($lines as $line){
$count_query = mysql_query("SELECT * FROM domains WHERE domain='$line'");
$count = mysql_num_rows($count_query);
if ($count > 0){
	$error[] = $line . " already exists!<br />";
}
}

I was wondering if anybody knows a way to use the mysql_query outside of the foreach loop, as if the person has entered 100 domains for example, thats 100 queries accessing my database.

 

Im not too sure if its possible or not.

 

Anybody have any ideas?

 

Many thanks

[wildteen88]

Yes, you need to dynamically generate your query, eg.

$domains_list = implode("','", array_map('trim', $lines));
$query = "SELECT domain FROM domains WHERE domain IN('$domains_list')";
$result = mysql_query($query);

That will query the database for all entered domains.

 

You should validate the domains the user has entered. Drop any invalid domains before querying the database.

[perky416]

Thanks wild teen you've come to my rescue once again .

 

A problem im having though is that currently i use the foreach to display each domain that doesn't exist.

 

test.com already exists.

test1.com already exists.

 

Using your code, iv tried to add the "already exists" error to each domain that already exists. I have came up with the following which does a good job of displaying the error for each domain, however obviously it also displays the error for domains that dont already exist.

 

Do you know how id be able to modify it for only domains that dont exists?

 

			$domains_list = implode("','", array_map('trim', $lines));
			$query = "SELECT domain FROM domains WHERE domain IN('$domains_list')";
			$result = mysql_query($query);
			$count = mysql_num_rows($result);
			if ($count > 0){
				$exists = explode("','",$domains_list);
				foreach ($exists as $exist){
					$error[] = $exist . " is already in use";
				}
			}

 

Thanks

[DavidAM]

The ones that already exist, will come back from the query. The ones that don't, won't.

 

$domains_list = implode("','", array_map('trim', $lines));
$query = "SELECT domain FROM domains WHERE domain IN('$domains_list')";
$result = mysql_query($query);
$exists = array();
$count = mysql_num_rows($result);
if ($count > 0){
while ($row = mysql_fetch_assoc($result)) {
	$exists[] = $row['domain'];
	$error[] = $row['domain'] . " is already in use";
}
}
$not_exists = array_diff(array_map('trim', $lines), $exists);

 

[perky416]

Sorted

 

Thanks i appreciate it.

[Psycho]

The ones that already exist, will come back from the query. The ones that don't, won't.

 

$domains_list = implode("','", array_map('trim', $lines));
$query = "SELECT domain FROM domains WHERE domain IN('$domains_list')";
$result = mysql_query($query);
$exists = array();
$count = mysql_num_rows($result);
if ($count > 0){
while ($row = mysql_fetch_assoc($result)) {
	$exists[] = $row['domain'];
	$error[] = $row['domain'] . " is already in use";
}
}
$not_exists = array_diff(array_map('trim', $lines), $exists);

 

 

FYI the following lines serve no purpose in the above code

$count = mysql_num_rows($result);
if ($count > 0){

 

The while() loop will take care of an empty result set as it is.