Page 1 of 1

Selecting 2 columns from multiple tables.

Posted: Sat May 29, 2010 8:05 pm
by brownfreelance
Hello,

I have 6 tables that have the same structure. (ie. each table has country, abbrev and filename, the columns i want). I'm trying to set up a form that will search through and return all results into a sing UL. Right now I have:

Code: Select all

$str = $_REQUEST["str"];
//
$sql = "SELECT 'country', 'filename' FROM europe, asia, africa, northamerica, southamerica, oceania WHERE '*.country' LIKE '%".$str."%' or '*.abbrev' LIKE '%".$str."%'";
//
$res = mysqli_query($link, $sql);
if($res) {
	print "Things and stuff.";
	print "<ul id=\"countryList\">";
	while($newArray = mysqli_fetch_array($res, MYSQLI_ASSOC)) {
		$name = $newArray["country"];
		$file = $newArray["filename"];
		print "<li><img src=\"/Flags/".$file."\" width=\"24\" height=\"24\" align=\"absmiddle\" />&nbsp;&nbsp;".$name."</li>";
	}
	print "</ul>";
} else {
	printf("error:%s\n", $mysqli_error($link));	
}
mysqli_close($link);
The above code returns no errors but returns no list items as well. I'm new to databases so... What am I doing wrong.

Thanks in advance for any help,
Scott.

Re: Selecting 2 columns from multiple tables.

Posted: Sat May 29, 2010 10:35 pm
by mikosiko
brownfreelance wrote:I have 6 tables that have the same structure
brownfreelance wrote:I'm new to databases so... What am I doing wrong
Just to start.... your database design..... why 6 tables if all of them have the same structure?... I will have only one table adding an additional field to properly qualify the countries .... and associate a reference table for that field..... that will simplify your queries in the future. One alternative to the second table is denormalization and include the qualification field values directly in the country table ...

in the code that your show your SELECT have several errors,,, you should read a little more about how to use this sentence.

hope this help