sql statement help

mfacer

Hey,
I need to open a few tables to see if any of them contain a specific value.... I was thinking of doing something like

if (!mysql_query("blah from tableX where xxx")) {
	if (!mysql_query("blah from table y where xxx")) {
		if (!mysql_query("blah from table z where xxx")) {
			echo "nothing found";
		}
	}
}

so if the first statement doesnt find anything, do the next one, if that doesnt find anything, do the next one..... until there are no more tables to search...

is there a better way to do it? maybe using inner joins or something?

basically I need to check if a certain field value is present in any of the tables... if it is, I perform an operation, if not, I leave it.....

thanks for any help 🙂

devinemke

are you searching through every field of every table? or just ceratin fields?

mfacer

the actual SQL used is

select year from table1 where orgID='xxx'

if there is a field returned, the code will return true and perform something... if false, it will do the next SQL..soemthing like

select year from table2 where orgID='xxx'

cheers 🙂

devinemke

mysql_connect($server, $user, $password);
mysql_select_db($database);

$result_tables = mysql_list_tables($database);
while ($table = mysql_fetch_row($result_tables))
{
	$result = mysql_query("SELECT year FROM $table[0] WHERE orgID = 'xxx'");
	if (mysql_num_rows($result))
	{
		echo 'xxx was found in table ' . $table[0];
		break;
	} 
}

AstroTeg

Making multiple query calls can slow things down.

You might look into joining the data by the orgID or doing a union:

select year from table1 where orgID='xxx' union select year from table2 where orgID='xxx' union select year from table3 where orgID='xxx'

This isn't too fancy. But it will consolidate the duplicates for you. If you absolutely need to know which table the value came from, you could do:

select year, "table1" AS tablename from table1 where orgID='xxx' union select year, "table2" AS tablename from table2 where orgID='xxx' union select year, "table3" AS tablename from table3 where orgID='xxx'

If you're really lazy, you use the for loop provided earlier and use it to build this query as one string. Then query the database once using this.